MySQL stores garbled data and mysql stores garbled data

MySQL stores garbled data and mysql stores garbled data

Mysql character set has multiple levels and stores Chinese Characters in mysql. If the character set cannot be correctly set, data garbled characters may easily occur. Today, a user reported that the data in his database had been garbled at one o'clock P.M. Here, I will share with you the troubleshooting process and solutions for specific problems.

(1) Eliminate the garbled display caused by client settings

If the character set of the mysql character_set_client you set is inconsistent with the character set displayed on the client, Chinese data may be garbled.

Set the session character set to utf8: set names utf8, set the display character set on the client to utf8, and select garbled data from the table.

As shown above, garbled characters still occur when character_set_client is consistent with the character set of the client. This exclusion is caused by incorrect display character set settings. The following uses the hex (item_title) column to check whether the underlying storage Character Set of this column is correct.

Through the above query, we can confirm that the garbled data is not a display problem, but the stored data content is itself incorrect.

(2) identify the cause of storage garbled data

1> the user confirms that this record can be normally displayed during insertion, but after the update, the data will be garbled. Find the update statement corresponding to the correct content in binlog based on this information.

The preceding binlog shows that the SQL statement updates the correct content in the original database into a pile of garbled characters. As a result, the stored data in the database is garbled.

From the binlog, we can see that latin1 is used to write data to the database during update. The content of the item_title field in the set statement after Update is garbled, so it is confirmed that there is a problem with the content of the imported data source itself, resulting in garbled data after Update. Confirm with the user that the update content of this update statement is first loaded from the database and then merged into an update SQL statement. Therefore, it is suspected that the load data is garbled, then, the correct data is directly updated with the wrong data, resulting in garbled data. Therefore, check whether the data source imported by the update operation is correct, that is, whether the data loaded is correct.

2> confirm the import data source

Enable the full log switch of the instance, compare the log, and search for the data Export Statement and Character Set settings from the SQL statement corresponding to the update statement above.

From the preceding log Content, we can see that no character set is set after the connection is established, and the content is directly selected from the database. In mysql, if the session-level character set is not set, use the default configuration as follows:

That is, the output is displayed in latin1 format. In the default character set configuration, manually run SELECT 'main _ Table '. * FROM the 'promo _ item' AS 'main _ table' WHERE promo_item_id = '2013' command, you can find that when character_set_results is set to latin1, the item_title in the output result is indeed a pile of question marks.

Because latin1 does not correctly represent Chinese characters, it is displayed as a bunch of question marks. You can directly update the original correct content, resulting in garbled content.

(3) Summary

Note the following when using mysql to store Chinese characters:

1> confirm that the updated data source is consistent with the mysql session-level character set. set names charset_name can be used for Session-level character sets.

2> to correctly display Chinese characters, set character_set_results to GBK or utf8. At the same time, the display character set of the client must be consistent with the character_set_results configuration.