1 root@localhost:mysql3306.sock [zlm]>create table charset(2 -> id int,3 -> name varchar(10)4 -> ) engine=innodb charset=utf8;5 Query OK, 0 rows affected (0.01 sec)
Check the character set.
1 root@localhost:mysql3306.sock [zlm]>\s2 --------------
3 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12(x86_64) using EditLine wrapper4
5 Connection id: 2
6 Current database: zlm7 Current user: root@localhost8 SSL: Not inuse9 Current pager: stdout10 Using outfile: ''
11 Using delimiter: ;12 Server version: 5.7.21-log MySQL Community Server (GPL)13 Protocol version: 10
14 Connection: Localhost via UNIX socket15 Server characterset: utf816 Db characterset: utf817 Client characterset: utf818 Conn. characterset: utf819 UNIX socket: /tmp/mysql3306.sock20 Uptime: 29 min 38 sec
Insert a record contains Chinese characters into test table.
1 root@localhost:mysql3306.sock [zlm]>insert into charset values(1,'黎明');2 Query OK, 1 row affected (0.00sec)3
4 root@localhost:mysql3306.sock [zlm]>select *from charset;5 +------+--------+
6 | id | name |
7 +------+--------+
8 | 1 | 黎明 |
9 +------+--------+
10 1 row in set (0.00 sec)
Change the character from utf8 to to gbk.
1 root@localhost:mysql3306.sock [zlm]>set @@global.character_set_database=gbk;2 Query OK, 0 rows affected, 1 warning (0.00sec)3
4 root@localhost:mysql3306.sock [zlm]>set @@global.character_set_server=gbk;5 Query OK, 0 rows affected (0.00sec)6
7 root@localhost:mysql3306.sock [zlm]>show global variables like 'character%';8 +--------------------------+----------------------------------------------------------------+
9 | Variable_name | Value |
10 +--------------------------+----------------------------------------------------------------+
11 | character_set_client | utf8 |
12 | character_set_connection | utf8 |
13 | character_set_database | gbk |
14 | character_set_filesystem | binary |
15 | character_set_results | utf8 |
16 | character_set_server | gbk |
17 | character_set_system | utf8 |
18 | character_sets_dir | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
19 +--------------------------+----------------------------------------------------------------+
20 8 rows in set (0.00sec)21
22 root@localhost:mysql3306.sock [zlm]>show variables like 'character%';23 +--------------------------+----------------------------------------------------------------+
24 | Variable_name | Value |
25 +--------------------------+----------------------------------------------------------------+
26 | character_set_client | utf8 |
27 | character_set_connection | utf8 |
28 | character_set_database | utf8 |
29 | character_set_filesystem | binary |
30 | character_set_results | utf8 |
31 | character_set_server | utf8 |
32 | character_set_system | utf8 |
33 | character_sets_dir | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
34 +--------------------------+----------------------------------------------------------------+
35 8 rows in set (0.00 sec)
Start a new mysql client and check the data in test table.
1 [root@zlm1 13:51:24 ~]2 #mysql3 Welcome to the MySQL monitor. Commands end with ; or \g.4 Your MySQL connection id is 6
5 Server version: 5.7.21-log MySQL Community Server (GPL)6
7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.8
9 Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective11 owners.12
13 Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.14
15 root@localhost:mysql3306.sock [(none)]>show variables like 'character%';16 +--------------------------+----------------------------------------------------------------+
17 | Variable_name | Value |
18 +--------------------------+----------------------------------------------------------------+
19 | character_set_client | utf8 |
20 | character_set_connection | utf8 |
21 | character_set_database | gbk |
22 | character_set_filesystem | binary |
23 | character_set_results | utf8 |
24 | character_set_server | gbk |
25 | character_set_system | utf8 |
26 | character_sets_dir | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
27 +--------------------------+----------------------------------------------------------------+
28 8 rows in set (0.00sec)29
30 root@localhost:mysql3306.sock [(none)]>select *from charset;31 ERROR 1046(3D000): No database selected32 root@localhost:mysql3306.sock [(none)]>use zlm //After execute "use database",the character set of database will turn into utf8 again.
33 Reading table information forcompletion of table and column names34 You can turn off this feature to get a quicker startup with -A35
36 Database changed37 root@localhost:mysql3306.sock [zlm]>select *from charset;38 +------+--------+
39 | id | name |
40 +------+--------+
41 | 1 | 黎明 |
42 +------+--------+
43 1 row in set (0.00sec)44
45 root@localhost:mysql3306.sock [zlm]>select length('黎明') from dual;46 +------------------+
47 | length('黎明') |
48 +------------------+
49 | 6 | //The length of one Chinese character occupys three bytes.It depends on the character set of table.
50 +------------------+
51 1 row in set (0.00sec)52
53 //The data still shows correct result after change the database and server character set to gbk.
54
55 root@localhost:mysql3306.sock [zlm]>\s56 --------------
57 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12(x86_64) using EditLine wrapper58
59 Connection id: 6
60 Current database: zlm61 Current user: root@localhost62 SSL: Not inuse63 Current pager: stdout64 Using outfile: ''
65 Using delimiter: ;66 Server version: 5.7.21-log MySQL Community Server (GPL)67 Protocol version: 10
68 Connection: Localhost via UNIX socket69 Server characterset: gbk70 Db characterset: utf8 //The character set of database turns back to utf8.Therefore,no messy code appears.
71 Client characterset: utf872 Conn. characterset: utf873 UNIX socket: /tmp/mysql3306.sock74 Uptime: 37 min 4sec75
76 Threads: 2 Questions: 116 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 113 Queries per second avg: 0.052
77 --------------
78
79 root@localhost:mysql3306.sock [zlm]>show variables like 'character%';80 +--------------------------+----------------------------------------------------------------+
81 | Variable_name | Value |
82 +--------------------------+----------------------------------------------------------------+
83 | character_set_client | utf8 |
84 | character_set_connection | utf8 |
85 | character_set_database | utf8 |
86 | character_set_filesystem | binary |
87 | character_set_results | utf8 |
88 | character_set_server | gbk |
89 | character_set_system | utf8 |
90 | character_sets_dir | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
91 +--------------------------+----------------------------------------------------------------+
92 8 rows in set (0.01 sec)
Set the character set again in curren session to gbk.
1 root@localhost:mysql3306.sock [zlm]>set character_set_database=gbk;2 Query OK, 0 rows affected, 1 warning (0.00sec)3
4 root@localhost:mysql3306.sock [zlm]>show variables like 'character%';5 +--------------------------+----------------------------------------------------------------+
6 | Variable_name | Value |
7 +--------------------------+----------------------------------------------------------------+
8 | character_set_client | utf8 |
9 | character_set_connection | utf8 |
10 | character_set_database | gbk |
11 | character_set_filesystem | binary |
12 | character_set_results | utf8 |
13 | character_set_server | gbk |
14 | character_set_system | utf8 |
15 | character_sets_dir | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
16 +--------------------------+----------------------------------------------------------------+
17 8 rows in set (0.00sec)18
19 root@localhost:mysql3306.sock [zlm]>select *from charset;20 +------+--------+
21 | id | name |
22 +------+--------+
23 | 1 | 黎明 |
24 +------+--------+
25 1 row in set (0.00sec)26
27 //Change the character set of client tool(mine is Xshell) to gbk.
28
29 root@localhost:mysql3306.sock [zlm]>select *from charset;30 +------+--------+
31 | id | name |
32 +------+--------+
33 | 1 | 榛庢槑 | //After changing the character set of client tool,the messy code occurs.
34 +------+--------+
35 1 row in set (0.00 sec)
Change the character set of client tool back to utf8 and insert another record into test table.
1 root@localhost:mysql3306.sock [zlm]>select *from charset;2 +------+--------+
3 | id | name |
4 +------+--------+
5 | 1 | 黎明 |
6 +------+--------+
7 1 row in set (0.00sec)8
9 root@localhost:mysql3306.sock [zlm]>insert into charset values(2,'上海');10 Query OK, 1 row affected (0.00sec)11
12 root@localhost:mysql3306.sock [zlm]>select *from charset;13 +------+--------+
14 | id | name |
15 +------+--------+
16 | 1 | 黎明 |
17 | 2 | 上海 |
18 +------+--------+
19 2 rows in set (0.00sec)20
21 //The changing of character set from utf8 to gbk does not influence the result of Chinese characters.
Change the character set of database & server to utf8 again.Then,change the character set of client & connection to gbk.
1 root@localhost:mysql3306.sock [zlm]>set character_set_database=utf8;2 Query OK, 0 rows affected, 1 warning (0.01sec)3
4 root@localhost:mysql3306.sock [zlm]>set character_set_server=utf8;5 Query OK, 0 rows affected (0.00sec)6
7 root@localhost:mysql3306.sock [zlm]>set names gbk;8 Query OK, 0 rows affected (0.00sec)9
10 root@localhost:mysql3306.sock [zlm]>\s11 --------------
12 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12(x86_64) using EditLine wrapper13
14 Connection id: 8
15 Current database: zlm16 Current user: root@localhost17 SSL: Not inuse18 Current pager: stdout19 Using outfile: ''
20 Using delimiter: ;21 Server version: 5.7.21-log MySQL Community Server (GPL)22 Protocol version: 10
23 Connection: Localhost via UNIX socket24 Server characterset: utf825 Db characterset: utf826 Client characterset: gbk27 Conn. characterset: gbk28 UNIX socket: /tmp/mysql3306.sock29 Uptime: 1 hour 1 min 33sec30
31 Threads: 1 Questions: 144 Slow queries: 0 Opens: 123 Flush tables: 1 Open tables: 116 Queries per second avg: 0.038
32 --------------
33
34 root@localhost:mysql3306.sock [zlm]>root@localhost:mysql3306.sock [zlm]>select *from charset;35 +------+------+
36 | id | name |
37 +------+------+
38 | 1 | hķ |
39 | 2 | ʏº£ |
40 +------+------+
41 2 rows in set (0.00sec)42
43 //The messy code occured after I've changed the character of my client tool to utf8.
Insert the third record with Chinese characters.
1 root@localhost:mysql3306.sock [zlm]>insert into charset values(3,'中国');2 ERROR 1366 (HY000): Incorrect string value: '\xAD\xE5\x9B\xBD' for column 'name' at row 1
3
4 //It doesn't permit your insertion operation now 'cause they'll be messy code again.
Summary
Cheracter set in MySQL does not make a large influence even though it has so many variables which may confuse us.
We can specify character set in a single table or even a column of the table which oracle cannot support.
In order to avoid messy code,make sure to keep character set of connection is bigger or equal with the one of our client tool.
It's reccomended to use utf8 even utf8mb4 as the character set of MySQL database because it can support almost all the languages
Notice that the character set of database may change after you execute "use xxx" to choose a target database.
834
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
