專治 MySQL 亂碼， 再也不想看到?了 ??！

MySQL出現(xiàn)亂碼的原因有很多，一般與character_set參數(shù)有關(guān)。我們先來看看有哪些參數(shù)：

SHOW?VARIABLES?LIKE?"character%";
Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/Cellar/[email protected]/5.7.24/share/mysql/charsets/

其中，最主要的是character_set_client和character_set_results。這兩個參數(shù)分別有什么用呢？

在客戶端將一條命令輸入MySQL時，MySQL只知道這條命令是0101的字節(jié)流，并不知道具體采用的是什么編碼。第一個參數(shù)character_set_client就告訴了MySQL，這條命令是UTF-8編碼，于是MySQL會使用UTF-8解碼字節(jié)流。當(dāng)MySQL成功解碼以后，會將命令內(nèi)容轉(zhuǎn)化為目標(biāo)表格的編碼。

表格的編碼可以通過以下命令查看：

SHOW?FULL?COLUMNS?FROM?student;

假設(shè)MySQL的character_set_client設(shè)置為UTF-8，表格的編碼為GBK。如果在UTF-8的終端中輸入：INSERT INTO student VALUES ('小明', 12)，MySQL首先會用UTF-8解碼這條命令，再將“小明”兩個字轉(zhuǎn)換為對應(yīng)的GBK編碼，最后存入表中。

另外一個參數(shù)character_set_results是指查詢結(jié)果輸出的編碼。如果表格的編碼是GBK，character_set_results設(shè)置為UTF-8，那么在表格中查詢的內(nèi)容會首先轉(zhuǎn)換為UTF-8編碼，再輸出到終端。

MySQL數(shù)據(jù)讀取和寫入的流程可以用下圖表示：

從圖中可以看出，當(dāng)存入表格的解碼/編碼過程和讀取表格的解碼/編碼過程對應(yīng)不上時，就會出現(xiàn)亂碼。

如果要改變character_set_client和character_set_results，可以方便地執(zhí)行一條命令：

SET?names?gbk;
Variable_name Value
character_set_client gbk
character_set_connection gbk
character_set_database utf8
character_set_filesystem binary
character_set_results gbk
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/Cellar/[email protected]/5.7.24/share/mysql/charsets/

這樣，character_set_client和character_set_results就被修改成了GBK。

UTF-8、GBK和Latin-1是MySQL中最常見的三種編碼形式。

• 它們都向下兼容ASCII。同一串使用ASCII編碼的字符，轉(zhuǎn)化為UTF-8、GBK和Latin-1以后的結(jié)果是一樣的。因此，假設(shè)客戶端傳入了SET NAMES latin1這條指令，不論character_set_client設(shè)置為UTF-8、GBK還是Latin-1，都可以正常解碼并執(zhí)行。

• Latin-1是單字節(jié)編碼，其編碼范圍是0x00-0xFF。也就是說任意的8位二進制字節(jié)都可以對應(yīng)于Latin-1中的字符。

• UTF-8的表示范圍遠大于GBK。所有Latin-1字符都能轉(zhuǎn)換為UTF-8字符，但不一定能轉(zhuǎn)換為GBK字符。

以上幾點為MySQL“錯進錯出”提供了條件。所謂的錯進錯出，是指客戶端的字符編碼和最終表的字符編碼格式不同，但是只要保證存和取兩次的字符集編碼一致就仍然能夠獲得沒有亂碼的輸出的這種現(xiàn)象。

?

我們先來考慮這樣一條命令：

INSERT?INTO?table?VALUE("啊");

假設(shè)終端編碼的方式是GBK，“啊”的二進制表示方式就是10110000 10100001。MySQL拿到這個命令以后，通過character_set_client指定的編碼方式進行解碼。

• 如果character_set_client是GBK，MySQL會認為這是一個“啊”字符；

• 如果character_set_client是Latin-1，MySQL會將它看作兩個單獨的Latin-1字符(10110000) (10100001)，最后解碼得到°?。

• 如果character_set_client是UTF-8，由于10110000 10100001 并不是一個有效的UTF-8編碼，所以要么報錯，要么會替換為一個錯誤標(biāo)識?。此時如果直接存入表中，就不能實現(xiàn)“錯進錯出”了。

因此，錯進錯出的一個必要條件是將character_set_client設(shè)置為Latin-1，如果設(shè)置為GBK或者UTF-8就無法保證能正確解碼。

