MySQL亂碼問題終極指南_Mysql

mysql的字元集設定眾多，從用戶端到串連到結果集，從伺服器到庫到表到列，都可以設定字元集，靈活很強大，但就是很容易出問題，如果不瞭解其機制，很容易就出現亂碼問題。

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

關於字元集和亂碼的基礎知識這裡就不詳細說明了（請自行搜尋），但有一個問題需要特彆強調一下：亂碼是怎麼產生的？
這個問題相信很多同學都是模稜兩可，或者沒有認真想過，反正理解就是”字元編碼“不對導致亂碼，但沒有真正想過為什麼”字元編碼“會導致亂碼。
答案其實很簡單：“轉換導致亂碼”！
根據這個原則來判斷，各種情況就很簡單了：

1）資料傳送過程中不會導致亂碼
2）資料存放區不會導致亂碼
3）資料輸入和輸出（包括顯示）可能導致亂碼
4）資料接收和發送可能導致亂碼

更詳細的解釋：轉換導致亂碼是指本來是A字元集的資料被當成了B字元集進行解析，而不是說正確的A字元集轉換為B字元集。
例如：如下mysql字元處理機制流程圖中，mysql用戶端發送的實際上是2個gbk字元（4位元組），但character_set_connection
設定了utf8，於是mysql伺服器將收到的4位元組gbk資料按照utf8解析，得到1個中文字元+1個位元組，這時就產生亂碼了；

如果character_set_connection 設定為gbk，mysql伺服器收到資料後按照gbk解析，得到兩個正確的中文，然後再轉換為這兩個中文對應的utf8編碼，這就不會產生亂碼。）

【mysql的字元處理機制】

詳細的處理機制如下圖：

我們類比一下一條資料從插入到讀取的處理流程，看看在整個流程中，字元集是如何輾轉騰挪的。
【插入流程】
1. 用戶端設定了自己的編碼（character_set_client），接收使用者的輸入；
2. 用戶端將使用者的輸入“轉換”成串連的編碼（character_set_connection） =====> 第一次轉換
3. 用戶端將轉換後的資料發送給伺服器；                               =====> 傳輸不會導致編碼轉換
4. 伺服器收到用戶端的資料，再判斷資料列的字元集，進行字元轉換       =====> 第二次轉換
5. 伺服器將資料存放區（例如磁碟）                                     =====> 儲存不會導致編碼轉換  

【讀取流程】
略去前面的sql語句處理流程，從資料讀取開始
1. 伺服器從儲存（例如磁碟）讀取資料                                 =====> 儲存不會導致編碼轉換，因此從儲存讀取也不需要
2. 伺服器判斷當前串連返回結果的字元集（character_set_results），
   將讀取的資料轉換為結果集要求的資料                               =====> 逆向的第一次轉換，對應正向的第二次編碼轉換
3. 伺服器將資料發送給用戶端                                         =====> 傳輸不會導致編碼轉換
4. 用戶端收到伺服器的資料，根據用戶端的字元集（character_set_client）進行編碼轉換          =====> 逆向第二次轉換，對應正向第一次編碼轉換
5. 用戶端顯示資料                                                   =====> 你能看到亂碼的時候

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

有了上面的理論分析後，我們再結合一個亂碼的抓包執行個體，加深理解，其中有一些問題，請大家思考一下，看看是否真的理解了。

環境：
+--------------------------+-----------------------------------------------------+
| Variable_name            | Value                                               |
+--------------------------+-----------------------------------------------------+
| character_set_client     | latin1                                              |
| character_set_connection | latin1                                              |
| character_set_database   | utf8                                                |
| character_set_filesystem | binary                                              |
| character_set_results    | latin1                                              |
| character_set_server     | utf8                                                |

測試語句是插入一個中文字元“你”，其utf8編碼為"0xE4 0xBD 0xA0"，

1. latin1發送包 

思考一下1：為什麼用戶端和串連都設定了latin1，但最終發送的是正確的utf8編碼呢？

2. latin1接收包 

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

3. latin1不顯示亂碼 

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

4. utf8接收包 

思考一下4：為什麼串連的字元集和資料庫的字元集設定成一樣了，接收的資料反而不是utf8了？（請與latin1接收資料包對比）

5. utf8顯示包

思考一下5：為什麼串連的字元集和資料庫的字元集設定成一樣了，顯示反而亂碼了？ 

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

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

附：mysql字元編碼操作技巧
【查看字元集設定】

mysql> show variables like '%char%';+--------------------------+-----------------------------------------------------+| Variable_name      | 說明                        |+--------------------------+-----------------------------------------------------+| character_set_client   | 用戶端字元集                    || character_set_connection | 當前串連字元集                   || character_set_database  | 資料庫字元集                    || character_set_filesystem | 檔案系統字元集，不要修改，使用binary即可      || character_set_results  | 返回結果集字元集                  || character_set_server   | 伺服器預設字元集，當資料庫、表、列沒有設定時，   ||             |   預設使用此字元集                || character_set_system   | 固定為utf8                     |+--------------------------+-----------------------------------------------------+ 

【修改字元集設定】
伺服器的配置在伺服器建立的時候就由DBA設定好了，不推薦後續再改
通過SET NAMES utf8命令同時設定character_set_client/character_set_connection/character_set_results的字元集
建議所有配置都設定成utf8

【問題答案】

思考一下1：為什麼用戶端和串連都設定了latin1，但最終發送的是正確的utf8編碼呢？
用戶端設定了latin1，而我的語句是從notepad++中寫好的，是utf8格式的；
中文utf8是3個位元組，而latin1是按照單個位元組解析的，雖然進行了轉換，但不會導致二進位內容的變化，但實際上mysql用戶端認為我輸入了3個latin1字元；
如果用戶端設定的編碼是2個位元組的gbk，這時轉換就會發生亂碼，utf8的3個位元組會被轉換為1個gbk字元（可能是亂碼，也可能不是亂碼）加上一個西歐字元（小於128就是英文，大於128就是其它西歐文）

思考一下2：為什麼接收到的還是正確的utf8編碼？
這是因為mysql伺服器從將資料從“列”的編碼（utf8）轉換為latin1了，而列儲存的資料並不是真正的utf8的中文“你”對應的"0xe4 0xbd 0xa0"，
而是後面抓包看到的“c3a4 c2bd c2a0”（6個位元組），mysql伺服器將utf8的c3a4轉換為latin1的0xe4，c2bd轉換為0xbd, c2a0轉換為0xa0

思考一下3：為什麼latin1顯示了正確的utf8字元？
因為mysql用戶端收到了mysql伺服器轉換後的"0xe4 0xbd 0xa0"，並把這個資料當做latin1的3個字元處理，然後拋給終端（我的是SecureCRT），
SecureCRT又把這三個latin1當做uft8處理，結果中文的“你”就顯示出來了。

思考一下4：為什麼串連的字元集和資料庫的字元集設定成一樣了，接收的資料反而不是utf8了？（請與latin1接收資料包對比）
字元集都一樣的情況下，整個流程中不需要進行編碼轉換，直接將儲存的“c3a4 c2bd c2a0”返回給用戶端

思考一下5：為什麼串連的字元集和資料庫的字元集設定成一樣了，顯示反而亂碼了？
參考思考4，用戶端收到資料後也直接拋給終端顯示，終端認為是兩個utf8字元，並且找到了對應字元並顯示，但我們看不懂，所以知道是亂碼了，但這兩個字元顯示並沒有錯，如果真正找不到字元，可能會顯示問號或者字元集規定的預設符號。

以上就是關於MySQL亂碼問題大集合，希望能夠協助大家解決MySQL亂碼問題，謝謝大家的閱讀。