[[433928]]

前言

一位讀者在本地部署 MySQL 測試環(huán)境時碰到一個問題，我覺得挺有代表性的，所以寫篇文章介紹一下，看完相信你會對 MySQL 的編碼機制有最本質(zhì)的了解，本文的目錄結構如下

• 讀者問題簡介
• MyQL 編解碼機制介紹
• 問題解答

讀者問題簡介

MyQL 編解碼機制介紹

問題解答

讀者問題簡介

為敘述方便，以下的「我」指代讀者

我們知道在 Java 中是通過 JDBC 來訪問數(shù)據(jù)庫的，以訪問 MySQL 為例，需要配置以下 url 才能訪問 MySQL

jdbc:mysql://10.65.110.9:3306/test?connectTimeout=5000&socketTimeout=20000 

這樣配置之前在我司的測試環(huán)境中 CRUD 是沒有問題的，但是后來想在個人的機器上部署一下 MySQL 環(huán)境就出問題了，首先為了保證數(shù)據(jù)的完整性，我將公司測試機的 SQL 全部導出后再導入到個人的 MySQL 環(huán)境中，但是詭異的事情發(fā)生了：此時在 Java 工程中如果查詢的 SQL 中都是英文是可以正常工作的，但如果包含中文(比如 SELECT * FROM USER WHERE name = '張三')是無法查詢到結果的。

碰到這種情況，一般我們會想到是編碼轉換出現(xiàn)了問題，相信聰明你不難發(fā)現(xiàn)上面的 jdbc url 似乎少了點什么，沒錯，就是沒有指定編碼方式，只要按如下方式指定了編碼方式(characterEncoding=UTF-8)即可正常工作

jdbc:mysql://10.65.110.9:3306/test?connectTimeout=5000&socketTimeout=20000&characterEncoding=UTF-8 

至此問題也就解決了，但奇怪的是之前為什么沒指定編碼方式也是可以的呢，應該是 server 指定了編碼方式，在哪指定的?要回答這個問題，就必須得對 MySQL 的編碼機制有所了解

MyQL 編解碼機制介紹

我們先來看看 MySQL 中涉及到哪些編碼流程，假設客戶端用的是 UTF-8 編碼，那么發(fā)送一條 SQL 語句會發(fā)生如下的編解碼流程:

1. 假設此時的客戶端為 Java 工程，用的是 intellj idea，其默認編碼為 UTF-8，那么執(zhí)行后這條語句會首先被 UTF-8 編碼，然后再將其轉成 unicode，在 Java 中所有的 String 都是以 unicode 字符存在的，然后再將 unicode 轉為用 character_set_client 來編碼
2. character_set_client 編碼后是以二進制流的形式傳到 MySQL 服務器的，然后再用 character_set_connection 解碼，然后 MySQL 引擎(比如 innodDB 引擎)會對這條語句進行語法，詞法解析，執(zhí)行操作
3. 執(zhí)行后的結果會轉為 DB 的編碼入庫
4. 如果是 SELECT * FROM t 這樣的查詢操作，那么數(shù)據(jù)會從 DB 中解碼后再用 character_set_connection 編碼，再轉為用 character_set_result 編碼傳給客戶端，客戶端再用 UTF-8 解碼得到正常結果

先簡單介紹一下上述步驟中涉及到的編碼集

1. character_set_client: 客戶端最終發(fā)送到服務端 SQL 所采用的編碼字符集
2. character_set_connection: MySQL 服務端收到步驟 1 編碼后的二進制流后采用的編碼字符集，會將步驟 1 傳過來的數(shù)據(jù)進行解碼。一般與 character_set_client 是一樣的，有人可能會奇怪，為什么會有這個字符集，直接用 character_set_client 來解碼不就行了，它存在的意義是啥呢?其實主要是為了作用上的的分離，character_set_client 主要用來客戶端的編碼，而 character_set_connection 主要是為了賦予開發(fā)人員解析語義的自由，比如考慮 SELECT LENGTH('中') 這樣的場景，如果采用 GBK 一個漢字 2 個長度，結果是 2，而如果是 UTF-8 編碼，則結果是 3，所以額外設定一個 character_set_connection 編碼，讓開發(fā)人員可以根據(jù)需要更自由地定義不同的業(yè)務場景
3. character_set_result: 結果集返回給客戶端采用的編碼字符集

