Character set scrambling in mysql

#View the character set of mysql database

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)


Tags: Linux MySQL Database

Posted on Tue, 03 Dec 2019 10:31:29 -0500 by bigsid