最近在将数据从Oracle迁移到MySQL的过程中，遇到一些问题，其中就包括中文字符显示乱码。
数据导入成功之后，中文字段内容无法正常显示。后来才发现客户端的字符集设置是对的，服务器端的字符集设置不支持中文字符。

后来经过测试发现，我们可以通过三种方法解决这个问题，按照从低到高的级别分别是：

• 表级
• 数据库级
• 服务器级

1.测试环境 Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16 

我是在Windows Server 2008 r2环境下进行测试，创建了测试数据库hoegh。

点击(此处)折叠或打开

1. mysql>
2. mysql> create database hoegh;
3. Query OK, 1 row affected (0.00 sec)
4. mysql>
5. mysql> show create database hoegh;
6. +----------+------------------------------------------------------------------+
7. | Database | Create Database |
8. +----------+------------------------------------------------------------------+
9. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET latin1 */ |
10. +----------+------------------------------------------------------------------+
11. 1 row in set (0.00 sec)
12. mysql>
13. mysql> use hoegh;
14. Database changed
15. mysql>
16. mysql> status
17. --------------
18. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
19. Connection id: 2
20. Current database: hoegh
21. Current user: root@localhost
22. SSL: Not in use
23. Using delimiter: ;
24. Server version: 5.7.16 MySQL Community Server (GPL)
25. Protocol version: 10
26. Connection: localhost via TCP/IP
27. Server characterset: latin1
28. Db characterset: utf8
29. Client characterset: utf8
30. Conn. characterset: utf8
31. TCP port: 3306
32. Uptime: 16 min 25 sec
33. Threads: 1 Questions: 18 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.018
34. --------------
35. mysql>

我们看到hoegh数据库的默认字符集是latin1。

2.create table设置DEFAULT CHARSET参数

首先我们创建测试表hoegh，分别向表中插入一条英文信息和中文信息。

点击(此处)折叠或打开

