相信大家通過前幾篇文章，已經瞭解了 MySQL 字符集使用相關注意事項。那麼數據亂碼問題在這兒顯得就非常簡單了，或許説可能不會出現這樣的問題。
數據之所以會亂碼，在 MySQL 裏無非有以下幾類情況：
一、轉碼失敗
在數據寫入到表的過程中轉碼失敗，數據庫端也沒有進行恰當的處理，導致存放在表裏的數據亂碼。
針對這種情況，前幾篇文章介紹過客户端發送請求到服務端。
其中任意一個編碼不一致，都會導致表裏的數據存入不正確的編碼而產生亂碼。
比如下面簡單一條語句：
set @a = "文本字符串";
insert into t1 values(@a);1.變量 @a 的字符編碼是由參數 CHARACTER_SET_CLIENT 決定的，假設此時編碼為 A，也就是變量 @a 的編碼。
2.寫入語句在發送到 MySQL 服務端之前的編碼由 CHARACTER_SET_CONNECTION 決定，假設此時編碼為 B。
3.經過 MySQL 一系列詞法，語法解析等處理後，寫入到表 t1，表 t1 的編碼為 C。
那這裏編碼 A、編碼 B、編碼 C 如果不兼容，寫入的數據就直接亂碼。
來看下數據寫入過程亂碼情況：
-- 我的終端字符集是 utf8
root@ytt-pc:/home/ytt# locale
LANG=zh_CN.UTF-8
LANGUAGE=zh_CN:zh
LC_CTYPE="zh_CN.UTF-8"
...
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=
-- 新建立一個連接，客户端這邊字符集為 gb2312
root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gb2312
...
mysql> create database ytt_new10;
Query OK, 1 row affected (0.02 sec)
mysql> use ytt_new10;
Database changed
-- 表的字符集為 utf8
mysql> create table t1(a1 varchar(100)) charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
-- 插入一條數據，有兩條警告信息
mysql> insert into t1 values ("病毒滾吧！");
Query OK, 1 row affected, 2 warnings (0.01 sec)
-- 兩條警告的內容， 對於字段 a1，內容不正確，但是依然寫入了。
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1300
Message: Invalid gb2312 character string: 'E79785'
*************************** 2. row ***************************
Level: Warning
Code: 1366
Message: Incorrect string value: '\xE7\x97\x85\xE6\xAF\x92...' for column 'a1' at row 1
2 rows in set (0.00 sec)
-- 那檢索出來看到，數據已經不可逆的亂碼了。
mysql> select * from t1;
+-----------+
| a1 |
+-----------+
| ???▒??▒ |
+-----------+
1 row in set (0.00 sec)那如何防止這種情形出現呢？方法有兩種：
1、把客户端編碼設置成和表編碼一致或者兼容的編碼
mysql> truncate t1;
Query OK, 0 rows affected (0.06 sec)
-- 把客户端字符集設置為 utf8mb4
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 數據正常寫入
mysql> insert into t1 values ("病毒滾吧！");
Query OK, 1 row affected (0.01 sec)
-- 數據正常檢索
mysql> select * from t1;
+-----------------+
| a1 |
+-----------------+
| 病毒滾吧！ |
+-----------------+
1 row in set (0.00 sec)2、設置合適的 SQL_MODE 強制避免不兼容的編碼插入數據。
-- 設置 SQL_MODE 為嚴格事務表模式
mysql> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 報錯信息由 warnings 變為 error 拒絕插入
mysql> insert into t1(a1) values ("病毒滾吧！");
ERROR 1366 (HY000): Incorrect string value: '\xE7\x97\x85\xE6\xAF\x92...' for column 'a1' at row 1二、客户端亂碼
表數據正常，但是客户端展示後出現亂碼。
這一類場景，指的是從 MySQL 表裏拿數據出來返回到客户端，MySQL 裏的數據本身沒有問題。客户端發送請求到 MySQL，表的編碼為 D，從 MySQL 拿到記錄結果傳輸到客户端，此時記錄編碼為 E（CHARACTER_SET_RESULTS）。
那以上編碼 E 和 D 如果不兼容，檢索出來的數據就看起來亂碼了。但是由於數據本身沒有被破壞，所以換個兼容的編碼就可以獲取正確的結果。
這一類又分為以下三個不同的小類：
1、字段編碼和表一致，客户端是不同的編碼
比如下面例子， 表數據的編碼是 utf8mb4，而 SESSION 1 發起的連接編碼為 gbk。那由於編碼不兼容，檢索出來的數據肯定為亂碼：
-- SESSION 1
root@ytt-pc:/home/ytt# mysql -S /tmp/mysqld_3305.sock --default-character-set=gbk;
...
mysql> use ytt_new10;
Database changed
mysql> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`a1` varchar(10) DEFAULT NULL,
`a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from t3;
+--------------+--------------+
| a1 | a2 |
+--------------+--------------+
| ▒▒▒▒▒▒▒▒ | ▒▒▒▒▒▒▒▒ |
| ▒▒▒▒▒▒▒▒ | ▒▒▒▒▒▒▒˹▒▒▒ |
| ▒▒▒▒▒▒▒߹▒▒▒ | ▒▒▒▒▒▒▒˹▒▒▒ |
+--------------+--------------+
3 rows in set (0.00 sec)接下來把 SESSION 1 的編碼重置為默認 utf8mb4，那查出來的數據一定就是對的。
mysql> set names default;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t3;
+--------------------+--------------------+
| a1 | a2 |
+--------------------+--------------------+
| 病毒快走 | 病毒走了 |
| 病毒快走 | 病毒走了哈哈 |
| 病毒快走哈哈 | 病毒走了哈哈 |
+--------------------+--------------------+
3 rows in set (0.00 sec)2、表編碼和客户端的編碼一致，但是記錄之間編碼存在不一致的情形
比如表編碼是 utf8mb4，應用端編碼也是 utf8mb4，但是表裏的數據可能一半編碼是 utf8mb4，另外一半是 gbk。那麼此時表的數據也是正常的，不過此時採用哪種編碼都讀不到所有完整的數據。這樣數據產生的原因很多，比如其中一種可能性就是表編碼多次變更而且每次變更不徹底導致（變更不徹底，我之前的篇章裏有介紹）。舉個例子，表 t3 的編碼之前是 utf8mb4，現在是 gbk，而且兩次編碼期間都被寫入了正常的數據。下面兩次 select 查詢的結果只有一半是正確的：
-- 前三條數據編碼為 utf8mb4.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+-----------+-----------+
| a1 | a2 |
+-----------+-----------+
| 編碼1 | 編碼1 |
| 編碼1 | 編碼2 |
| 編碼1 | 編碼3 |
| 緙栫爜 | 緙栫爜 |
| 緙栫爜 | 緙栫爜 |
| 緙栫爜 | 緙栫爜 |
+-----------+-----------+
6 rows in set (0.00 sec)
-- 後三條數據編碼為 gbk.
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
+--------+--------+
| a1 | a2 |
+--------+--------+
| ▒▒▒▒1 | ▒▒▒▒1 |
| ▒▒▒▒1 | ▒▒▒▒2 |
| ▒▒▒▒1 | ▒▒▒▒3 |
| 編碼 | 編碼 |
| 編碼 | 編碼 |
| 編碼 | 編碼 |
+--------+--------+
6 rows in set (0.01 sec)那這樣的問題該如何解決呢？
前提是找到兩種不同編碼記錄的分界點！
比如表 t3 的記錄前三條編碼和後三條的編碼不一致，那可以把兩種數據分別導出，再導入到一張改好的表 t4 裏。
-- utf8mb4 的編碼數據，前三條導出
mysql> set names default;select * from t3 limit 0,3 into outfile '/var/lib/mysql-files/tx.txt';
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
-- GBK 編碼的數據，後三條導出
mysql> set names gbk;select * from t3 limit 3,3 into outfile '/var/lib/mysql-files/ty.txt';
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
-- 建立一張新表 t4，編碼改為統一的 utf8mb4
mysql> create table t4 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;
Query OK, 0 rows affected (0.04 sec)
-- 分別導入兩部分數據
mysql> load data infile '/var/lib/mysql-files/tx.txt' into table t4 character set gbk;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/var/lib/mysql-files/ty.txt' into table t4 ;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
-- 接下來看結果，一切正常
mysql> set names default;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t4;
+---------+---------+
| a1 | a2 |
+---------+---------+
| 編碼 | 編碼 |
| 編碼 | 編碼 |
| 編碼 | 編碼 |
| 編碼1 | 編碼1 |
| 編碼1 | 編碼2 |
| 編碼1 | 編碼3 |
+---------+---------+
6 rows in set (0.00 sec)
-- 完了把原來的表刪掉，新表 t4 改名即可。
mysql> drop table t3;
Query OK, 0 rows affected (0.04 sec)
mysql> alter table t4 rename to t3;
Query OK, 0 rows affected (0.04 sec)
-- 再次查看記錄，一切正常
mysql> select * from t3;
+---------+---------+
| a1 | a2 |
+---------+---------+
| 編碼1 | 編碼1 |
| 編碼1 | 編碼2 |
| 編碼1 | 編碼3 |
| 編碼 | 編碼 |
| 編碼 | 編碼 |
| 編碼 | 編碼 |
+---------+---------+
6 rows in set (0.00 sec)3、每個字段的編碼不一致，導致亂碼
和第二點一樣的場景。不同的是：非記錄間的編碼不統一，而是每個字段編碼不統一。舉個例子，表 c1 字段 a1,a2。a1 編碼 gbk，a2 編碼是 utf8mb4。那每個字段單獨讀出來數據是完整的，但是所有字段一起讀出來，數據總會有一部分亂碼。具體看下面的示例：
-- 字段 a1 編碼 GBK，讀出來正常，字段 a2 不正常。
mysql >set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql >select * from c1;
+--------------+----------------+
| a1 | a2 |
+--------------+----------------+
| 我在中國 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在日本 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在韓國 | ▒▒▒▒▒й▒▒▒ã▒ |
| 你在美國 | ▒▒▒▒▒й▒▒▒ã▒ |
| 中國太好 | ▒▒▒▒▒й▒▒▒ã▒ |
| 中國太棒 | ▒▒▒▒▒й▒▒▒ã▒ |
+--------------+----------------+
6 rows in set (0.00 sec)
-- 以編碼 utf8mb4 來獲取字段 a1 的值，顯示不正常，字段 a2 讀出來正常。
mysql >set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql >select * from c1;
+--------------------+-----------------------+
| a1 | a2 |
+--------------------+-----------------------+
| 鎴戝湪涓?浗 | 還是中國最好！ |
| 浣犲湪鏃ユ湰 | 還是中國最好！ |
| 浣犲湪闊╁浗 | 還是中國最好！ |
| 浣犲湪緹庡浗 | 還是中國最好！ |
| 涓?浗澶?ソ | 還是中國最好！ |
| 涓?浗澶?? | 還是中國最好！ |
+--------------------+-----------------------+
6 rows in set (0.00 sec)以上結果怎麼能一種編碼的方式正常顯示呢？也是類似第二種解決方式，把數據導出來，再導進去。由於 MySQL 處理數據是按照行的方式，按照列的方式會麻煩一點，我這裏用 OS 層來合併導出的文件，再導入到 MySQL 表裏。
-- 分別按列導出兩個文件
mysql >select a2 from c1 into outfile '/var/lib/mysql-files/c1_a2.txt';
Query OK, 6 rows affected (0.01 sec)
mysql >select a1 from c1 into outfile '/var/lib/mysql-files/c1_a1.txt';
Query OK, 6 rows affected (0.00 sec)
-- OS 層用paste命令合併這兩個文件
[root@ytt-pc mysql-files]# paste c1_a1.txt c1_a2.txt > c1.txt
-- 創建表c2，編碼統一。
mysql >create table c2 (a1 varchar(10),a2 varchar(10)) charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)
-- 導入合成後的文件到表c2
mysql >load data infile '/var/lib/mysql-files/c1.txt' into table c2 ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
-- 刪除表c1，重命名錶c2為c1。
mysql >drop table c1;
Query OK, 0 rows affected (0.02 sec)
mysql >alter table c2 rename to c1;
Query OK, 0 rows affected (0.02 sec)
-- 顯示結果正常，問題得到解決。
mysql >select * from c1;
+--------------+-----------------------+
| a1 | a2 |
+--------------+-----------------------+
| 我在中國 | 還是中國最好！ |
| 你在日本 | 還是中國最好！ |
| 你在韓國 | 還是中國最好！ |
| 你在美國 | 還是中國最好！ |
| 中國太好 | 還是中國最好！ |
| 中國太棒 | 還是中國最好！ |
+--------------+-----------------------+
6 rows in set (0.00 sec)三、LATIN1
還有一種情形就是以 LATIN1 的編碼存儲數據
估計大家都知道字符集 LATIN1，LATIN1 對所有字符都是單字節流處理，遇到不能處理的字節流，保持原樣，那麼在以上兩種存入和檢索的過程中都能保證數據一致，所以 MySQL 長期以來默認的編碼都是 LATIN1。這種情形，看起來也沒啥不對的點，數據也沒亂碼，那為什麼還有選用其他的編碼呢？原因就是對字符存儲的字節數不一樣，比如 emoji 字符 "❤"，如果用 utf8mb4 存儲，佔用 3 個字節，那 varchar(12) 就能存放 12 個字符，但是換成 LATIN1，只能存 4 個字符。來看下這個例子就明白了。
-- 更改數據庫 ytt_new10 字符集為 LATIN1
mysql> alter database ytt_new10 charset latin1;
Query OK, 1 row affected (0.02 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> use ytt_new10;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
-- 創建表 t2，默認字符集為 LATIN1
mysql> create table t2(a1 varchar(12));
Query OK, 0 rows affected (0.05 sec)
-- 插入emoji字符，只能插入4個字符
mysql> insert into t2 values ('❤❤❤❤');
Query OK, 1 row affected (0.02 sec)
-- 檢索出來結果完全正確
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)
-- 但是在加一個字符，插入第五個字符報錯。
mysql> insert into t2 values ('❤❤❤❤❤');
ERROR 1406 (22001): Data too long for column 'a1' at row 1
-- 換張表t3,字符集為utf8mb4.
mysql> create table t3 (a1 varchar(12)) charset utf8mb4;
Query OK, 0 rows affected (0.06 sec)
-- 結果集的字符集也設置為utf8mb4.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 插入12個'❤'，也就是同樣的表結構，存儲的字符串比latin1多。
mysql> insert into t3 values (rpad('❤',12,'❤'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+--------------------------------------+
| a1 |
+--------------------------------------+
| ❤❤❤❤❤❤❤❤❤❤❤❤ |
+--------------------------------------+
1 row in set (0.00 sec)其實 MySQL 一直到發佈了 8.0 才把默認字符集改為 utf8mb4。比如現在依然是表 t2，如果想把編碼改為 utf8mb4。那之前的數據必然沒法正常顯式：
-- 改為 utf8mb4
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 數據顯式亂碼
mysql> select * from t2;
+--------------------------+
| a1 |
+--------------------------+
| ���� |
+--------------------------+
1 row in set (0.00 sec)怎麼解決這個問題。有兩種方法：
1、把表 t2 的列 a1 先改為二進制類型，在改回來用 utf8mb4 的編碼的字符類型。
-- 現改為 binary 類型
mysql> alter table t2 modify a1 binary(12);
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----------------------------+
| a1 |
+----------------------------+
| 0xE29DA4E29DA4E29DA4E29DA4 |
+----------------------------+
1 row in set (0.00 sec)
-- 再改為varchar(12) utf8mb4.
mysql> alter table t2 modify a1 varchar(12) charset utf8mb4;
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0
-- 數據就正常顯式。
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)
-- 接下來，再把表的字符集改回UTF8MB4。
mysql> alter table t2 charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 02、還是用最土的方法，把數據導出來，把表編碼修改好，再把數據導入到表裏。
-- 導出表t2數據。
mysql> select * from t2 into outfile '/var/lib/mysql-files/t2.dat';
Query OK, 1 row affected (0.00 sec)
-- 刪除表
mysql> drop table t2;
Query OK, 0 rows affected (0.07 sec)
-- 重建表，編碼為utf8mb4.
mysql> create table t2(a1 varchar(12)) charset utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
-- 導入之前導出來的數據
mysql> load data infile '/var/lib/mysql-files/t2.dat' into table t2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
-- 檢索完全正常。
mysql> select * from t2;
+--------------+
| a1 |
+--------------+
| ❤❤❤❤ |
+--------------+
1 row in set (0.00 sec)總結
通過上面的詳細説明，相信對 MySQL 亂碼問題已經有一個很好的瞭解了。那來回顧下本篇的內容。本篇主要列列舉了 MySQL 亂碼可能出現的場景，並對應給出詳細的處理方法以及相關建議，希望以後大家永遠不會出現亂碼問題。
關於 MySQL 的技術內容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！
