快捷導(dǎo)航

MySQL亂碼問題終極指南

更新時(shí)間：2016年04月20日 15:40:31 作者：yah99_wolf

為了讓大家盡量在工作中少受或者不受亂碼的困擾，這篇文章主要為大家分享了MySQL亂碼問題終極指南，感興趣的小伙伴們可以參考一下

mysql的字符集設(shè)置眾多，從客戶端到連接到結(jié)果集，從服務(wù)器到庫(kù)到表到列，都可以設(shè)置字符集，靈活很強(qiáng)大，但就是很容易出問題，如果不了解其機(jī)制，很容易就出現(xiàn)亂碼問題。

為了讓大家盡量在工作中少受或者不受亂碼的困擾，這里我結(jié)合之前其它同學(xué)在論壇的發(fā)帖，并結(jié)合自己的理解和實(shí)踐，詳細(xì)分析總結(jié)了一下，以饗各位看官。

關(guān)于字符集和亂碼的基礎(chǔ)知識(shí)這里就不詳細(xì)說明了（請(qǐng)自行搜索），但有一個(gè)問題需要特別強(qiáng)調(diào)一下：亂碼是怎么產(chǎn)生的？
這個(gè)問題相信很多同學(xué)都是模棱兩可，或者沒有認(rèn)真想過，反正理解就是”字符編碼“不對(duì)導(dǎo)致亂碼，但沒有真正想過為什么”字符編碼“會(huì)導(dǎo)致亂碼。
答案其實(shí)很簡(jiǎn)單：“轉(zhuǎn)換導(dǎo)致亂碼”！
根據(jù)這個(gè)原則來判斷，各種情況就很簡(jiǎn)單了：

1）數(shù)據(jù)傳送過程中不會(huì)導(dǎo)致亂碼
2）數(shù)據(jù)存儲(chǔ)不會(huì)導(dǎo)致亂碼
3）數(shù)據(jù)輸入和輸出（包括顯示）可能導(dǎo)致亂碼
4）數(shù)據(jù)接收和發(fā)送可能導(dǎo)致亂碼

更詳細(xì)的解釋：轉(zhuǎn)換導(dǎo)致亂碼是指本來是A字符集的數(shù)據(jù)被當(dāng)成了B字符集進(jìn)行解析，而不是說正確的A字符集轉(zhuǎn)換為B字符集。
例如：如下mysql字符處理機(jī)制流程圖中，mysql客戶端發(fā)送的實(shí)際上是2個(gè)gbk字符（4字節(jié)），但character_set_connection
設(shè)置了utf8，于是mysql服務(wù)器將收到的4字節(jié)gbk數(shù)據(jù)按照utf8解析，得到1個(gè)中文字符+1個(gè)字節(jié)，這時(shí)就產(chǎn)生亂碼了；

如果character_set_connection 設(shè)置為gbk，mysql服務(wù)器收到數(shù)據(jù)后按照gbk解析，得到兩個(gè)正確的中文，然后再轉(zhuǎn)換為這兩個(gè)中文對(duì)應(yīng)的utf8編碼，這就不會(huì)產(chǎn)生亂碼。）

【mysql的字符處理機(jī)制】

詳細(xì)的處理機(jī)制如下圖：

我們模擬一下一條數(shù)據(jù)從插入到讀取的處理流程，看看在整個(gè)流程中，字符集是如何輾轉(zhuǎn)騰挪的。
【插入流程】
1. 客戶端設(shè)定了自己的編碼（character_set_client），接收用戶的輸入；
2. 客戶端將用戶的輸入“轉(zhuǎn)換”成連接的編碼（character_set_connection） =====> 第一次轉(zhuǎn)換
3. 客戶端將轉(zhuǎn)換后的數(shù)據(jù)發(fā)送給服務(wù)器；                            =====> 傳輸不會(huì)導(dǎo)致編碼轉(zhuǎn)換
4. 服務(wù)器收到客戶端的數(shù)據(jù)，再判斷數(shù)據(jù)列的字符集，進(jìn)行字符轉(zhuǎn)換    =====> 第二次轉(zhuǎn)換
5. 服務(wù)器將數(shù)據(jù)存儲(chǔ)（例如磁盤）                                  =====> 存儲(chǔ)不會(huì)導(dǎo)致編碼轉(zhuǎn)換