1. mysql>
2. mysql> create table hoegh(id int,name varchar(20));
3. Query OK, 0 rows affected (0.20 sec)
4. mysql> insert into hoegh values(1,'HOEGH');--插入成功
5. Query OK, 1 row affected (0.00 sec)
6. mysql>
7. mysql> insert into hoegh values(1,'霍格');--插入失败
8. ERROR 1366 (HY000):
9. mysql>
10. mysql> show create table hoegh;
11. +-------+------------------------------------------------------------------------------------------------------------------------------
12. | Table | Create Table
13. +-------+------------------------------------------------------------------------------------------------------------------------------
14. | hoegh | CREATE TABLE `hoegh` (
15.   `id` int(11) DEFAULT NULL,
16.   `name` varchar(20) DEFAULT NULL
17. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
18. +-------+------------------------------------------------------------------------------------------------------------------------------
19. 1 row in set (0.02 sec)
20. mysql>

我们发现包含中文的记录插入失败，通过show create table语句可以看到表的默认字符集为latin1，不支持中文。

接下来，我们重建测试表hoegh并设置DEFAULT CHARSET为utf8，然后向表中插入一条英文信息和中文信息。

点击(此处)折叠或打开

1. mysql>
2. mysql> drop table hoegh;
3. Query OK, 0 rows affected (0.13 sec)
4. mysql>
5. mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8;
6. Query OK, 0 rows affected (0.17 sec)
7. mysql> show create table hoegh;
8. +-------+----------------------------------------------------------------------------------------------------------------------------+
9. | Table | Create Table |
10. +-------+----------------------------------------------------------------------------------------------------------------------------+
11. | hoegh | CREATE TABLE `hoegh` (
12.   `id` int(11) DEFAULT NULL,
13.   `name` varchar(20) DEFAULT NULL
14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
15. +-------+----------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)
17. mysql>
18. mysql> insert into hoegh values(1,'HOEGH');--插入成功
19. Query OK, 1 row affected (0.00 sec)
20. mysql> insert into hoegh values(1,'霍格');--插入成功
21. Query OK, 1 row affected (0.00 sec)
22. mysql> select * from hoegh;
23. +------+--------+
24. | id | name |
25. +------+--------+
26. | 1 | HOEGH |
27. | 1 | 霍格 |
28. +------+--------+
29. 2 rows in set (0.00 sec)
30. mysql>

我们看到插入过程没有报错，也能正常查询。

3.create database设置CHARACTER SET参数

我们重建测试库hoegh，并设置CHARACTER SET参数。
然后，重建测试表hoegh并插入两条记录。

点击(此处)折叠或打开

1. mysql>
2. mysql> drop database hoegh;--删除测试库
3. Query OK, 1 row affected (0.11 sec)
4. mysql>
5. mysql> create database hoegh CHARACTER SET 'utf8';--重建测试库hoehg并设置CHARACTER SET参数
6. Query OK, 1 row affected (0.00 sec)
7. mysql> show create database hoegh;
8. +----------+----------------------------------------------------------------+
9. | Database | Create Database |
10. +----------+----------------------------------------------------------------+
11. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
12. +----------+----------------------------------------------------------------+
13. 1 row in set (0.00 sec)
14. mysql>
15. mysql> use hoegh
16. Database changed
17. mysql> create table hoegh(id int,name varchar(20)); 
18. Query OK, 0 rows affected (0.19 sec)
19. mysql> show create table hoegh;
20. +-------+----------------------------------------------------------------------------------------------------------------------------+
21. | Table | Create Table |
22. +-------+----------------------------------------------------------------------------------------------------------------------------+
23. | hoegh | CREATE TABLE `hoegh` (
24.   `id` int(11) DEFAULT NULL,
25.   `name` varchar(20) DEFAULT NULL
26. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
27. +-------+----------------------------------------------------------------------------------------------------------------------------+
28. 1 row in set (0.00 sec)
29. mysql>
30. mysql> insert into hoegh values(1,'HOEGH');--插入成功
31. Query OK, 1 row affected (0.02 sec)
32. mysql> insert into hoegh values(1,'霍格');--插入成功
33. Query OK, 1 row affected (0.00 sec)
34. mysql>
35. mysql> select * from hoegh;
36. +------+--------+
37. | id | name |
38. +------+--------+
39. | 1 | HOEGH |
40. | 1 | 霍格 |
41. +------+--------+
42. 2 rows in set (0.00 sec)
43. mysql>

我们看到插入过程没有报错，也能正常查询。

此时，为了方便对比，我们查看一下系统的字符集参数。

点击(此处)折叠或打开

1. mysql> show variables like '%char%';
2. +--------------------------+-------------------------------------------+
3. | Variable_name | Value |
4. +--------------------------+-------------------------------------------+
5. | character_set_client | utf8 |
6. | character_set_connection | utf8 |
7. | character_set_database | latin1 |
8. | character_set_filesystem | binary |
9. | character_set_results | utf8 |
10. | character_set_server | latin1 |
11. | character_set_system | utf8 |
12. | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
13. +--------------------------+-------------------------------------------+
14. 8 rows in set, 1 warning (0.00 sec)
15. mysql>

我们看到character_set_server参数为latin1，这表明当前服务器端的字符集为latin1。接下来，我们就通过修改参数文件来修改这个参数。

4.配置my.ini中的character_set_server参数

通过直接配置my.ini方式修改mysql的服务器端字符集，这样就可以一劳永逸。
首先我们找到配置文件，在里面添加一行记录——character-set-server=utf8。

点击(此处)折叠或打开

1. [mysql]
2. default-character-set=utf8
3. [mysqld]
4. max_connections=200
5. default-storage-engine=INNODB
6. basedir =E:\mysql-5.7.16-winx64\bin
7. datadir =E:\mysql-5.7.16-winx64\data
8. port = 3306
9. autocommit=0
10. character-set-server=utf8

需要注意的是，上述配置中default-character-set=utf8是针对客户端的设置，
而character-set-server=utf8是针对服务器端的设置。

接下来，我们重启一下MySQL服务，查看数据库的字符集参数。

点击(此处)折叠或打开

1. E:\mysql-5.7.16-winx64\bin>net stop mysql
2. MySQL 服务正在停止.
3. MySQL 服务已成功停止。
4. E:\mysql-5.7.16-winx64\bin>
5. E:\mysql-5.7.16-winx64\bin>net start mysql
6. MySQL 服务正在启动 .
7. MySQL 服务已经启动成功。
8. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
9. mysql: [Warning] Using a password on the command line interface can be insecure.
10. Welcome to the MySQL monitor. Commands end with ; or \g.
11. Your MySQL connection id is 2
12. Server version: 5.7.16 MySQL Community Server (GPL)
13. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
14. Oracle is a registered trademark of Oracle Corporation and/or its
15. affiliates. Other names may be trademarks of their respective
16. owners.
17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18. mysql> show variables like '%char%';
19. +--------------------------+-------------------------------------------+
20. | Variable_name | Value |
21. +--------------------------+-------------------------------------------+
22. | character_set_client | utf8 |
23. | character_set_connection | utf8 |
24. | character_set_database | utf8 |
25. | character_set_filesystem | binary |
26. | character_set_results | utf8 |
27. | character_set_server | utf8 |
28. | character_set_system | utf8 |
29. | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
30. +--------------------------+-------------------------------------------+
31. 8 rows in set, 1 warning (0.00 sec)
32. mysql>

我们看到character_set_server已经成功设置为utf8。然后我们重建测试数据库hoegh，并重复之前的建表语句。

点击(此处)折叠或打开

1. mysql>
2. mysql> drop database hoegh;
3. Query OK, 1 row affected (0.16 sec)
4. mysql> create database hoegh;--重建测试数据库hoegh
5. Query OK, 1 row affected (0.00 sec)
6. mysql>
7. mysql> show create database hoegh;
8. +----------+----------------------------------------------------------------+
9. | Database | Create Database |
10. +----------+----------------------------------------------------------------+
11. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
12. +----------+----------------------------------------------------------------+
13. 1 row in set (0.00 sec)
14. mysql>
15. mysql> use hoegh;
16. Database changed
17. mysql> create table hoegh(id int,name varchar(20));
18. Query OK, 0 rows affected (0.17 sec)
19. mysql>
20. mysql> show create table hoegh;
21. +-------+----------------------------------------------------------------------------------------------------------------------------+
22. | Table | Create Table |
23. +-------+----------------------------------------------------------------------------------------------------------------------------+
24. | hoegh | CREATE TABLE `hoegh` (
25.   `id` int(11) DEFAULT NULL,
26.   `name` varchar(20) DEFAULT NULL
27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
28. +-------+----------------------------------------------------------------------------------------------------------------------------+
29. 1 row in set (0.00 sec)
30. mysql>
31. mysql> insert into hoegh values(1,'HOEGH');
32. Query OK, 1 row affected (0.00 sec)
33. mysql> insert into hoegh values(1,'霍格');
34. Query OK, 1 row affected (0.00 sec)
35. mysql> select * from hoegh;
36. +------+--------+
37. | id | name |
38. +------+--------+
39. | 1 | HOEGH |
40. | 1 | 霍格 |
41. +------+--------+
42. 2 rows in set (0.00 sec)
43. mysql>

我们看到一切正常，搞定！

最后强烈建议大家使用utf8！号称万国码！