How to Correctly Convert Latin1 Characters to UTF-8 in a MySQL Database?

Convert Latin1 Characters in a UTF-8 Table to UTF-8

Encoding Discrepancy:

You have discovered a misalignment between PHP and MySQL encoding settings, leading to incorrect storage of diacritical characters in a UTF-8 table. While new inserts are now correct, older rows still contain corrupted characters.

Corrective Measure:

To rectify this issue, utilize MySQL's convert() function:

<code class="sql">convert(cast(convert(name using  latin1) as binary) using utf8)</code>

This function converts Latin1-encoded data into UTF-8. The double conversion ensures that the data is not truncated during the conversion process.

Example Usage:

In your code, apply the conversion within your update query:

<code class="sql">mysql_iquery('UPDATE `table` SET `name`="'.mysql_real_escape_string(convert(cast(convert(name using  latin1) as binary) using utf8)).'" WHERE `a1`="'.$row['a1'].'"');</code>

This will update the name column in your table with the correctly converted UTF-8 characters.

Note:

Depending on the data alteration during the encoding conversion, you may need to omit the inner convert function from the query. Test both scenarios and adopt the one that yields the desired results.

The above is the detailed content of How to Correctly Convert Latin1 Characters to UTF-8 in a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!