首页 > > 网络编程 > Mysql >

修改MySQL数据库字符集

2018-07-16 02:48:21来源：博客园阅读 ()

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.

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 | ?o￡    |
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.