How to Convert Latin1 Characters in a UTF8 Table to UTF8?

Convert latin1 characters on a UTF8 table into UTF8

You have noticed that you missed inserting mysql_set_charset('utf8') in your PHP scripts, and now all the tables are in utf8_unicode_ci collation, making the insertion of characters with diacritics not behave properly.

Previously, every time you tried to insert something with diacritics, the data would be shown incorrectly due to the mismatch in character sets. However, after fixing the charset between PHP and MySQL, new insertions are now being stored correctly.

The next step is to fix all the older rows that are still messed up. You have tried using mb_convert_encoding and iconv, but the strings are getting truncated after a certain character.

To recover the data, you can use a MySQL function like:

convert(cast(convert(name using latin1) as binary) using utf8)

Depending on how the data was altered during the encoding conversion, you may need to omit the inner conversion. This function will convert the data from Latin-1 to binary and then to UTF-8, ensuring that all the characters are displayed correctly.

The above is the detailed content of How to Convert Latin1 Characters in a UTF8 Table to UTF8?. For more information, please follow other related articles on the PHP Chinese website!