修改MySQL数据库字符集
Preface
I've demonstrated how to change character set in Oracle database in my previous blog.Now,I'm gonna do the similar operation in MySQL database,Let's see the difference of details.
Example
Create a test table.
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
Create a test table.
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]>\s 2 -------------- 3 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using EditLine wrapper 4 5 Connection id: 2 6 Current database: zlm 7 Current user: root@localhost 8 SSL: Not in use 9 Current pager: stdout 10 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 socket 15 Server characterset: utf8 16 Db characterset: utf8 17 Client characterset: utf8 18 Conn. characterset: utf8 19 UNIX socket: /tmp/mysql3306.sock 20 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.00 sec) 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.00 sec) 3 4 root@localhost:mysql3306.sock [zlm]>set @@global.character_set_server=gbk; 5 Query OK, 0 rows affected (0.00 sec) 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.00 sec) 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 #mysql 3 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 its 10 affiliates. Other names may be trademarks of their respective 11 owners. 12 13 Type 'help;' or '\h' for help. Type '\c' to clear the 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.00 sec) 29 30 root@localhost:mysql3306.sock [(none)]>select * from charset; 31 ERROR 1046 (3D000): No database selected 32 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 for completion of table and column names 34 You can turn off this feature to get a quicker startup with -A 35 36 Database changed 37 root@localhost:mysql3306.sock [zlm]>select * from charset; 38 +------+--------+ 39 | id | name | 40 +------+--------+ 41 | 1 | 黎明 | 42 +------+--------+ 43 1 row in set (0.00 sec) 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.00 sec) 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]>\s 56 -------------- 57 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using EditLine wrapper 58 59 Connection id: 6 60 Current database: zlm 61 Current user: root@localhost 62 SSL: Not in use 63 Current pager: stdout 64 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 socket 69 Server characterset: gbk 70 Db characterset: utf8 //The character set of database turns back to utf8.Therefore,no messy code appears. 71 Client characterset: utf8 72 Conn. characterset: utf8 73 UNIX socket: /tmp/mysql3306.sock 74 Uptime: 37 min 4 sec 75 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.00 sec) 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.00 sec) 18 19 root@localhost:mysql3306.sock [zlm]>select * from charset; 20 +------+--------+ 21 | id | name | 22 +------+--------+ 23 | 1 | 黎明 | 24 +------+--------+ 25 1 row in set (0.00 sec) 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.00 sec) 8 9 root@localhost:mysql3306.sock [zlm]>insert into charset values(2,'上海'); 10 Query OK, 1 row affected (0.00 sec) 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.00 sec) 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.01 sec) 3 4 root@localhost:mysql3306.sock [zlm]>set character_set_server=utf8; 5 Query OK, 0 rows affected (0.00 sec) 6 7 root@localhost:mysql3306.sock [zlm]>set names gbk; 8 Query OK, 0 rows affected (0.00 sec) 9 10 root@localhost:mysql3306.sock [zlm]>\s 11 -------------- 12 mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using EditLine wrapper 13 14 Connection id: 8 15 Current database: zlm 16 Current user: root@localhost 17 SSL: Not in use 18 Current pager: stdout 19 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 socket 24 Server characterset: utf8 25 Db characterset: utf8 26 Client characterset: gbk 27 Conn. characterset: gbk 28 UNIX socket: /tmp/mysql3306.sock 29 Uptime: 1 hour 1 min 33 sec 30 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.00 sec) 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.
更多精彩
