共計 11123 個字符，預計需要花費 28 分鐘才能閱讀完成。
本篇內容主要講解“MySQL 出現亂碼的原因及解決方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“MySQL 出現亂碼的原因及解決方法”吧!
MySQL 出現亂碼的原因
要了解為什么會出現亂碼，我們就先要理解：從客戶端發起請求，到 MySQL 存儲數據，再到下次從表取回客戶端的過程中，哪些環節會有編碼 / 解碼的行為。為了更好的解釋這個過程，博主制作了兩張流程圖，分別對應存入和取出兩個階段。
存入 MySQL 經歷的編碼轉換過程
上圖中有 3 次編碼 / 解碼的過程(紅色箭頭)。三個紅色箭頭分別對應：客戶端編碼，MySQL Server 解碼，Client 編碼向表編碼的轉換。其中 Terminal 可以是一個 Bash，一個 web 頁面又或者是一個 APP。本文中我們假定 Bash 是我們的 Terminal，即用戶端的輸入和展示界面。圖中每一個框格對應的行為如下：
在 terminal 中使用輸入法輸入
terminal 根據字符編碼轉換成二進制流
二進制流通過 MySQL 客戶端傳輸到 MySQL Server
Server 通過 character-set-client 解碼
判斷 character-set-client 和目標表的 charset 是否一致
如果不一致則進行一次從 client-charset 到 table-charset 的一次字符編碼轉換
將轉換后的字符編碼二進制流存入文件中
從 MySQL 表中取出數據經歷的編碼轉換過程
上圖有 3 次編碼 / 解碼的過程(紅色箭頭)。上圖中三個紅色箭頭分別對應：客戶端解碼展示，MySQL Server 根據 character-set-client 編碼，表編碼向 character-set-client 編碼的轉換。
從文件讀出二進制數據流
用表字符集編碼進行解碼
將數據轉換為 character-set-client 的編碼
使用 character-set-client 編碼為二進制流
Server 通過網絡傳輸到遠端 client
client 通過 bash 配置的字符編碼展示查詢結果
造成 MySQL 亂碼的原因
1. 存入和取出時對應環節的編碼不一致
這個會造成亂碼是顯而易見的。我們把存入階段的三次編解碼使用的字符集編號為 C1,C2,C3(圖一從左到右); 取出時的三個字符集依次編號為 C1 rsquo;,C2 rsquo;,C3 rsquo;(從左到右)。那么存入的時候 bash C1 用的是 UTF- 8 編碼，取出的時候,C1 我們卻使用了 windows 終端(默認是 GBK 編碼)，那么結果幾乎一定是亂碼。又或者存入 MySQL 的時候 set names utf8(C2)，而取出的時候卻使用了 set names gbk(C2)，那么結果也必然是亂碼
2. 單個流程中三步的編碼不一致
即上面任意一幅圖中的同方向的三步中，只要兩步或者兩部以上的編碼有不一致就有可能出現編解碼錯誤。如果差異的兩個字符集之間無法進行無損編碼轉換(下文會詳細介紹)，那么就一定會出現亂碼。例如：我們的 shell 是 UTF8 編碼，MySQL 的 character-set-client 配置成了 GBK，而表結構卻又是 charset=utf8，那么毫無疑問的一定會出現亂碼。
這里我們就簡單演示下這種情況
master [localhost] {msandbox} (test) create table charset_test_utf8 (id int primary key auto_increment, char_col varchar(50)) charset = utf8; Query OK, 0 rows affected (0.04 sec) master [localhost] {msandbox} (test) set names gbk; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) insert into charset_test_utf8 (char_col) values (中文 Query OK, 1 row affected, 1 warning (0.01 sec) master [localhost] {msandbox} (test) show warnings; +---------+------+---------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: \xAD\xE6\x96\x87 for column char_col at row 1 | +---------+------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) select id,hex(char_col),char_col from charset_test_utf8; +----+----------------+----------+ | id | hex(char_col) | char_col | +----+----------------+----------+ | 1 | E6B6933FE69E83 | ? | +----+----------------+----------+ 1 row in set (0.01 sec)關于 MySQL 的編 / 解碼
既然系統之間是按照二進制流進行傳輸的，那直接把這串二進制流直接存入表文件就好啦。為什么在存儲之前還要進行兩次編解碼的操作呢?
Client to Server 的編解碼的原因是 MySQL 需要對傳來的二進制流做語法和詞法解析。如果不做編碼解析和校驗，我們甚至沒法知道傳來的一串二進制流是 insert 還是 update。
File to Engine 的編解碼是為知道二進制流內的分詞情況。舉個簡單的例子：我們想要從表里取出某個字段的前兩個字符，執行了一句形如 select left(col,2) from table 的語句，存儲引擎從文件讀入該 column 的值是 E4B8ADE69687。那么這個時候如果我們按照 GBK 把這個值分割成 E4B8,ADE6,9687 三個字，并那么返回客戶端的值就應該是 E4B8ADE6; 如果按照 UTF8 分割成 E4B8AD,E69687，那么就應該返回 E4B8ADE69687 兩個字。可見，如果在從數據文件讀入數據后，不進行編解碼的話在存儲引擎內部是無法進行字符級別的操作的。
關于錯進錯出
在 MySQL 中最常見的亂碼問題的起因就是把錯進錯出神話。所謂的錯進錯出就是，客戶端 (web 或 shell) 的字符編碼和最終表的字符編碼格式不同，但是只要保證存和取兩次的字符集編碼一致就仍然能夠獲得沒有亂碼的輸出的這種現象。但是，錯進錯出并不是對于任意兩種字符集編碼的組合都是有效的。我們假設客戶端的編碼是 C，MySQL 表的字符集編碼是 S。那么為了能夠錯進錯出，需要滿足以下兩個條件
MySQL 接收請求時，從 C 編碼后的二進制流在被 S 解碼時能夠無損
MySQL 返回數據是，從 S 編碼后的二進制流在被 C 解碼時能夠無損
編碼無損轉換
那么什么是有損轉換，什么是無損轉換呢? 假設我們要把用編碼 A 表示的字符 X，轉化為編碼 B 的表示形式，而編碼 B 的字形集中并沒有 X 這個字符，那么此時我們就稱這個轉換是有損的。那么，為什么會出現兩個編碼所能表示字符集合的差異呢? 如果大家看過博主之前的那篇 十分鐘搞清字符集和字符編碼，或者對字符編碼有基礎理解的話，就應該知道每個字符集所支持的字符數量是有限的，并且各個字符集涵蓋的文字之間存在差異。UTF8 和 GBK 所能表示的字符數量范圍如下
GBK 單個字符編碼后的取值范圍是：8140 – FEFE 其中不包括 **7E，總共字符數在 27000 左右
UTF8 單個字符編碼后，按照字節數的不同，取值范圍如下表：
由于 UTF- 8 編碼能表示的字符數量遠超 GBK。那么我們很容易就能找到一個從 UTF8 到 GBK 的有損編碼轉換。我們用字符映射器 (見下圖) 找出了一個明顯就不在 GBK 編碼表中的字符，嘗試存入到 GBK 編碼的表中。并再次取出查看有損轉換的行為
字符信息具體是：? GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85
在 MySQL 中存儲的具體情況如下：
master [localhost] {msandbox} (test) create table charset_test_gbk (id int primary key auto_increment, char_col varchar(50)) charset = gbk; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) set names utf8; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) insert into charset_test_gbk (char_col) values (? Query OK, 1 row affected, 1 warning (0.01 sec) master [localhost] {msandbox} (test) show warnings; +---------+------+-----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: \xE0\xA8\x85 for column char_col at row 1 | +---------+------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) select id,hex(char_col),char_col,char_length(char_col) from charset_test_gbk; +----+---------------+----------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+---------------+----------+-----------------------+ | 1 | 3F | ? | 1 | +----+---------------+----------+-----------------------+ 1 row in set (0.00 sec)出錯的部分是在編解碼的第 3 步時發生的。具體見下圖
可見 MySQL 內部如果無法找到一個 UTF8 字符所對應的 GBK 字符時，就會轉換成一個錯誤 mark(這里是問號)。而每個字符集在程序實現的時候內部都約定了當出現這種情況時的行為和轉換規則。例如：UTF8 中無法找到對應字符時，如果不拋錯那么就將該字符替換成 (U+FFFD)
那么是不是任何兩種字符集編碼之間的轉換都是有損的呢? 并非這樣，轉換是否有損取決于以下幾點：
被轉換的字符是否同時在兩個字符集中
目標字符集是否能夠對不支持字符，保留其原有表達形式
關于 *** 點，剛才已經通過實驗來解釋過了。這里來解釋下造成有損轉換的第二個因素。從剛才的例子我們可以看到由于 GBK 在處理自己無法表示的字符時的行為是：用錯誤標識替代，即 0x3F。而有些字符集 (例如 latin1) 在遇到自己無法表示的字符時，會保留原字符集的編碼數據，并跳過忽略該字符進而處理后面的數據。如果目標字符集具有這樣的特性，那么就能夠實現這節最開始提到的錯進錯出的效果。
我們來看下面這個例子
master [localhost] {msandbox} (test) create table charset_test (id int primary key auto_increment, char_col varchar(50)) charset = latin1; Query OK, 0 rows affected (0.03 sec) master [localhost] {msandbox} (test) set names latin1; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) insert into charset_test (char_col) values (中文 Query OK, 1 row affected (0.01 sec) master [localhost] {msandbox} (test) select id,hex(char_col),char_col from charset_test; +----+---------------+----------+ | id | hex(char_col) | char_col | +----+---------------+----------+ | 2 | E4B8ADE69687 | 中文 | +----+---------------+----------+ 2 rows in set (0.00 sec)具體流程圖如下。可見在被 MySQL Server 接收到以后實際上已經發生了編碼不一致的情況。但是由于 Latin1 字符集對于自己表述范圍外的字符不會做任何處理，而是保留原值。這樣的行為也使得錯進錯出成為了可能。
如何避免亂碼
理解了上面的內容，要避免亂碼就顯得很容易了。只要做到“三位一體”，即客戶端，MySQL character-set-client，table charset 三個字符集完全一致就可以保證一定不會有亂碼出現了。而對于已經出現亂碼，或者已經遭受有損轉碼的數據，如何修復相對來說就會有些困難。下一節我們詳細介紹具體方法。
如何修復已經編碼損壞的數據
在介紹正確方法前，我們先科普一下那些網上流傳的所謂的“正確方法”可能會造成的嚴重后果。
錯誤方法一
無論從語法還是字面意思來看：ALTER TABLE … CHARSET=xxx 無疑是最像包治亂碼的良藥了! 而事實上，他對于你已經損壞的數據一點幫助也沒有，甚至連已經該表已經創建列的默認字符集都無法改變。我們看下面這個例子
master [localhost] {msandbox} (test) show create table charset_test; +--------------+--------------------------------+ | Table | Create Table | +--------------+--------------------------------+ | charset_test | CREATE TABLE `charset_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `char_col` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 | +--------------+--------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) alter table charset_test charset=gbk; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) show create table charset_test; +--------------+--------------------------------+ | Table | Create Table | +--------------+--------------------------------+ | charset_test | CREATE TABLE `charset_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `char_col` varchar(50) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk | +--------------+--------------------------------+ 1 row in set (0.00 sec)可見該語法緊緊修改了表的默認字符集，即只對以后創建的列的默認字符集產生影響，而對已經存在的列和數據沒有變化。
錯誤方法二
ALTER TABLE hellip; CONVERT TO CHARACTER SET hellip; 的相較于方法一來說殺傷力更大，因為從 官方文檔的解釋 他的作用就是用于對一個表的數據進行編碼轉換。下面是文檔的一小段摘錄：
To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
而實際上，這句語法只適用于當前并沒有亂碼，并且不是通過錯進錯出的方法保存的表。。而對于已經因為錯進錯出而產生編碼錯誤的表，則會帶來更糟的結果。我們用一個實際例子來解釋下，這句 SQL 實際做了什么和他會造成的結果。假設我們有一張編碼是 latin1 的表，且之前通過錯進錯出存入了 UTF- 8 的數據，但是因為通過 terminal 仍然能夠正常顯示。即上文錯進錯出章節中舉例的情況。一段時間使用后我們發現了這個錯誤，并打算把表的字符集編碼改成 UTF- 8 并且不影響原有數據的正常顯示。這種情況下使用 alter table convert to character set 會有這樣的后果：
master [localhost] {msandbox} (test) create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = latin1; Query OK, 0 rows affected (0.01 sec) master [localhost] {msandbox} (test) set names latin1; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) insert into charset_test_latin1 (char_col) values (這是中文 Query OK, 1 row affected (0.01 sec) master [localhost] {msandbox} (test) select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1; +----+--------------------------+--------------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+--------------------------+--------------+-----------------------+ | 1 | E8BF99E698AFE4B8ADE69687 | 這是中文 | 12 | +----+--------------------------+--------------+-----------------------+ 1 row in set (0.01 sec) master [localhost] {msandbox} (test) alter table charset_test_latin1 convert to character set utf8; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) set names utf8; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) select id,hex(char_col),char_col,char_length(char_col) from charset_test_latin1; +----+--------------------------------------------------------+-----------------------------+-----------------------+ | id | hex(char_col) | char_col | char_length(char_col) | +----+--------------------------------------------------------+-----------------------------+-----------------------+ | 1 | C3A8C2BFE284A2C3A6CB9CC2AFC3A4C2B8C2ADC3A6E28093E280A1 | egrave; iquest; trade; aelig; tilde; macr; auml; cedil; shy; aelig; ndash; Dagger; | 12 | +----+--------------------------------------------------------+-----------------------------+-----------------------+ 1 row in set (0.00 sec)從這個例子我們可以看出，對于已經錯進錯出的數據表，這個命令不但沒有起到“撥亂反正”的效果，還會徹底將數據糟蹋，連數據的二進制編碼都改變了。
正確的方法一 Dump Reload
這個方法比較笨，但也比較好操作和理解。簡單的說分為以下三步：
通過錯進錯出的方法，導出到文件
用正確的字符集修改新表
將之前導出的文件導回到新表中
還是用上面那個例子舉例，我們用 UTF- 8 將數據“錯進”到 latin1 編碼的表中。現在需要將表編碼修改為 UTF- 8 可以使用以下命令
shell mysqldump -u root -p -d --skip-set-charset --default-character-set=utf8 test charset_test_latin1 data.sql # 確保導出的文件用文本編輯器在 UTF- 8 編碼下查看沒有亂碼 shell mysql -uroot -p -e create table charset_test_latin1 (id int primary key auto_increment, char_col varchar(50)) charset = utf8 test shell mysql -uroot -p --default-character-set=utf8 test data.sql正確的方法二 Convert to Binary Convert Back
這種方法比較取巧，用的是將二進制數據作為中間數據的做法來實現的。由于，MySQL 再將有編碼意義的數據流，轉換為無編碼意義的二進制數據的時候并不做實際的數據轉換。而從二進制數據準換為帶編碼的數據時，又會用目標編碼做一次編碼轉換校驗。通過這兩個特性就相當于在 MySQL 內部模擬了一次“錯出”，將亂碼“撥亂反正”了。
還是用上面那個例子舉例，我們用 UTF- 8 將數據“錯進”到 latin1 編碼的表中。現在需要將表編碼修改為 UTF- 8 可以使用以下命令
mysql ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col VARBINARY(50); mysql ALTER TABLE charset_test_latin1 MODIFY COLUMN char_col varchar(50) character set utf8;到此，相信大家對“MySQL 出現亂碼的原因及解決方法”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
