MySQL出現(xiàn)亂碼的原因

要了解為什么會(huì)出現(xiàn)亂碼，我們就先要理解：從客戶端發(fā)起請求，到MySQL存儲(chǔ)數(shù)據(jù)，再到下次從表取回客戶端的過程中，哪些環(huán)節(jié)會(huì)有編碼/解碼的行為。為了更好的解釋這個(gè)過程，博主制作了兩張流程圖，分別對應(yīng)存入和取出兩個(gè)階段。
存入MySQL經(jīng)歷的編碼轉(zhuǎn)換過程

上圖中有3次編碼/解碼的過程（紅色箭頭）。三個(gè)紅色箭頭分別對應(yīng)：客戶端編碼，MySQL Server解碼，Client編碼向表編碼的轉(zhuǎn)換。其中Terminal可以是一個(gè)Bash，一個(gè)web頁面又或者是一個(gè)APP。本文中我們假定Bash是我們的Terminal，即用戶端的輸入和展示界面。圖中每一個(gè)框格對應(yīng)的行為如下：

•     在terminal中使用輸入法輸入
•     terminal根據(jù)字符編碼轉(zhuǎn)換成二進(jìn)制流
•     二進(jìn)制流通過MySQL客戶端傳輸?shù)組ySQL Server
•     Server通過character-set-client解碼
•     判斷character-set-client和目標(biāo)表的charset是否一致
•     如果不一致則進(jìn)行一次從client-charset到table-charset的一次字符編碼轉(zhuǎn)換
•     將轉(zhuǎn)換后的字符編碼二進(jìn)制流存入文件中

從MySQL表中取出數(shù)據(jù)經(jīng)歷的編碼轉(zhuǎn)換過程

上圖有3次編碼/解碼的過程（紅色箭頭）。上圖中三個(gè)紅色箭頭分別對應(yīng)：客戶端解碼展示，MySQL Server根據(jù)character-set-client編碼，表編碼向character-set-client編碼的轉(zhuǎn)換。

•     從文件讀出二進(jìn)制數(shù)據(jù)流
•     用表字符集編碼進(jìn)行解碼
•     將數(shù)據(jù)轉(zhuǎn)換為character-set-client的編碼
•     使用character-set-client編碼為二進(jìn)制流
•     Server通過網(wǎng)絡(luò)傳輸?shù)竭h(yuǎn)端client
•     client通過bash配置的字符編碼展示查詢結(jié)果

造成MySQL亂碼的原因
1. 存入和取出時(shí)對應(yīng)環(huán)節(jié)的編碼不一致

這個(gè)會(huì)造成亂碼是顯而易見的。我們把存入階段的三次編解碼使用的字符集編號為C1,C2,C3（圖一從左到右）；取出時(shí)的三個(gè)字符集依次編號為C1',C2',C3'（從左到右）。那么存入的時(shí)候bash C1用的是UTF-8編碼，取出的時(shí)候,C1'我們卻使用了windows終端（默認(rèn)是GBK編碼），那么結(jié)果幾乎一定是亂碼。又或者存入MySQL的時(shí)候set names utf8(C2)，而取出的時(shí)候卻使用了set names gbk(C2')，那么結(jié)果也必然是亂碼
2. 單個(gè)流程中三步的編碼不一致

即上面任意一幅圖中的同方向的三步中，只要兩步或者兩部以上的編碼有不一致就有可能出現(xiàn)編解碼錯(cuò)誤。如果差異的兩個(gè)字符集之間無法進(jìn)行無損編碼轉(zhuǎn)換（下文會(huì)詳細(xì)介紹），那么就一定會(huì)出現(xiàn)亂碼。例如：我們的shell是UTF8編碼，MySQL的character-set-client配置成了GBK，而表結(jié)構(gòu)卻又是charset=utf8，那么毫無疑問的一定會(huì)出現(xiàn)亂碼。

這里我們就簡單演示下這種情況：

  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)

關(guān)于MySQL的編/解碼