知道了以上各個字符編碼集所代表的釋義，現(xiàn)在就可以輕松解釋開頭的問題了，我們知道對 MySQL 來說，操作無非就是增刪改查，所以主要有以下兩個轉化流程

如果是增刪改操作，流程為：客戶端--->character_set_client--->character_set_connection---->DB

如果是查操作，客戶端--->character_set_client--->character_set_connection---->DB---->character_set_result

如果這兩個轉化流程對應的每一步都是無損轉換，那么結果集就沒有問題的

什么是無損轉換

假設我們要把用編碼 A 表示的字符 X，轉化為編碼 B 的表示形式，而編碼 B 的字符集中并沒有 X 這個字符，那么此時我們就稱這個轉換是有損的，如果在 B 的字符集都能找到 A 中的字符，那么就是無損的，所以最簡單的方式就是將每個步驟對應的編碼字符集都設置成一樣的，比如都設置成 UTF-8，這樣就肯定沒問題了。

開頭的問題解答

現(xiàn)在回過頭來看一下開頭的問題，為什么將 DB 數(shù)據(jù)從公司的測試機導入到個人機器后，如果 SQL 中包含有中文查詢?nèi)缦?jdbc url 的配置會導致原本正常返回的結果集失效呢?

jdbc:mysql://10.65.110.9:3306/test?connectTimeout=5000&socketTimeout=20000 

顯然是客戶端--->character_set_client--->character_set_connection---->DB---->character_set_result 這個步驟中的結果集發(fā)生了有損轉換，到底是哪一步呢?

DB 表數(shù)據(jù)采用的編碼都是 UTF-8，如果只要搞清楚 character_set_client，character_set_connection，character_set_result 這三個編碼字符集是啥問題就解決了，這個問題的答案得去官網(wǎng)找,來看下官網(wǎng)是怎么說的

The character encoding between client and server is automatically detected upon connection (provided that the Connector/J connection properties characterEncoding and connectionCollation are not set). You specify the encoding on the server using the system variable character_set_server (for more information, see Server Character Set and Collation). The driver automatically uses the encoding specified by the server.

To override the automatically detected encoding on the client side, use the characterEncoding property in the connection URL to the server. Use Java-style names when specifying character encodings. The following table lists MySQL character set names and their corresponding Java-style names:

從中我們可以看到，如果未設置 characterEncoding，那么 character_set_client，character_set_connection，character_set_result 這三的編碼字符集與 character_set_server 的設置相同，如果設置了 characterEncoding，那么這三者的值與 characterEncoding 相同，這就是為什么指定了characterEncoding=utf8后 SQL 能正常工作的原因了，

那為什么不指定 characterEncoding=utf8 在公司的測試 MySQL 服務器中可以正常工作呢，顯然是設置了 character_set_server，在哪設置?在 MySQL 的配置文件 my.cnf 設置

## my.cnf 
 
[mysqld] 
character-set-server=utf8 

再來看為什么在個人的測試機中包含有中文的 SQL 卻不生效呢，因為個人的測試機當時用 docker 搭了一個 MySQL，它的 my.cnf 文件是空的，這種情況下 character-set-server 編碼字符集是 latin，于是 character_set_client，character_set_connection，character_set_result 這三者的編碼字符集也都為 latin 了，顯然在第一步客戶端轉 chacacter_set_client 就出現(xiàn)了問題

我們之前提過在 Java 中所有的字符串都以 unicode 形式存在，而 latin 字符集是不包含中文的，那么顯然中文的 unicode 在 latin1 中是找不到對應的字符的，這一步就會發(fā)生有損編碼，這就是為什么在個人的機器上執(zhí)行帶有中文的 SQL 會出異常的根本原因!

所以問題的根因本質(zhì)上是因為遷移不完整導致的，只遷移了 DB 數(shù)據(jù)，但沒有把 my.cnf 這個配置文件也完整地拷過來!拷過來之后問題就解決了

總結

知道了 MySQL 編解碼機制，之后再碰到類似的問題就比較簡單了，比如亂碼，顯然就是上述步驟中的步驟發(fā)生了有損編碼

本文轉載自微信公眾號「碼海」，可以通過以下二維碼關注。轉載本文請聯(lián)系碼海公眾號。