以上是解碼的過程，當(dāng)使用Latin-1解碼完成以后，數(shù)據(jù)還要存入目標(biāo)表格中。

• 如果目標(biāo)表格是Latin-1編碼，解碼完成的數(shù)據(jù)可以直接存入表中。

• 如果目標(biāo)表格是UTF-8編碼，解碼完成的數(shù)據(jù)先轉(zhuǎn)換為UTF-8編碼，再存入表中。

• 如果目標(biāo)表格是GBK編碼，由于并不是每一個Latin-1編碼的字符都能在GBK中找到對應(yīng)的編碼，所以在轉(zhuǎn)碼的過程中可能會報錯。

因此，錯進錯出的另一個條件是目標(biāo)表格必須是Latin-1或者UTF-8編碼。

讀取時，MySQL會將目標(biāo)表格中的數(shù)據(jù)轉(zhuǎn)化為character_set_results指定的編碼。由于我們寫入時使用的Latin-1，讀取時也需要指定character_set_results為Latin-1。這樣最終就實現(xiàn)了“錯進錯出”。

假設(shè)有這樣一張student表：

|name|?age|
|----|----|
|小明|12|
|小紅|10|

其中，name列編碼為Latin-1，其儲存的數(shù)據(jù)使用的編碼為GBK。

也就是說向表里存入數(shù)據(jù)的人可能使用GBK的終端下執(zhí)行了下列語句：

SET?NAMES?latin1;
INSERT?INTO?student VALUES?('小明', 12);

那么，如果我們現(xiàn)在使用的終端編碼為UTF-8，要怎樣從表中查詢關(guān)于小明的信息呢？

1.可以嘗試直接登陸MySQL，輸入以下語句：

SELECT?* FROM?student WHERE?name?= "小明";

但這樣做得到了一個錯誤：

ERROR 1267?(HY000): Illegal mix of?collations (latin1_swedish_ci,IMPLICIT) and?(utf8_general_ci,COERCIBLE) for?operation '='

MySQL默認用戶終端使用的是UTF-8編碼，與表格的編碼 Latin-1 不一致，于是MySQL會首先嘗試把查詢語句轉(zhuǎn)換為Latin-1。但是Latin-1中沒有對應(yīng)“小明”這兩個字的編碼，因此會報錯。

如果增加一條改變character_set_client的語句，會怎么樣呢？

SET?NAMES?latin1;
SELECT?* FROM?students WHERE?name?= "小明";

這一次MySQL會認為用戶的終端就是Latin-1編碼，所以沒有做轉(zhuǎn)換操作。但最終查詢到的結(jié)果卻為空。

這是因為用戶終端的編碼是UTF-8, 因此傳入的“小明”的編碼也是UTF-8，而表格中的數(shù)據(jù)是GBK編碼，它們在內(nèi)存中的儲存形式不同。因此，即便MySQL都將它們當(dāng)作Latin-1處理，也不會認為它們相等。

不直接登陸MySQL，而是在Shell中先將查詢語句轉(zhuǎn)化為GBK編碼，再傳入MySQL：

echo "
SET?names?latin1;
SELECT?* FROM?student WHERE?name?= '小明';"\
| iconv -f utf8 -t gbk\
| mysql -uroot -p123 -Dtest

其中iconv的作用是將標(biāo)準(zhǔn)輸入轉(zhuǎn)換為指定的編碼格式（這里是GBK），再通過標(biāo)準(zhǔn)輸出傳遞給MySQL。我們得到了：

name????age
С?? 12

能查詢到結(jié)果，但名字部分是亂碼。這是由于表格中儲存的數(shù)據(jù)是GBK編碼，而終端編碼是UTF-8。所以還需要增加最后一步：將查詢的結(jié)果轉(zhuǎn)換為UTF-8。

echo "
SET?names?latin1;
SELECT?* FROM?student WHERE?name?= '小明';"\
| iconv -f utf8 -t gbk\
| mysql -uroot -p123 -Dtest\
| iconv -f gbk -t utf8

輸出結(jié)果為：

name????age
小明 12

這樣，我們終于得到了正確的信息。

如果表格本身就是GBK編碼，而不是Latin-1，是否還需要這樣的繁瑣的步驟呢？

答案是不需要的。因為只要正確地設(shè)置了character_set_client和character_set_results，盡管表格的編碼是GBK，MySQL在讀寫的過程中會自動進行轉(zhuǎn)換。

來源：blog.csdn.net/weixin_32431435/article/details/113943983