Character set scrambling in mysql

mysql> show variables like "%character%"; +--------------------------+-----------------------------+ | 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 | /data/mysql/share/charsets/ | +--------------------------+-----------------------------+ 8 rows in set (0.01 sec)

#The client's character set when viewing

mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 sec)

#When the client queries, the database returns the character set used by the client

mysql> set character_set_results=gbk; Query OK, 0 rows affected (0.00 sec)

#When data is stored, the connector can use both gbk and UTF. When data is stored, it will be converted to utf8

set character_set_connection=utf8;

If the character set is different when the client is inserted and removed, it will be garbled?

#Set the character set of the client to utf8

mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 sec)

#Create database t5

mysql> create table t5 ( name char(20) ); Query OK, 0 rows affected (0.02 sec)

#Insert the character set utf8 used;

mysql> insert into t5 values ("China"); Query OK, 1 row affected (0.00 sec)

#No problem checking

mysql> select * from t5; +--------+ | name | +--------+ | China | +--------+ 1 row in set (0.00 sec)

#The character set that the server returns to gbk when fetching

mysql> set character_set_results=gbk; Query OK, 0 rows affected (0.00 sec)

#When setting the server to return, gbk will be garbled

mysql> select * from t5; +------+ | name | +------+ | א¹| +------+ 1 row in set (0.02 sec)

Note: the client xshell link I use is utf8 character set

#If the client / connector / server is set to utf8, it will not be scrambled

mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_connection=utf8; Query OK, 0 rows affected (0.00 sec) mysql> set character_set_results=utf8; Query OK, 0 rows affected (0.00 sec) mysql> select * from t5; +--------+ | name | +--------+ | China | +--------+ 1 row in set (0.00 sec)

#Using names, you can directly set all three to the same character set

mysql> set names gbk; Query OK, 0 rows affected (0.00 sec)

#Looking at the character set, it is found that clien/connection/results all change to gbk

mysql> show variables like "%character%"; +--------------------------+-----------------------------+ | Variable_name | Value | +--------------------------+-----------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /data/mysql/share/charsets/ | +--------------------------+-----------------------------+ 8 rows in set (0.00 sec)

3 December 2019, 10:31 | Views: 4889