The problem analysis of Mysql storing data garbled _mysql

MySQL's character set has more than one level, in the MySQL store Chinese, if the character set can not be correct, it is easy to appear garbled data. Today, there's a user who feeds him. Data in the database 1 o'clock in the afternoon began to appear garbled. Here, I share the process of troubleshooting specific issues, and the solution.

(1) Exclude client settings resulting in garbled display

If the user set the MySQL character_set_client with the client display of inconsistent character sets, it is easy to cause Chinese data garbled.

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

Shown above, in the character_set_client with the client's character set consistent with the case, or appear garbled, this exclusion is the user to display the wrong character set is not possible. The following hex (item_title) column is used to see if the underlying storage character set is correct.

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

(2) Location store garbled reason

1> users confirm that this record can be inserted when the normal display, but later update, the data is garbled. Based on this information, find the UPDATE statement in Binlog that corresponds to the correct content change.

The above Binlog log shows this SQL will be the original database in the correct content, updated into a heap of garbled. So the stored data in the database is garbled.

You can see from the Binlog log that when you update, you write to the database in a latin1 way. The contents of the Item_title field in the SET statement after update are garbled, so it is confirmed that there is a problem with the import data source itself, resulting in the updated data garbled. With the user to confirm the UPDATE statement updates, is first from the library load out, after the mosaic of the update SQL, so the suspect load out of the data is already garbled, and then directly with the wrong data to update the original correct data, resulting in all the correct data garbled. Therefore, you need to verify that the data source that this update is imported is correct, that is, whether the data that is load is correct.

2> Import Data Source Confirmation

Turn on the full-time log switch for the instance, and then look for data export statements, and corresponding character set settings, from the SQL running from the connection to the UPDATE statement above.

From the above log content can be seen, this connection was established without any set of character sets, directly from the database to select the content. In MySQL, if you do not set a session-level character set, use the default configuration as follows:

That is, the output is displayed in latin1 format. Under the configuration of the default character set, manually run select ' main_table '. * from ' promo_item ' as ' main_table ' WHERE promo_item_id = ' 500186324 ' command, can be found, In the case where Character_set_results is set to Latin1, the item_title in the output is indeed a bunch of question marks.

Because Latin1 does not correctly represent Chinese characters, it is displayed as a bunch of question marks, and the user updates the content directly to the original content, causing the stored content to be garbled.

(3) Summary

When using MySQL to store Chinese characters, you need to be aware of the following points:

1> confirms that the updated data source is consistent with the MySQL session-level character set, the session-level character set can be set with set names Charset_name.

2> If you want to display Chinese correctly, you need to set Character_set_results to GBK or UTF8. At the same time, the client's display character set needs to be consistent with the Character_set_results configuration.