How Can I Correct Latin1 Characters Incorrectly Stored as UTF8 in MySQL?

Correcting Latin1 Characters Stored as UTF8 in a MySQL Table

Your issue arises from improper conversion of Latin1 data into UTF8 during insertion. Despite setting the correct character sets, the older data remains corrupted with incorrect characters. To rectify this situation, we need to convert the affected data to the correct UTF8 representation.

The recommended solution involves utilizing the MySQL function:

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

This function takes the name column data, interprets it as Latin1 binary, and then converts it to UTF8. The outer conversion to binary ensures the data is handled as raw data, preventing further character set conversions from corrupting it.

Here's an example query:

UPDATE `table` SET `name` = convert(cast(convert(name using latin1) as binary) using utf8)
WHERE `name` LIKE '%[non-UTF8 characters]%'

This query updates all rows with non-UTF8 characters in the name column, ensuring the data is converted correctly.

Note: If the original encoding process altered the data slightly, you may need to omit the inner conversion to Latin1 binary in the convert function call.

The above is the detailed content of How Can I Correct Latin1 Characters Incorrectly Stored as UTF8 in MySQL?. For more information, please follow other related articles on the PHP Chinese website!