MySQL編碼過程

MySQL由于各種原因被打破，通常與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/mysql@5.7

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

在客戶端將一條命令輸入MySQL時(shí)，MySQL只知道這條命令是0101的字節(jié)流，并不知道具體采用的是什么編碼。第一個(gè)參數(shù)character_set_client就告訴了MySQL，這條命令是UTF-8編碼，于是MySQL會(huì)使用UTF-8解碼字節(jié)流。當(dāng)MySQL成功解碼以后，會(huì)將命令內(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首先會(huì)用UTF-8解碼這條命令，再將“小明”兩個(gè)字轉(zhuǎn)換為對(duì)應(yīng)的GBK編碼，最后存入表中。

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

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

img

從圖中可以看出，當(dāng)存入表格的解碼/編碼過程和讀取表格的解碼/編碼過程對(duì)應(yīng)不上時(shí)，就會(huì)出現(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/mysql@5.7

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

UTF-8、GBK和Latin-1

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位二進(jìn)制字節(jié)都可以對(duì)應(yīng)于Latin-1中的字符。
• UTF-8的表示范圍遠(yuǎn)大于GBK。所有Latin-1字符都能轉(zhuǎn)換為UTF-8字符，但不一定能轉(zhuǎn)換為GBK字符。

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

錯(cuò)進(jìn)錯(cuò)出

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

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

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

• 如果character_set_client是GBK，MySQL會(huì)認(rèn)為這是一個(gè)“啊”字符；
• 如果character_set_client是Latin-1，MySQL會(huì)將它看作兩個(gè)單獨(dú)的Latin-1字符(10110000) (10100001)，最后解碼得到°?。
• 如果character_set_client是UTF-8，由于10110000 10100001 并不是一個(gè)有效的UTF-8編碼，所以要么報(bào)錯(cuò)，要么會(huì)替換為一個(gè)錯(cuò)誤標(biāo)識(shí)?。此時(shí)如果直接存入表中，就不能實(shí)現(xiàn)“錯(cuò)進(jìn)錯(cuò)出”了。

因此，錯(cuò)進(jìn)錯(cuò)出的一個(gè)必要條件是將character_set_client設(shè)置為L(zhǎng)atin-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編碼，由于并不是每一個(gè)Latin-1編碼的字符都能在GBK中找到對(duì)應(yīng)的編碼，所以在轉(zhuǎn)碼的過程中可能會(huì)報(bào)錯(cuò)。

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

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

舉個(gè)例子

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

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

其中，name列編碼為L(zhǎng)atin-1，其儲(chǔ)存的數(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?=?"小明";

但這樣做得到了一個(gè)錯(cuò)誤：

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

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

1. 如果增加一條改變character_set_client的語句，會(huì)怎么樣呢？

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

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

這是因?yàn)橛脩艚K端的編碼是UTF-8, 因此傳入的“小明”的編碼也是UTF-8，而表格中的數(shù)據(jù)是GBK編碼，它們?cè)趦?nèi)存中的儲(chǔ)存形式不同。因此，即便MySQL都將它們當(dāng)作Latin-1處理，也不會(huì)認(rèn)為它們相等。

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é)果，但名字部分是亂碼。這是由于表格中儲(chǔ)存的數(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，是否還需要這樣的繁瑣的步驟呢？

答案是不需要的。因?yàn)橹灰_地設(shè)置了character_set_client和character_set_results，盡管表格的編碼是GBK，MySQL在讀寫的過程中會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。