Write down the troubleshooting of garbled Chinese characters in mysql
Today, it is normal to develop programs that respond differently to insert data into one library, while it is garbled to insert data into another library. The first reaction is that the two databases have different configurations for character sets.
View the parameters in the two libraries:
Show variables like "char%"
+-+
| | Variable_name | Value |
+-+
| | character_set_client | utf8 |
| | character_set_connection | utf8 |
| | character_set_database | utf8 |
| | character_set_filesystem | binary |
| | character_set_results | utf8 |
| | character_set_server | latin1 |
| | character_set_system | utf8 |
| | character_sets_dir | / usr/local/mysql/share/charsets/ |
+-+
> show variables like "char%"
+-+
| | Variable_name | Value |
+-+
| | character_set_client | utf8 |
| | character_set_connection | utf8 |
| | character_set_database | utf8 |
| | character_set_filesystem | binary |
| | character_set_results | utf8 |
| | character_set_server | utf8 |
| | character_set_system | utf8 |
| | character_sets_dir | / usr/local/mysql/share/charsets/ |
+-+
You can see that the parameter configuration of character_set_server is different, so the library in question is configured as utf8. When the developer tests again, it indicates that there is a problem of garbled code. Then remember that the check character set has not been changed, so check the differences between the two check character sets:
> show variables like "coll%"
+-+ +
| | Variable_name | Value |
+-+ +
| | collation_connection | utf8_general_ci |
| | collation_database | utf8_general_ci |
| | collation_server | latin1_swedish_ci |
+-+ +
> show variables like "coll%"
+-+ +
| | Variable_name | Value |
+-+ +
| | collation_connection | utf8_general_ci |
| | collation_database | utf8_general_ci |
| | collation_server | utf8_general_ci |
+-+ +
You can see that the setting of the collation_server parameter is inconsistent, so change this parameter to utf8, and the data looks normal when you test it again.
Mysql supports multiple levels of character set settings:
Service layer (server), database layer (database), data table (table), field (column), connection (connection), result set (result)
Priority: server > database > table > column
In order to avoid the problem of Chinese garbled caused by character inconsistency, it is best to set all the character sets to the same.
Also mention the parameter skip-character-set-client-handshake. You can filter the character set set by the client by turning on this parameter.