【讀取流程】
略去前面的sql語句處理流程，從數(shù)據(jù)讀取開始
1. 服務(wù)器從存儲(chǔ)（例如磁盤）讀取數(shù)據(jù)                               =====> 存儲(chǔ)不會(huì)導(dǎo)致編碼轉(zhuǎn)換，因此從存儲(chǔ)讀取也不需要
2. 服務(wù)器判斷當(dāng)前連接返回結(jié)果的字符集（character_set_results），
將讀取的數(shù)據(jù)轉(zhuǎn)換為結(jié)果集要求的數(shù)據(jù)                            =====> 逆向的第一次轉(zhuǎn)換，對(duì)應(yīng)正向的第二次編碼轉(zhuǎn)換
3. 服務(wù)器將數(shù)據(jù)發(fā)送給客戶端                                        =====> 傳輸不會(huì)導(dǎo)致編碼轉(zhuǎn)換
4. 客戶端收到服務(wù)器的數(shù)據(jù)，根據(jù)客戶端的字符集（character_set_client）進(jìn)行編碼轉(zhuǎn)換       =====> 逆向第二次轉(zhuǎn)換，對(duì)應(yīng)正向第一次編碼轉(zhuǎn)換
5. 客戶端顯示數(shù)據(jù)                                                 =====> 你能看到亂碼的時(shí)候

有了這個(gè)流程，我們就很容易定位亂碼可能產(chǎn)生的地方，以及產(chǎn)生亂碼的字符集配置究竟是哪個(gè)了。
理想的情況是整個(gè)流程中，所有涉及字符轉(zhuǎn)換的地方都不需要轉(zhuǎn)換，這樣就不會(huì)產(chǎn)生亂碼了。

有了上面的理論分析后，我們?cè)俳Y(jié)合一個(gè)亂碼的抓包實(shí)例，加深理解，其中有一些問題，請(qǐng)大家思考一下，看看是否真的理解了。

測(cè)試語句是插入一個(gè)中文字符“你”，其utf8編碼為"0xE4 0xBD 0xA0"，

1. latin1發(fā)送包

思考一下1：為什么客戶端和連接都設(shè)置了latin1，但最終發(fā)送的是正確的utf8編碼呢？

2. latin1接收包

思考一下2：為什么接收到的還是正確的utf8編碼？

3. latin1不顯示亂碼

思考一下3：為什么latin1顯示了正確的utf8字符？

4. utf8接收包

思考一下4：為什么連接的字符集和數(shù)據(jù)庫(kù)的字符集設(shè)置成一樣了，接收的數(shù)據(jù)反而不是utf8了？（請(qǐng)與latin1接收數(shù)據(jù)包對(duì)比）

5. utf8顯示包

思考一下5：為什么連接的字符集和數(shù)據(jù)庫(kù)的字符集設(shè)置成一樣了，顯示反而亂碼了？

怎么樣，上面的思考題是否都有答案了，如果沒有，相信下面這幅圖能夠幫助你：

這個(gè)抓包案例的字符變化圖解：

附：mysql字符編碼操作技巧
【查看字符集設(shè)置】

mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------+
| Variable_name      | 說明                        |
+--------------------------+-----------------------------------------------------+
| character_set_client   | 客戶端字符集                    |
| character_set_connection | 當(dāng)前連接字符集                   |
| character_set_database  | 數(shù)據(jù)庫(kù)字符集                    |
| character_set_filesystem | 文件系統(tǒng)字符集，不要修改，使用binary即可      |
| character_set_results  | 返回結(jié)果集字符集                  |
| character_set_server   | 服務(wù)器默認(rèn)字符集，當(dāng)數(shù)據(jù)庫(kù)、表、列沒有設(shè)置時(shí)，   |
|             |   默認(rèn)使用此字符集                |
| character_set_system   | 固定為utf8                     |
+--------------------------+-----------------------------------------------------+

【修改字符集設(shè)置】
服務(wù)器的配置在服務(wù)器建立的時(shí)候就由DBA設(shè)置好了，不推薦后續(xù)再改
通過SET NAMES utf8命令同時(shí)設(shè)置character_set_client/character_set_connection/character_set_results的字符集
建議所有配置都設(shè)置成utf8

【問題答案】

思考一下1：為什么客戶端和連接都設(shè)置了latin1，但最終發(fā)送的是正確的utf8編碼呢？
客戶端設(shè)置了latin1，而我的語句是從notepad++中寫好的，是utf8格式的；
中文utf8是3個(gè)字節(jié)，而latin1是按照單個(gè)字節(jié)解析的，雖然進(jìn)行了轉(zhuǎn)換，但不會(huì)導(dǎo)致二進(jìn)制內(nèi)容的變化，但實(shí)際上mysql客戶端認(rèn)為我輸入了3個(gè)latin1字符；
如果客戶端設(shè)置的編碼是2個(gè)字節(jié)的gbk，這時(shí)轉(zhuǎn)換就會(huì)發(fā)生亂碼，utf8的3個(gè)字節(jié)會(huì)被轉(zhuǎn)換為1個(gè)gbk字符（可能是亂碼，也可能不是亂碼）加上一個(gè)西歐字符（小于128就是英文，大于128就是其它西歐文）

思考一下2：為什么接收到的還是正確的utf8編碼？
這是因?yàn)閙ysql服務(wù)器從將數(shù)據(jù)從“列”的編碼（utf8）轉(zhuǎn)換為latin1了，而列存儲(chǔ)的數(shù)據(jù)并不是真正的utf8的中文“你”對(duì)應(yīng)的"0xe4 0xbd 0xa0"，
而是后面抓包看到的“c3a4 c2bd c2a0”（6個(gè)字節(jié)），mysql服務(wù)器將utf8的c3a4轉(zhuǎn)換為latin1的0xe4，c2bd轉(zhuǎn)換為0xbd, c2a0轉(zhuǎn)換為0xa0

思考一下3：為什么latin1顯示了正確的utf8字符？
因?yàn)閙ysql客戶端收到了mysql服務(wù)器轉(zhuǎn)換后的"0xe4 0xbd 0xa0"，并把這個(gè)數(shù)據(jù)當(dāng)做latin1的3個(gè)字符處理，然后拋給終端（我的是SecureCRT），
SecureCRT又把這三個(gè)latin1當(dāng)做uft8處理，結(jié)果中文的“你”就顯示出來了。

思考一下4：為什么連接的字符集和數(shù)據(jù)庫(kù)的字符集設(shè)置成一樣了，接收的數(shù)據(jù)反而不是utf8了？（請(qǐng)與latin1接收數(shù)據(jù)包對(duì)比）
字符集都一樣的情況下，整個(gè)流程中不需要進(jìn)行編碼轉(zhuǎn)換，直接將存儲(chǔ)的“c3a4 c2bd c2a0”返回給客戶端

思考一下5：為什么連接的字符集和數(shù)據(jù)庫(kù)的字符集設(shè)置成一樣了，顯示反而亂碼了？
參考思考4，客戶端收到數(shù)據(jù)后也直接拋給終端顯示，終端認(rèn)為是兩個(gè)utf8字符，并且找到了對(duì)應(yīng)字符并顯示，但我們看不懂，所以知道是亂碼了，但這兩個(gè)字符顯示并沒有錯(cuò)，如果真正找不到字符，可能會(huì)顯示問號(hào)或者字符集規(guī)定的缺省符號(hào)。

以上就是關(guān)于MySQL亂碼問題大集合，希望能夠幫助大家解決MySQL亂碼問題，謝謝大家的閱讀。

您可能感興趣的文章: