How to convert an entire MySQL database when text was Not correctly stored, and stored in some other charset

Question

in this image you can see this problem

SOURCE CODE OF SQL FILE IS:

    -- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 20, 2021 at 07:03 PM
-- Server version: 5.5.32
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `test_table`
--

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(11) NOT NULL,
  `title` text CHARACTER SET utf8
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `test_table`
--

INSERT INTO `test_table` (`id`, `title`) VALUES
(2, 'ÛŒØ§Ø´ÛŒÙ„ Ù‚Ø·Ø±Ù‡'),
(3, 'ØªÙˆØ³Ø¹Ù‡ ØµÙ†Ø¹Øª ÙØ±Ø¢ÛŒÙ†Ø¯ Ø¨Ø§Ø®ØªØ±'),
(4, 'Ø§Ù„ÙˆÙ†Ø¯ Ù…Ú©Ø´'),
(5, 'Ù†Ù‡Ø§Ø¯Ù‡ Ú¯Ø³ØªØ±'),
(6, 'Ù¾Ø§Ú©Ø§Ù† Ø¨Ø°Ø±');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

link to :

![sample query for this problem][2]

**What is the problem: **

By using of

ALTER DATABASE

and

ALTER TABLE

commands. same as

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

we can convert an entire MySQL database characterset and collation to UTF-8 when text was Not correctly stored but my problem is that: ALTER TABLE tablename CHARACTER SET utf8 only sets the default charset on a table which is used for newly created columns. And it does not convert existing columns that already have a char set. how do it only by MySQL Commands and without exporting to file

pay attention: This problem started when the character settings (character set ) in the server settings were not set correctly to utf8 and remained unchanged on latin1, which is mysql by default setting.

Edit: add source sql insted linking to it

please include a sample sql exported from your db. I want to import it in my local db and work on it — , Oct 20 '21 at 16:32
Does this answer your question? [How to change the default charset of a MySQL table?](https://stackoverflow.com/questions/8906813/how-to-change-the-default-charset-of-a-mysql-table) There are answers there that show how to use a procedure to do so, so you can change it a little bit to cover all tables. — Jorge Campos, Oct 20 '21 at 17:12
i check it (by replace of table name and column) but There is nothing happening — webrefer, Oct 20 '21 at 17:20
@(saeed arab sheybani ) you can get sample file from this link: https://bayanbox.ir/download/9207589261626850516/test-table.zip — webrefer, Oct 20 '21 at 17:23
Please [edit] this question to type or paste in the code rather than linking to a zip file containing it. As it stands now, your zip file makes it hard to answer your question or for people with related issues to find your question. See the [formatting documentation](/editing-help) for tips to make your code appear nicely inline. — Stephen Ostermiller, Oct 21 '21 at 09:36
The magic is inside `CONVERT TO` - it not only changes the column definition but also converts the content of the column from the old to the new charset. — IVO GELOV, Oct 21 '21 at 11:57

score 1 · Accepted Answer · 2021-10-22T18:58:17.633

1

Well-Defined problems lead to breakthrough solutions

Check out this simple solution:

UPDATE `test_table` SET `title` = CONVERT(CAST(CONVERT(title USING latin1) AS 
BINARY)  USING utf8)

Left: Run code

Right: Result of code

Thanks, IVO GELOV

He is right

The magic was inside CONVERT TO

edited Oct 22 '21 at 18:58

answered Oct 22 '21 at 18:52

How to convert an entire MySQL database when text was Not correctly stored, and stored in some other charset

1 Answers1