既然系統(tǒng)之間是按照二進(jìn)制流進(jìn)行傳輸?shù)模侵苯影堰@串二進(jìn)制流直接存入表文件就好啦。為什么在存儲(chǔ)之前還要進(jìn)行兩次編解碼的操作呢？

•     Client to Server的編解碼的原因是MySQL需要對傳來的二進(jìn)制流做語法和詞法解析。如果不做編碼解析和校驗(yàn)，我們甚至沒法知道傳來的一串二進(jìn)制流是insert還是update。
•     File to Engine的編解碼是為知道二進(jìn)制流內(nèi)的分詞情況。舉個(gè)簡單的例子：我們想要從表里取出某個(gè)字段的前兩個(gè)字符，執(zhí)行了一句形如select left(col,2) from table的語句，存儲(chǔ)引擎從文件讀入該column的值是E4B8ADE69687。那么這個(gè)時(shí)候如果我們按照GBK把這個(gè)值分割成E4B8,ADE6,9687三個(gè)字，并那么返回客戶端的值就應(yīng)該是E4B8ADE6；如果按照UTF8分割成E4B8AD,E69687，那么就應(yīng)該返回E4B8ADE69687兩個(gè)字?？梢姡绻趶臄?shù)據(jù)文件讀入數(shù)據(jù)后，不進(jìn)行編解碼的話在存儲(chǔ)引擎內(nèi)部是無法進(jìn)行字符級別的操作的。
• 關(guān)于錯(cuò)進(jìn)錯(cuò)出

在MySQL中最常見的亂碼問題的起因就是把錯(cuò)進(jìn)錯(cuò)出神話。所謂的錯(cuò)進(jìn)錯(cuò)出就是，客戶端(web或shell)的字符編碼和最終表的字符編碼格式不同，但是只要保證存和取兩次的字符集編碼一致就仍然能夠獲得沒有亂碼的輸出的這種現(xiàn)象。但是，錯(cuò)進(jìn)錯(cuò)出并不是對于任意兩種字符集編碼的組合都是有效的。我們假設(shè)客戶端的編碼是C，MySQL表的字符集編碼是S。那么為了能夠錯(cuò)進(jìn)錯(cuò)出，需要滿足以下兩個(gè)條件：

1.     MySQL接收請求時(shí)，從C編碼后的二進(jìn)制流在被S解碼時(shí)能夠無損
2.     MySQL返回?cái)?shù)據(jù)是，從S編碼后的二進(jìn)制流在被C解碼時(shí)能夠無損

編碼無損轉(zhuǎn)換

那么什么是有損轉(zhuǎn)換，什么是無損轉(zhuǎn)換呢？假設(shè)我們要把用編碼A表示的字符X，轉(zhuǎn)化為編碼B的表示形式，而編碼B的字形集中并沒有X這個(gè)字符，那么此時(shí)我們就稱這個(gè)轉(zhuǎn)換是有損的。那么，為什么會(huì)出現(xiàn)兩個(gè)編碼所能表示字符集合的差異呢？如果大家看過博主之前的那篇 十分鐘搞清字符集和字符編碼，或者對字符編碼有基礎(chǔ)理解的話，就應(yīng)該知道每個(gè)字符集所支持的字符數(shù)量是有限的，并且各個(gè)字符集涵蓋的文字之間存在差異。UTF8和GBK所能表示的字符數(shù)量范圍如下：

•     GBK單個(gè)字符編碼后的取值范圍是：8140 - FEFE 其中不包括**7E，總共字符數(shù)在27000左右
•     UTF8單個(gè)字符編碼后，按照字節(jié)數(shù)的不同，取值范圍如下表：
  

由于UTF-8編碼能表示的字符數(shù)量遠(yuǎn)超GBK。那么我們很容易就能找到一個(gè)從UTF8到GBK的有損編碼轉(zhuǎn)換。我們用字符映射器（見下圖）找出了一個(gè)明顯就不在GBK編碼表中的字符，嘗試存入到GBK編碼的表中。并再次取出查看有損轉(zhuǎn)換的行為
字符信息具體是：? GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85

