快捷導航

MySQL亂碼問題終極指南

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

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

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

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

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

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

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

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

【mysql的字符處理機制】

詳細的處理機制如下圖：

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

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

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

有了上面的理論分析后，我們再結(jié)合一個亂碼的抓包實例，加深理解，其中有一些問題，請大家思考一下，看看是否真的理解了。

測試語句是插入一個中文字符“你”，其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ù)庫的字符集設(shè)置成一樣了，接收的數(shù)據(jù)反而不是utf8了？（請與latin1接收數(shù)據(jù)包對比）

5. utf8顯示包

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

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

這個抓包案例的字符變化圖解：

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

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

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

【問題答案】

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

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

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

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

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

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

您可能感興趣的文章: