2

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??

shohidul
  • 37
  • 4
  • Why would you want to convert to `utf8_general_ci`. If you want to use UTF-8 you must use `utf8mb4_general_ci` instead. – Dharman Oct 10 '20 at 11:31
  • Seems like there is no easy way to do such a work. You must create another table with chosen encodeing pattern and then insert data into it. At last, you may need change this new table's name to whatever you want. – Light.G Oct 10 '20 at 15:48

1 Answers1

1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222