在MySQL中存儲(chǔ)的具體情況如下：

  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)

出錯(cuò)的部分是在編解碼的第3步時(shí)發(fā)生的。具體見下圖

可見MySQL內(nèi)部如果無法找到一個(gè)UTF8字符所對應(yīng)的GBK字符時(shí)，就會(huì)轉(zhuǎn)換成一個(gè)錯(cuò)誤mark（這里是問號）。而每個(gè)字符集在程序?qū)崿F(xiàn)的時(shí)候內(nèi)部都約定了當(dāng)出現(xiàn)這種情況時(shí)的行為和轉(zhuǎn)換規(guī)則。例如：UTF8中無法找到對應(yīng)字符時(shí)，如果不拋錯(cuò)那么就將該字符替換成? (U+FFFD)

那么是不是任何兩種字符集編碼之間的轉(zhuǎn)換都是有損的呢？并非這樣，轉(zhuǎn)換是否有損取決于以下幾點(diǎn)：

•     被轉(zhuǎn)換的字符是否同時(shí)在兩個(gè)字符集中
•     目標(biāo)字符集是否能夠?qū)Σ恢С肿址Ａ羝湓斜磉_(dá)形式

關(guān)于第一點(diǎn)，剛才已經(jīng)通過實(shí)驗(yàn)來解釋過了。這里來解釋下造成有損轉(zhuǎn)換的第二個(gè)因素。從剛才的例子我們可以看到由于GBK在處理自己無法表示的字符時(shí)的行為是：用錯(cuò)誤標(biāo)識替代，即0x3F。而有些字符集（例如latin1）在遇到自己無法表示的字符時(shí)，會(huì)保留原字符集的編碼數(shù)據(jù)，并跳過忽略該字符進(jìn)而處理后面的數(shù)據(jù)。如果目標(biāo)字符集具有這樣的特性，那么就能夠?qū)崿F(xiàn)這節(jié)最開始提到的錯(cuò)進(jìn)錯(cuò)出的效果。

我們來看下面這個(gè)例子：

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)

具體流程圖如下?？梢娫诒籑ySQL Server接收到以后實(shí)際上已經(jīng)發(fā)生了編碼不一致的情況。但是由于Latin1字符集對于自己表述范圍外的字符不會(huì)做任何處理，而是保留原值。這樣的行為也使得錯(cuò)進(jìn)錯(cuò)出成為了可能。

如何避免亂碼

理解了上面的內(nèi)容，要避免亂碼就顯得很容易了。只要做到“三位一體”，即客戶端，MySQL character-set-client，table charset三個(gè)字符集完全一致就可以保證一定不會(huì)有亂碼出現(xiàn)了。而對于已經(jīng)出現(xiàn)亂碼，或者已經(jīng)遭受有損轉(zhuǎn)碼的數(shù)據(jù)，如何修復(fù)相對來說就會(huì)有些困難。下一節(jié)我們詳細(xì)介紹具體方法。
如何修復(fù)已經(jīng)編碼損壞的數(shù)據(jù)

在介紹正確方法前，我們先科普一下那些網(wǎng)上流傳的所謂的“正確方法”可能會(huì)造成的嚴(yán)重后果。
錯(cuò)誤方法一

無論從語法還是字面意思來看：ALTER TABLE ... CHARSET=xxx 無疑是最像包治亂碼的良藥了！而事實(shí)上，他對于你已經(jīng)損壞的數(shù)據(jù)一點(diǎn)幫助也沒有，甚至連已經(jīng)該表已經(jīng)創(chuàng)建列的默認(rèn)字符集都無法改變。我們看下面這個(gè)例子

  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)

可見該語法緊緊修改了表的默認(rèn)字符集，即只對以后創(chuàng)建的列的默認(rèn)字符集產(chǎn)生影響，而對已經(jīng)存在的列和數(shù)據(jù)沒有變化。
錯(cuò)誤方法二

