How Can I Recover Corrupted Latin1 Characters in UTF8 MySQL Tables?

Recovering Latin1 Characters in UTF8 Tables: A Solution

As you've mentioned, setting the character set between PHP and MySQL ensures new inserts are stored correctly. However, recovering old data with diacritics that were previously stored as Latin1 and later corrupted can be challenging.

To resolve this issue, you can utilize the MySQL conversion function:

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

By default, this function can repair most corrupted data without any further modifications. However, you may need to adjust the inner conversion based on how the data was altered during the initial encoding conversion.

Consider the following example:

$result = mysql_iquery('SELECT * FROM `table`');
while ($row = mysql_fetch_assoc($result)) {
    $message = $row['name'];
    $message = convert(cast(convert($message using latin1) as binary) using utf8);
    mysql_iquery('UPDATE `table` SET `name`="'.mysql_real_escape_string($message).'" WHERE `a1`="'.$row['a1'].'"');
}

By using this function, you should be able to recover the corrupted diacritics and update the affected rows correctly.

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