Windows環境下MySQL5.7中文顯示亂碼的三種解決方案

Hoegh發表於2016-11-04
WindowsMySql
最近在將資料從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！號稱萬國碼！


~~~~~~~ the end~~~~~~~~~
hoegh
2016.11.04

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-2127822/，如需轉載，請註明出處，否則將追究法律責任。

相關文章