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

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

表级
数据库级
服务器级

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

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

点击(此处)折叠或打开

mysql>
mysql> create database hoegh;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show create database hoegh;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use hoegh;
Database changed
mysql>
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
Connection id: 2
Current database: hoegh
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.16 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 16 min 25 sec
Threads: 1 Questions: 18 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.018
--------------
mysql>

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

2.create table设置DEFAULT CHARSET参数

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

点击(此处)折叠或打开

mysql>
mysql> create table hoegh(id int,name varchar(20));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into hoegh values(1,'HOEGH');--插入成功
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into hoegh values(1,'霍格');--插入失败
ERROR 1366 (HY000):
mysql>
mysql> show create table hoegh;
+-------+------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------------------------------------------------------------------------
| hoegh | CREATE TABLE `hoegh` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)
mysql>

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

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

点击(此处)折叠或打开

mysql>
mysql> drop table hoegh;
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)
mysql> show create table hoegh;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| hoegh | CREATE TABLE `hoegh` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> insert into hoegh values(1,'HOEGH');--插入成功
Query OK, 1 row affected (0.00 sec)
mysql> insert into hoegh values(1,'霍格');--插入成功
Query OK, 1 row affected (0.00 sec)
mysql> select * from hoegh;
+------+--------+
| id | name |
+------+--------+
| 1 | HOEGH |
| 1 | 霍格 |
+------+--------+
2 rows in set (0.00 sec)
mysql>

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

3.create database设置CHARACTER SET参数

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

点击(此处)折叠或打开

mysql>
mysql> drop database hoegh;--删除测试库
Query OK, 1 row affected (0.11 sec)
mysql>
mysql> create database hoegh CHARACTER SET 'utf8';--重建测试库hoehg并设置CHARACTER SET参数
Query OK, 1 row affected (0.00 sec)
mysql> show create database hoegh;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use hoegh
Database changed
mysql> create table hoegh(id int,name varchar(20));
Query OK, 0 rows affected (0.19 sec)
mysql> show create table hoegh;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| hoegh | CREATE TABLE `hoegh` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> insert into hoegh values(1,'HOEGH');--插入成功
Query OK, 1 row affected (0.02 sec)
mysql> insert into hoegh values(1,'霍格');--插入成功
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from hoegh;
+------+--------+
| id | name |
+------+--------+
| 1 | HOEGH |
| 1 | 霍格 |
+------+--------+
2 rows in set (0.00 sec)
mysql>

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

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

点击(此处)折叠或打开

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql>

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

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

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

点击(此处)折叠或打开

[mysql]
default-character-set=utf8
[mysqld]
max_connections=200
default-storage-engine=INNODB
basedir =E:\mysql-5.7.16-winx64\bin
datadir =E:\mysql-5.7.16-winx64\data
port = 3306
autocommit=0
character-set-server=utf8

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

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

点击(此处)折叠或打开

E:\mysql-5.7.16-winx64\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。
E:\mysql-5.7.16-winx64\bin>
E:\mysql-5.7.16-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql>

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

点击(此处)折叠或打开

mysql>
mysql> drop database hoegh;
Query OK, 1 row affected (0.16 sec)
mysql> create database hoegh;--重建测试数据库hoegh
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show create database hoegh;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> use hoegh;
Database changed
mysql> create table hoegh(id int,name varchar(20));
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> show create table hoegh;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| hoegh | CREATE TABLE `hoegh` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> insert into hoegh values(1,'HOEGH');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hoegh values(1,'霍格');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hoegh;
+------+--------+
| id | name |
+------+--------+
| 1 | HOEGH |
| 1 | 霍格 |
+------+--------+
2 rows in set (0.00 sec)
mysql>

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

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

Windows环境下MySQL5.7中文显示乱码的三种解决方案

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

2.create table设置DEFAULT CHARSET参数

3.create database设置CHARACTER SET参数

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像