how to encode existing mysql data latin1 to utf8_general_ci

user_10212821 0
Viewed 95

In the past, my DB collation and table were latin1 or like this. And stored some data on my database.

But recently changed My DB collation UTF8 and table utf8_general_ci, but my previous data still latin1.

I want to convert my existing table data to utf8_general_ci. How to??

collation mysql
1 Answers

Are you seeing any "problems"? The encoding of a column and the encoding in the client do not have to be the same; MySQL will convert on the fly.

If you want to add Asian characters or Emoji, then you do need utf8mb4 (or at least utf8). Before embarking on a conversion, please discuss the scope of the problem. In particular, if you are seeing 'garbage', look at Trouble with UTF-8 characters; what I see is not what I stored for diagnosing the problem.

Assuming you have correctly encoded data in your table,

ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Here are 7 possible cases; you seem to have the first case: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

If you have already stored, say, Emoji in the latin1 column, you have a worse mess; we need to discuss further.

Terminology:

  • The CHARACTER SET is what bits are used to encode characters. Eg: latin1, utf8, utf8mb4.
  • The COLLATION is how characters are compared, as for WHERE and ORDER BY. Eg: latin1_general_ci, utf8_general_ci, utf8mb4_unicode_520_ci`.

latin1 can handle all Western European characters.
utf8 can handle most characters worldwide.
utf8mb4 is a superset of utf8, and is needed for Emoji and some Chinese characters.

user_17668310
Related