簡(jiǎn)單明白徹底解決 MySQL 中文編碼問(wèn)題
1. 問(wèn)題重現(xiàn)
mysql> create database school;
mysql> use school;
mysql> create table student(name varchar(10));
mysql> insert into student values("Clarke");
mysql> select * from student;
+--------+
| name |
+--------+
| Clarke |
+--------+
mysql> insert into student values("唐三");
# 插入中文失敗
ERROR 1366 (HY000): Incorrect string value: '\xE5\x94\x90\xE4\xB8\x89' for column 'name' at row 1
2. 分析和解決
2.1 數(shù)據(jù)庫(kù)編碼設(shè)置
2.1.1 查看 MySQL 程序編碼設(shè)置
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
可以看到，設(shè)置里有很多的編碼設(shè)置是 latin1，這個(gè)編碼是無(wú)法正確顯示中文的，如果你的設(shè)置也是這樣，這就是導(dǎo)致中文編碼問(wèn)題的可能原因。
關(guān)于這些設(shè)置的含義：
| option | desc |
|---|---|
| character_set_client | 客戶(hù)端使用的字符編碼，如果客戶(hù)端連接時(shí)沒(méi)有設(shè)置，或者服務(wù)端已配置為忽略客戶(hù)端的設(shè)置 |
| character_set_connection | 客戶(hù)端設(shè)置連接數(shù)據(jù)庫(kù)時(shí)的字符編碼，如果客戶(hù)端沒(méi)有指明，則連接數(shù)據(jù)庫(kù)使用該設(shè)置的編碼 |
| character_set_database | 當(dāng)前選中數(shù)據(jù)庫(kù)的默認(rèn)字符編碼，如果沒(méi)有選中數(shù)據(jù)庫(kù)(use <database>)，則和 character_set_server 的值一致 |
| character_set_filesystem | 文件系統(tǒng)的編碼格式，把操作系統(tǒng)上的文件名轉(zhuǎn)化成此字符集，即把 character_set_client轉(zhuǎn)換character_set_filesystem， 默認(rèn)binary是不做任何轉(zhuǎn)換的 |
| character_set_results | 數(shù)據(jù)庫(kù)給客戶(hù)端返回時(shí)使用的編碼格式，如果客戶(hù)端連接時(shí)沒(méi)有指明，則使用該編碼 |
| character_set_server | 數(shù)據(jù)庫(kù)服務(wù)器默認(rèn)編碼格式，創(chuàng)建數(shù)據(jù)庫(kù)時(shí)默認(rèn)使用 |
| character_set_system | 數(shù)據(jù)庫(kù)系統(tǒng)使用的編碼格式，這個(gè)值一直是utf8，不需要設(shè)置，它是為存儲(chǔ)系統(tǒng)元數(shù)據(jù)的編碼格式 |
| character_sets_dir | 這個(gè)變量是字符集安裝的目錄 |
更多說(shuō)明：MySQL doc - 5.1.7 Server System Variables
2.1.2 修改編碼設(shè)置
編碼設(shè)置中我們需要關(guān)注的是下面 5 個(gè)字符編碼設(shè)置：
# 服務(wù)端相關(guān)
character_set_server
character_set_database #當(dāng)前選中數(shù)據(jù)庫(kù)的編碼，這個(gè)設(shè)置不需要手動(dòng)修改?
# 客戶(hù)端相關(guān)
character_set_client
character_set_connection
character_set_results
修改編碼設(shè)置的方式有三種。
方式1: session 范圍修改
mysql> set character_set_server=utf8mb4
建議使用 utf8mb4 編碼而不是 utf8，因?yàn)?MySQL 的 utf8 編碼有點(diǎn)小問(wèn)題，可以自行百度 MySQL 中 utf8 和 utf8mb4 的區(qū)別
這種修改方式是 session 范圍的，也就是當(dāng)前的 MySQL 連接結(jié)束后，設(shè)置就失效了。
其他幾個(gè)編碼設(shè)置也一樣修改。
方式2: global 范圍修改
mysql> set global character_set_server=utf8mb4
global 范圍下的修改，重新連接依然有效，直到 MySQL 服務(wù)端重啟。
方式3: 修改配置文件
想要編碼設(shè)置在 MySQL 服務(wù)端重啟后依然生效，可以修改配置文件。
不同平臺(tái)的配置文件位置不一樣，可以通過(guò)下面命令查看：
? ~ mysql --verbose --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
更多說(shuō)明： MySQL 官方文檔：4.2.2.2 Using Option Files
除了~/.my.cnf文件是用戶(hù)級(jí)別的外，其他幾個(gè)位置都是系統(tǒng)級(jí)別的，如果該位置沒(méi)有my.cnf文件，就新建一個(gè)文本文件，命名為 my.cnf。
windows 系統(tǒng)下，這個(gè)文件叫 my.ini。
在 my.cnf文件中添加以下內(nèi)容：
[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
[client]
default_character-set=utf8mb4
更多信息參考： A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
配置項(xiàng)說(shuō)明：
character-set-server 設(shè)置影響 character_set_server 的值，character_set_database 的值在未選中當(dāng)前數(shù)據(jù)庫(kù)的情況下下，默認(rèn)跟隨 character_set_server 的值。
default-character-set 影響 character_set_client, character_set_connection, character_set_results 三者的值。
在 MySQL 連接終端中執(zhí)行
SET NAMES <charset>也是影響的 character_set_client, character_set_connection, character_set_results 三者的值
- collation_server 影響 orderby 的排序結(jié)果，建議設(shè)置 character-set-server 的同時(shí)也要設(shè)置
關(guān)于 character_set_server 和 collation_server： MySQL doc - sysvar_character_set_server
修改配置文件后重啟 MySQL，再查看下編碼設(shè)置，
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
可以看到已經(jīng)生效了，5 個(gè)相關(guān)設(shè)置已經(jīng)修改為 utf8mb4。
三種修改方式，優(yōu)先選擇修改配置文件，其次的選擇或者想要靈活設(shè)置的話(huà)，可以在終端會(huì)話(huà)中設(shè)置。
2.2 已創(chuàng)建的數(shù)據(jù)庫(kù)的編碼
如果上面的配置已經(jīng)修改完成，可能仍然有中文編碼問(wèn)題，因?yàn)閷?duì)于已經(jīng)創(chuàng)建完成的數(shù)據(jù)庫(kù)和表，它的編碼在創(chuàng)建時(shí)已經(jīng)確定了，前面的配置項(xiàng)（character_set_server）已經(jīng)不能影響了，需要逐個(gè)修改相應(yīng)的數(shù)據(jù)庫(kù)，表，列。
2.2.1 具體數(shù)據(jù)庫(kù)的編碼
查看完整的數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句：
mysql> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
可以看到數(shù)據(jù)庫(kù) school 的默認(rèn)編碼仍然是 latin1，改起：
mysql> alter database school character set 'utf8mb4';
mysql> show create database school;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
2.2.2 表的編碼
在前一節(jié)，數(shù)據(jù)庫(kù) school 的默認(rèn)編碼已經(jīng)修改為 utf8mb4，接下來(lái)看數(shù)據(jù)表的默認(rèn)編碼：
mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------+
看到數(shù)據(jù)表 student 的默認(rèn)編碼仍然是 latin1，再改起：
mysql> alter table student character set 'utf8mb4';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`name` varchar(10) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------------------+
2.2.3 列的編碼
在上一節(jié)可以看到，數(shù)據(jù)表 student 的默認(rèn)編碼已經(jīng)修改為 utf8mb4，但是列 name 的編碼還是 latin1，改起！
mysql> alter table `student` change `name` `name` text character set 'utf8mb4';
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table student;
+---------+--------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+--------------------------------------------------------------------------------+
到這里，對(duì)于已經(jīng)存在的數(shù)據(jù)庫(kù)，修改完成，再插入中文試試：
mysql> insert into student value("昊天");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+--------+
| name |
+--------+
| Clarke |
| 昊天 |
+--------+
2 rows in set (0.00 sec)
成功！到此，中文編碼的問(wèn)題就解決了。
2.3 防御性編碼
為了防止意外的情況發(fā)生，我們可以采取更健壯的防御性編碼的方式，哪怕數(shù)據(jù)庫(kù)服務(wù)端的編碼設(shè)置不正確，我們?nèi)匀豢梢圆迦胫形臄?shù)據(jù)。
具體就是在創(chuàng)建數(shù)據(jù)表的時(shí)候指定默認(rèn)編碼：
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
# 指定存儲(chǔ)引擎，編碼，排序規(guī)則
mysql> create table course(name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into course value('高數(shù)');
Query OK, 1 row affected (0.01 sec)
mysql> select * from course;
+--------+
| name |
+--------+
| 高數(shù) |
+--------+
1 row in set (0.00 sec)
或者直接在創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候就指定編碼：
mysql> create database school DEFAULT CHARACTER SET utf8mb4;
3. 總結(jié)
解決 MySQL 中文編碼問(wèn)題的步驟：
查看數(shù)據(jù)庫(kù)編碼設(shè)置
修改編碼設(shè)置，在終端中修改設(shè)置項(xiàng)，或者修改配置文件
my.cnf以永久生效對(duì)于之前創(chuàng)建的數(shù)據(jù)庫(kù)，修改數(shù)據(jù)庫(kù)，數(shù)據(jù)表，數(shù)據(jù)列的默認(rèn)編碼
最佳實(shí)踐：防御性編碼，在數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句中指定默認(rèn)編碼。
參考：
