How to Fix Broken UTF-8 Encoding in MySQL Using mysqldump and mysql?

Fixing Broken UTF-8 Encoding

Problem:

Encountering broken UTF-8 characters (e.g., î) in MySQL database, despite using PHP header and UTF-8 settings in Notepad .

Solution:

To resolve the broken encoding, follow these steps:

1. Dump the database data to a file using mysqldump. Ensure that character set conversion is not performed during the dump by using --skip-set-charset and specifying latin1 as the default character set.
2. Import the dumped data back into the database using mysql. Set the default character set to utf8 during the import to correctly interpret the data.

Code:

# Dump data with latin1 character set
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

# Import data with UTF-8 character set
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
    --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

Explanation:

This method replaces the broken UTF-8 characters with their proper values by reconstructing the data using MySQL commands. It ensures that the encoding is correctly set both during export and import.

The above is the detailed content of How to Fix Broken UTF-8 Encoding in MySQL Using mysqldump and mysql?. For more information, please follow other related articles on the PHP Chinese website!