MySQL編碼機(jī)制原理

前言

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

讀者問(wèn)題簡(jiǎn)介

MyQL 編解碼機(jī)制介紹

問(wèn)題解答

讀者問(wèn)題簡(jiǎn)介

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

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

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

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

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

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

至此問(wèn)題也就解決了，但奇怪的是之前為什么沒(méi)指定編碼方式也是可以的呢，應(yīng)該是 server 指定了編碼方式，在哪指定的？要回答這個(gè)問(wèn)題，就必須得對(duì) MySQL 的編碼機(jī)制有所了解

MyQL 編解碼機(jī)制介紹

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

假設(shè)此時(shí)的客戶端為 Java 工程，用的是 intellj idea，其默認(rèn)編碼為 UTF-8，那么執(zhí)行后這條語(yǔ)句會(huì)首先被 UTF-8 編碼，然后再將其轉(zhuǎn)成 unicode，在 Java 中所有的 String 都是以 unicode 字符存在的，然后再將 unicode 轉(zhuǎn)為用 character_set_client 來(lái)編碼

character_set_client 編碼后是以二進(jìn)制流的形式傳到 MySQL 服務(wù)器的，然后再用 character_set_connection 解碼，然后 MySQL 引擎（比如 innodDB 引擎）會(huì)對(duì)這條語(yǔ)句進(jìn)行語(yǔ)法，詞法解析，執(zhí)行操作

執(zhí)行后的結(jié)果會(huì)轉(zhuǎn)為 DB 的編碼入庫(kù)

如果是 SELECT * FROM t 這樣的查詢操作，那么數(shù)據(jù)會(huì)從 DB 中解碼后再用 character_set_connection 編碼，再轉(zhuǎn)為用 character_set_result 編碼傳給客戶端，客戶端再用 UTF-8 解碼得到正常結(jié)果

先簡(jiǎn)單介紹一下上述步驟中涉及到的編碼集

character_set_client: 客戶端最終發(fā)送到服務(wù)端 SQL 所采用的編碼字符集

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

character_set_result: 結(jié)果集返回給客戶端采用的編碼字符集

知道了以上各個(gè)字符編碼集所代表的釋義，現(xiàn)在就可以輕松解釋開(kāi)頭的問(wèn)題了，我們知道對(duì) MySQL 來(lái)說(shuō)，操作無(wú)非就是增刪改查，所以主要有以下兩個(gè)轉(zhuǎn)化流程

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

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

如果這兩個(gè)轉(zhuǎn)化流程對(duì)應(yīng)的每一步都是無(wú)損轉(zhuǎn)換，那么結(jié)果集就沒(méi)有問(wèn)題的

什么是無(wú)損轉(zhuǎn)換

假設(shè)我們要把用編碼 A 表示的字符 X，轉(zhuǎn)化為編碼 B 的表示形式，而編碼 B 的字符集中并沒(méi)有 X 這個(gè)字符，那么此時(shí)我們就稱這個(gè)轉(zhuǎn)換是有損的，如果在 B 的字符集都能找到 A 中的字符，那么就是無(wú)損的，所以最簡(jiǎn)單的方式就是將每個(gè)步驟對(duì)應(yīng)的編碼字符集都設(shè)置成一樣的，比如都設(shè)置成 UTF-8，這樣就肯定沒(méi)問(wèn)題了。

開(kāi)頭的問(wèn)題解答

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

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

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

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

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:

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

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

##my.cnf

[mysqld]
character-set-server=utf8

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

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

所以問(wèn)題的根因本質(zhì)上是因?yàn)檫w移不完整導(dǎo)致的，只遷移了 DB 數(shù)據(jù)，但沒(méi)有把 my.cnf 這個(gè)配置文件也完整地拷過(guò)來(lái)！拷過(guò)來(lái)之后問(wèn)題就解決了

總結(jié)

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