Home > Database > Mysql Tutorial > How Can I Correct Latin1 Characters Incorrectly Stored as UTF8 in MySQL?

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

DDD
Release: 2024-12-03 15:06:10
Original
415 people have browsed it

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)
Copy after login

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]%'
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template