ALTER TABLE … CONVERT TO CHARACTER SET … 的相較于方法一來說殺傷力更大，因?yàn)閺?官方文檔的解釋他的作用就是用于對一個(gè)表的數(shù)據(jù)進(jìn)行編碼轉(zhuǎn)換。下面是文檔的一小段摘錄：

  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];

而實(shí)際上，這句語法只適用于當(dāng)前并沒有亂碼，并且不是通過錯(cuò)進(jìn)錯(cuò)出的方法保存的表。。而對于已經(jīng)因?yàn)殄e(cuò)進(jìn)錯(cuò)出而產(chǎn)生編碼錯(cuò)誤的表，則會(huì)帶來更糟的結(jié)果。

我們用一個(gè)實(shí)際例子來解釋下，這句SQL實(shí)際做了什么和他會(huì)造成的結(jié)果。假設(shè)我們有一張編碼是latin1的表，且之前通過錯(cuò)進(jìn)錯(cuò)出存入了UTF-8的數(shù)據(jù)，但是因?yàn)橥ㄟ^terminal仍然能夠正常顯示。即上文錯(cuò)進(jìn)錯(cuò)出章節(jié)中舉例的情況。一段時(shí)間使用后我們發(fā)現(xiàn)了這個(gè)錯(cuò)誤，并打算把表的字符集編碼改成UTF-8并且不影響原有數(shù)據(jù)的正常顯示。這種情況下使用alter table convert to character set會(huì)有這樣的后果：

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 | è????ˉ??-?–?        |          12 |
  +----+--------------------------------------------------------+-----------------------------+-----------------------+
  1 row in set (0.00 sec)

從這個(gè)例子我們可以看出，對于已經(jīng)錯(cuò)進(jìn)錯(cuò)出的數(shù)據(jù)表，這個(gè)命令不但沒有起到“撥亂反正”的效果，還會(huì)徹底將數(shù)據(jù)糟蹋，連數(shù)據(jù)的二進(jìn)制編碼都改變了。
正確的方法一 Dump & Reload

這個(gè)方法比較笨，但也比較好操作和理解。簡單的說分為以下三步：

1.     通過錯(cuò)進(jìn)錯(cuò)出的方法，導(dǎo)出到文件
2.     用正確的字符集修改新表
3.     將之前導(dǎo)出的文件導(dǎo)回到新表中

還是用上面那個(gè)例子舉例，我們用UTF-8將數(shù)據(jù)“錯(cuò)進(jìn)”到latin1編碼的表中?，F(xiàn)在需要將表編碼修改為UTF-8可以使用以下命令

  shell> mysqldump -u root -p -d --skip-set-charset --default-character-set=utf8 test charset_test_latin1 > data.sql
  #確保導(dǎo)出的文件用文本編輯器在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

這種方法比較取巧，用的是將二進(jìn)制數(shù)據(jù)作為中間數(shù)據(jù)的做法來實(shí)現(xiàn)的。由于，MySQL再將有編碼意義的數(shù)據(jù)流，轉(zhuǎn)換為無編碼意義的二進(jìn)制數(shù)據(jù)的時(shí)候并不做實(shí)際的數(shù)據(jù)轉(zhuǎn)換。而從二進(jìn)制數(shù)據(jù)準(zhǔn)換為帶編碼的數(shù)據(jù)時(shí)，又會(huì)用目標(biāo)編碼做一次編碼轉(zhuǎn)換校驗(yàn)。通過這兩個(gè)特性就相當(dāng)于在MySQL內(nèi)部模擬了一次“錯(cuò)出”，將亂碼“撥亂反正”了。

還是用上面那個(gè)例子舉例，我們用UTF-8將數(shù)據(jù)“錯(cuò)進(jìn)”到latin1編碼的表中?，F(xiàn)在需要將表編碼修改為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;

最新評論