簡單明白徹底解決 MySQL 中文編碼問題

1. 問題重現(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ù)庫編碼設(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è)編碼是無法正確顯示中文的，如果你的設(shè)置也是這樣，這就是導(dǎo)致中文編碼問題的可能原因延欠。

關(guān)于這些設(shè)置的含義：

更多說明：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ù)庫的編碼埋凯，這個(gè)設(shè)置不需要手動(dòng)修改?

# 客戶端相關(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)小問題白对，可以自行百度 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)的配置文件位置不一樣塞关，可以通過下面命令查看：

?  ~ mysql --verbose --help | grep my.cnf

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

更多說明： MySQL 官方文檔：4.2.2.2 Using Option Files

除了~/.my.cnf文件是用戶級(jí)別的外，其他幾個(gè)位置都是系統(tǒng)級(jí)別的子巾，如果該位置沒有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)說明：

• character-set-server 設(shè)置影響 character_set_server 的值仪搔，character_set_database 的值在未選中當(dāng)前數(shù)據(jù)庫的情況下下瘾婿，默認(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ì)話中設(shè)置宝泵。

2.2 已創(chuàng)建的數(shù)據(jù)庫的編碼

如果上面的配置已經(jīng)修改完成好啰，可能仍然有中文編碼問題，因?yàn)閷?duì)于已經(jīng)創(chuàng)建完成的數(shù)據(jù)庫和表儿奶，它的編碼在創(chuàng)建時(shí)已經(jīng)確定了框往，前面的配置項(xiàng)（character_set_server）已經(jīng)不能影響了，需要逐個(gè)修改相應(yīng)的數(shù)據(jù)庫闯捎，表椰弊，列许溅。

2.2.1 具體數(shù)據(jù)庫的編碼

查看完整的數(shù)據(jù)庫創(chuàng)建語句：

mysql> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+

可以看到數(shù)據(jù)庫 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ù)庫 school 的默認(rèn)編碼已經(jīng)修改為 utf8mb4贤重，接下來看數(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ù)庫，修改完成粥惧，再插入中文試試：

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)

成功键畴！到此，中文編碼的問題就解決了突雪。

2.3 防御性編碼

為了防止意外的情況發(fā)生起惕，我們可以采取更健壯的防御性編碼的方式，哪怕數(shù)據(jù)庫服務(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ù)庫的時(shí)候就指定編碼：

mysql> create database school DEFAULT CHARACTER SET utf8mb4;

3. 總結(jié)

解決 MySQL 中文編碼問題的步驟：

1. 查看數(shù)據(jù)庫編碼設(shè)置

2. 修改編碼設(shè)置勺馆，在終端中修改設(shè)置項(xiàng)，或者修改配置文件 my.cnf 以永久生效

3. 對(duì)于之前創(chuàng)建的數(shù)據(jù)庫侨核，修改數(shù)據(jù)庫，數(shù)據(jù)表灌灾，數(shù)據(jù)列的默認(rèn)編碼

最佳實(shí)踐：防御性編碼搓译，在數(shù)據(jù)庫創(chuàng)建語句中指定默認(rèn)編碼。

參考：

MySQL 5.7 官方文檔