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

2021-10-21 21:36:33

最近在将数据从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)

  5. mysql>
  6. mysql> show create database hoegh;
  7. +----------+------------------------------------------------------------------+
  8. | Database | Create Database |
  9. +----------+------------------------------------------------------------------+
  10. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  11. +----------+------------------------------------------------------------------+
  12. 1 row in set (0.00 sec)

  14. mysql>
  15. mysql> use hoegh;
  16. Database changed
  17. mysql>
  18. mysql> status
  19. --------------
  20. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)

  22. Connection id: 2
  23. Current database: hoegh
  24. Current user: root@localhost
  25. SSL: Not in use
  26. Using delimiter: ;
  27. Server version: 5.7.16 MySQL Community Server (GPL)
  28. Protocol version: 10
  29. Connection: localhost via TCP/IP
  30. Server characterset: latin1
  31. Db characterset: utf8
  32. Client characterset: utf8
  33. Conn. characterset: utf8
  34. TCP port: 3306
  35. Uptime: 16 min 25 sec

  37. Threads: 1 Questions: 18 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.018
  38. --------------

  40. 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)


  6. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  7. Query OK, 1 row affected (0.00 sec)

  9. mysql>
  10. mysql> insert into hoegh values(1,'霍格');--插入失败
  11. ERROR 1366 (HY000):
  12. mysql>
  13. mysql> show create table hoegh;
  14. +-------+------------------------------------------------------------------------------------------------------------------------------
  15. | Table | Create Table
  16. +-------+------------------------------------------------------------------------------------------------------------------------------
  17. | hoegh | CREATE TABLE `hoegh` (
  18.   `id` int(11) DEFAULT NULL,
  19.   `name` varchar(20) DEFAULT NULL
  20. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  21. +-------+------------------------------------------------------------------------------------------------------------------------------
  22. 1 row in set (0.02 sec)

  24. 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)

  5. mysql>
  6. mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8;
  7. Query OK, 0 rows affected (0.17 sec)

  9. mysql> show create table hoegh;
  10. +-------+----------------------------------------------------------------------------------------------------------------------------+
  11. | Table | Create Table |
  12. +-------+----------------------------------------------------------------------------------------------------------------------------+
  13. | hoegh | CREATE TABLE `hoegh` (
  14.   `id` int(11) DEFAULT NULL,
  15.   `name` varchar(20) DEFAULT NULL
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  17. +-------+----------------------------------------------------------------------------------------------------------------------------+
  18. 1 row in set (0.00 sec)

  20. mysql>
  21. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  22. Query OK, 1 row affected (0.00 sec)

  24. mysql> insert into hoegh values(1,'霍格');--插入成功
  25. Query OK, 1 row affected (0.00 sec)

  27. mysql> select * from hoegh;
  28. +------+--------+
  29. | id | name |
  30. +------+--------+
  31. | 1 | HOEGH |
  32. | 1 | 霍格 |
  33. +------+--------+
  34. 2 rows in set (0.00 sec)

  36. 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)

  5. mysql>
  6. mysql> create database hoegh CHARACTER SET 'utf8';--重建测试库hoehg并设置CHARACTER SET参数
  7. Query OK, 1 row affected (0.00 sec)

  9. mysql> show create database hoegh;
  10. +----------+----------------------------------------------------------------+
  11. | Database | Create Database |
  12. +----------+----------------------------------------------------------------+
  13. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  14. +----------+----------------------------------------------------------------+
  15. 1 row in set (0.00 sec)

  17. mysql>
  18. mysql> use hoegh
  19. Database changed
  20. mysql> create table hoegh(id int,name varchar(20)); 
  21. Query OK, 0 rows affected (0.19 sec)


  24. mysql> show create table hoegh;
  25. +-------+----------------------------------------------------------------------------------------------------------------------------+
  26. | Table | Create Table |
  27. +-------+----------------------------------------------------------------------------------------------------------------------------+
  28. | hoegh | CREATE TABLE `hoegh` (
  29.   `id` int(11) DEFAULT NULL,
  30.   `name` varchar(20) DEFAULT NULL
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  32. +-------+----------------------------------------------------------------------------------------------------------------------------+
  33. 1 row in set (0.00 sec)

  35. mysql>
  36. mysql> insert into hoegh values(1,'HOEGH');--插入成功
  37. Query OK, 1 row affected (0.02 sec)

  39. mysql> insert into hoegh values(1,'霍格');--插入成功
  40. Query OK, 1 row affected (0.00 sec)

  42. mysql>
  43. mysql> select * from hoegh;
  44. +------+--------+
  45. | id | name |
  46. +------+--------+
  47. | 1 | HOEGH |
  48. | 1 | 霍格 |
  49. +------+--------+
  50. 2 rows in set (0.00 sec)

  52. 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)

  16. 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 服务已成功停止。


  6. E:\mysql-5.7.16-winx64\bin>
  7. E:\mysql-5.7.16-winx64\bin>net start mysql
  8. MySQL 服务正在启动 .
  9. MySQL 服务已经启动成功。


  12. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
  13. mysql: [Warning] Using a password on the command line interface can be insecure.
  14. Welcome to the MySQL monitor. Commands end with ; or \g.
  15. Your MySQL connection id is 2
  16. Server version: 5.7.16 MySQL Community Server (GPL)

  18. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  20. Oracle is a registered trademark of Oracle Corporation and/or its
  21. affiliates. Other names may be trademarks of their respective
  22. owners.

  24. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  26. mysql> show variables like '%char%';
  27. +--------------------------+-------------------------------------------+
  28. | Variable_name | Value |
  29. +--------------------------+-------------------------------------------+
  30. | character_set_client | utf8 |
  31. | character_set_connection | utf8 |
  32. | character_set_database | utf8 |
  33. | character_set_filesystem | binary |
  34. | character_set_results | utf8 |
  35. | character_set_server | utf8 |
  36. | character_set_system | utf8 |
  37. | character_sets_dir | E:\mysql-5.7.16-winx6in\share\charsets\ |
  38. +--------------------------+-------------------------------------------+
  39. 8 rows in set, 1 warning (0.00 sec)

  41. mysql>

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

