修改MySQL数据库字符集

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)

 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:        stdout10 Using outfile:        ''11 Using delimiter:    ;12 Server version:        5.7.21-log MySQL Community Server (GPL)13 Protocol version:    1014 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

 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)

 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)

 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 its10 affiliates. Other names may be trademarks of their respective11 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 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 for completion 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.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]>s56 --------------57 mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper58 59 Connection id:        660 Current database:    zlm61 Current user:        root@localhost62 SSL:            Not in use63 Current pager:        stdout64 Using outfile:        ''65 Using delimiter:    ;66 Server version:        5.7.21-log MySQL Community Server (GPL)67 Protocol version:    1068 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 4 sec75 76 Threads: 2  Questions: 116  Slow queries: 0  Opens: 120  Flush tables: 1  Open tables: 113  Queries per second avg: 0.05277 --------------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)

 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)

 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.

 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]>s11 --------------12 mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper13 14 Connection id:        815 Current database:    zlm16 Current user:        root@localhost17 SSL:            Not in use18 Current pager:        stdout19 Using outfile:        ''20 Using delimiter:    ;21 Server version:        5.7.21-log MySQL Community Server (GPL)22 Protocol version:    1023 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 33 sec30 31 Threads: 1  Questions: 144  Slow queries: 0  Opens: 123  Flush tables: 1  Open tables: 116  Queries per second avg: 0.03832 --------------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.

1 root@localhost:mysql3306.sock [zlm]>insert into charset values(3,'中国');2 ERROR 1366 (HY000): Incorrect string value: 'xADxE5x9BxBD' for column 'name' at row 13 4 //It doesn't permit your insertion operation now 'cause they'll be messy code again.

• 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.