最近在将資料從Oracle遷移到MySQL的過程中,遇到一些問題,其中就包括中文字元顯示亂碼。
資料導入成功之後,中文字段内容無法正常顯示。後來才發現用戶端的字元集設定是對的,伺服器端的字元集設定不支援中文字元。
後來經過測試發現,我們可以通過三種方法解決這個問題,按照從低到高的級别分别是:
表級
資料庫級
伺服器級
我是在Windows Server 2008 r2環境下進行測試,建立了測試資料庫hoegh。
點選(此處)折疊或打開
mysql>
mysql> create database hoegh;
Query OK, 1 row affected (0.00 sec)
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> use hoegh;
Database changed
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
我們看到hoegh資料庫的預設字元集是latin1。
首先我們建立測試表hoegh,分别向表中插入一條英文資訊和中文資訊。
mysql> create table hoegh(id int,name varchar(20));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into hoegh values(1,'HOEGH');--插入成功
mysql> insert into hoegh values(1,'霍格');--插入失敗
ERROR 1366 (HY000):
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)
我們發現包含中文的記錄插入失敗,通過show create table語句可以看到表的預設字元集為latin1,不支援中文。
接下來,我們重建測試表hoegh并設定DEFAULT CHARSET為utf8,然後向表中插入一條英文資訊和中文資訊。
mysql> drop table hoegh;
Query OK, 0 rows affected (0.13 sec)
mysql> create table hoegh(id int,name varchar(20)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> insert into hoegh values(1,'霍格');--插入成功
mysql> select * from hoegh;
+------+--------+
| id | name |
| 1 | HOEGH |
| 1 | 霍格 |
2 rows in set (0.00 sec)
我們看到插入過程沒有報錯,也能正常查詢。
我們重建測試庫hoegh,并設定CHARACTER SET參數。
然後,重建測試表hoegh并插入兩條記錄。
mysql> drop database hoegh;--删除測試庫
Query OK, 1 row affected (0.11 sec)
mysql> create database hoegh CHARACTER SET 'utf8';--重建測試庫hoehg并設定CHARACTER SET參數
+----------+----------------------------------------------------------------+
| hoegh | CREATE DATABASE `hoegh` /*!40100 DEFAULT CHARACTER SET utf8 */ |
mysql> use hoegh
mysql> create table hoegh(id int,name varchar(20));
Query OK, 0 rows affected (0.19 sec)
Query OK, 1 row affected (0.02 sec)
此時,為了友善對比,我們檢視一下系統的字元集參數。
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)
我們看到character_set_server參數為latin1,這表明目前伺服器端的字元集為latin1。接下來,我們就通過修改參數檔案來修改這個參數。
通過直接配置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
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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.
| character_set_database | utf8 |
| character_set_server | utf8 |
我們看到character_set_server已經成功設定為utf8。然後我們重建測試資料庫hoegh,并重複之前的建表語句。
mysql> drop database hoegh;
Query OK, 1 row affected (0.16 sec)
mysql> create database hoegh;--重建測試資料庫hoegh
mysql> insert into hoegh values(1,'HOEGH');
mysql> insert into hoegh values(1,'霍格');
我們看到一切正常,搞定!
最後強烈建議大家使用utf8!号稱萬國碼!
<b><b></b></b>