点击(此处)折叠或打开

  1. mysql>
  2. mysql> drop database hoegh;
  3. Query OK, 1 row affected (0.16 sec)

  5. mysql> create database hoegh;--重建测试数据库hoegh
  6. Query OK, 1 row affected (0.00 sec)

  8. mysql>
  9. mysql> show create database hoegh;
  10. +----------+----------------------------------------------------------------+
  11. | Database | Create Database |
  12. +----------+----------------------------------------------------------------+
  13. | hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  14. +----------+----------------------------------------------------------------+
  15. 1 row in set (0.00 sec)

  17. mysql>
  18. mysql> use hoegh;
  19. Database changed
  20. mysql> create table hoegh(id int,name varchar(20));
  21. Query OK, 0 rows affected (0.17 sec)

  23. mysql>
  24. mysql> show create table hoegh;
  25. +-------+----------------------------------------------------------------------------------------------------------------------------+
  26. | Table | Create Table |
  27. +-------+----------------------------------------------------------------------------------------------------------------------------+
  28. | hoegh | CREATE TABLE `hoegh` (
  29.   `id` int(11) DEFAULT NULL,
  30.   `name` varchar(20) DEFAULT NULL
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  32. +-------+----------------------------------------------------------------------------------------------------------------------------+
  33. 1 row in set (0.00 sec)

  35. mysql>
  36. mysql> insert into hoegh values(1,'HOEGH');
  37. Query OK, 1 row affected (0.00 sec)

  39. mysql> insert into hoegh values(1,'霍格');
  40. Query OK, 1 row affected (0.00 sec)

  42. mysql> select * from hoegh;
  43. +------+--------+
  44. | id | name |
  45. +------+--------+
  46. | 1 | HOEGH |
  47. | 1 | 霍格 |
  48. +------+--------+
  49. 2 rows in set (0.00 sec)

  51. mysql>
我们看到一切正常，搞定！

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






上一篇：《Unity着色器和屏幕特效开发秘笈》—— 3.7　创建各向异性高光类型


下一篇：微软在新加坡开设全新的透明中心及网络安全中心