MySQL 編碼不一致竟導致無法命中數據？
本文轉載自微信公眾號「碼農私房話」，作者Liew。轉載本文請聯系碼農私房話公眾號。
由于組內技術棧轉型，需要使用 Java 重構一個 NodeJS 編寫的業務后臺模塊，模塊包含一個根據名稱模糊查詢觸點標簽的功能，這是一個非常普通的 CRUD 操作，但讓人百思不得其解的是模糊查詢并沒有把數據查出來。
項目使用的是 MySQL 數據庫，配置的編碼是 utf8，具體表結構語句如下：
- CREATE TABLE `t_touch_label` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `label_name` varchar(100) DEFAULT NULL COMMENT '標簽名',
- `state` tinyint(1) DEFAULT '1' COMMENT '是否啟用',
- `merchant_id` int(11) NOT NULL COMMENT '用戶ID',
- `remark` int(11) DEFAULT NULL COMMENT '備注'
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
當客戶端與 MySQL 服務器編碼一致時，執行下面語句是能正常查詢到數據的。
- select id,label_name from t_touch_label where merchant_id=10086 and label_name like %B輪標簽%;
但如果未在數據庫 jdbc-url 配置中指定編碼 characterEncoding=utf8 就會導致 like 模糊查詢無法命中數據，這是因為在客戶端操作數據的編碼與 MySQL Server 存儲引擎使用的編碼格式不一致導致的。
問題復現
首先，通過日志定位有問題的 SQL：
- select id,label_name from t_touch_label where merchant_id=10086 and label_name like %B輪標簽%;
并在測試庫上驗證 SQL 是否正常查詢、過濾數據，令我震驚的是一切正常。
接下來本地查看 MyBatis 生成的 SQL 日志，確實查詢出來的是 0 條數據，而且代碼也正常運行沒有報錯，奇了個怪。
于是我產生了一個大膽的猜測：可能是中文參數導致的問題，我把參數改為英文字符會不會就正常呢?
果不其然，當參數輸入為英文字符時，一切都變得如此正常了，同時也驗證了我那“大膽的猜測”是正確的。
按照這個思路，我檢查了 MySQL 服務端：
- character_set_client：客戶端使用的字符集，可通過 characterEncoding 配置，客戶端未配置 characterEncoding 時，會自動檢測并使用 MySQL 服務器的 character_set_server 變量值。
- character_set_results：將查詢結果返回給客戶端使用的字符集，包括結果數據(例如列值)，結果元數據(例如列名)和錯誤消息。
- character_set_system：MySQL 服務器存儲元數據的字符集，始終為 utf8。
- character_sets_dir：字符集的安裝目錄。
- character_set_connection：該字符集用于不帶字符集描述的常量及用于數字到字符串的轉換，
- character_set_server：服務器默認字符集編碼。
- character_set_database：默認數據庫使用的字符集，每當默認數據庫更改時，服務器都會設置此變量，如果沒有默認數據庫，則該變量的值與 character_set_server 相同。
- character_set_filesystem：文件系統字符集編碼，主要用于解析文件名稱的字符串字面值，如 load data 、load file 以及 select into outfile 等語句。在打開文件之前，文件名稱會從 character_set_client 設定編碼轉換為 character_set_filesystem 指定的編碼，默認值為 binary 二進制，即不進行任何轉換。
在項目中，配置的 MySQL 數據庫連接池：
觀察上面 jdbc-url 項的配置不難發現，在 url 中是沒有配置 characterEncoding 字符集編碼。
于是在數據庫的 jdbc-url 中添加配置 characterEncoding=utf8 后再次使用中文模糊查詢就恢復正常。
由此可見，MySQL 使用 like 模糊查詢時，無法命中數據的問題是由于 MySQL 服務端與客戶端字符集編碼集不一致導致的。
編碼不一致，無法命中數據?
MySQL 在存儲數據與查詢數據時，對數據編解碼流程如下：
- MySQL Client 根據 jdbc-url 中設定的 characterEncoding 字符編碼(沒有配置則使用 MySQL Server 配置的 character_set_server )轉換成二進制流，并傳輸到 MySQL Server。
- MySQL Server 收到請求時將請求數據 data 從 character_set_client 轉換為 character_set_connection。
- 在內部操作數據前將請求數據從 character_set_connection 轉換為內部操作的字符集，其編碼確定順序：
- 使用表字段的 character set 值。
- 當上述值不存在，則使用數據表的 default character set 值。
- 若上述值不存在，則使用數據庫的 default character set 值。
- 若上述值不存在，則使用 character_set_server 值。
- 引擎層讀寫存儲文件，涉及內部操作字符集與二進制流之間的相互轉換;
- 將操作結果從內部操作字符集轉換為 character_set_results 。
- MySQL Client 接收到數據后，根據本地配置的字符編碼 characterEncoding 渲染查詢結果。
數據文件到存儲引擎的編解碼：執行 select left(name,2) from table 語句時，存儲引擎加載數據文件時讀入的 name 值是 E4B8ADE69687，而 left(name,2) 操作需要對內容進行分詞處理：
- 如果按照GBK 編碼，該值則分割成E4B8、ADE6、9687 三個字，并返回客戶端的值是 E4B8ADE6;
- 如果按照 UTF8 編碼，就會分割成E4B8AD、E69687，返回客戶端為 E4B8ADE69687 兩個字。
由此可見，從數據文件讀入數據后，如果不進行編解碼，存儲引擎內部是無法進行字符維度的操作。
MySQL 存取數據亂碼
除了上述編碼不一致導致無法命中數據外，還可能引起存取數據亂碼的問題,例如向數據表字符集為 utf8 插入 utf8 編碼的數據，查詢時設置連接字符集為 utf8。
另外在 MySQL Server 的字符集配置中，character_set_client、character_set_results、character_set_connection 等變量的默認值均為 latin1。
插入操作的數據將經過 latin1 -> latin1 -> utf8 的字符集轉換流程，過程中每個漢字會從原始的 3 個字節變成 6 個字節存儲。
查詢的數據將經過 utf8 -> utf8 的字符集轉換，將保存的 6 個字節返回，產生亂碼。
當單個流程中編碼不一致，且兩個字符集之間無法進行無損編碼轉換，也會出現亂碼。
例如 MySQL Client 使用的編碼是 utf8，而 MySQL Server 的 character_set_client 為 gbk，表的字符集為 utf8，則一定出現亂碼。
客戶端的字符編碼和最終表的字符編碼格式雖然不同，但是只要保證存儲和查詢兩個操作的字符集編碼一致且能無損編碼轉換時，就不會產生亂碼的問題。
避免編碼不一致的措施
1、在建立數據庫、表結構或者數據庫操作時，應盡量顯式指定使用的字符集。而 character_set_client、character_set_result、character_set_connection 等變量值與庫表字段字符集定義相同，不依賴于MySQL 的默認設置，否則升級 MySQL 時可能帶來很大困擾。
2、當數據庫和連接字符集都使用 latin1 時，大部分情況下可以解決亂碼問題，但缺點是無法以字符為單位來進行 SQL 操作。一般情況下將數據庫和連接字符集都置為 utf8 ，可避免出現編碼問題。
3、my.cnf 文件中的 default_character_set 設置只影響命令連接服務器時的連接字符集。
4、對字段進行的 SQL 函數操作時，通常都是以內部操作字符集進行的，不受連接字符集設置的影響。
5、SQL 語句中的字符串會受到連接字符集或 introducer (即在 SQL 中對查詢列直接指定字符集)設置的影響，因此對比較的操作可能產生不同的結果。
