[[402050]]

本文轉(zhuǎn)載自微信公眾號「碼農(nóng)私房話」，作者Liew。轉(zhuǎn)載本文請聯(lián)系碼農(nóng)私房話公眾號。

由于組內(nèi)技術(shù)棧轉(zhuǎn)型，需要使用 Java 重構(gòu)一個 NodeJS 編寫的業(yè)務(wù)后臺模塊，模塊包含一個根據(jù)名稱模糊查詢觸點(diǎn)標(biāo)簽的功能，這是一個非常普通的 CRUD 操作，但讓人百思不得其解的是模糊查詢并沒有把數(shù)據(jù)查出來。

項(xiàng)目使用的是 MySQL 數(shù)據(jù)庫，配置的編碼是 utf8，具體表結(jié)構(gòu)語句如下：

CREATE TABLE `t_touch_label` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `label_name` varchar(100) DEFAULT NULL COMMENT '標(biāo)簽名', 
  `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; 

當(dāng)客戶端與 MySQL 服務(wù)器編碼一致時，執(zhí)行下面語句是能正常查詢到數(shù)據(jù)的。

select id,label_name from t_touch_label where merchant_id=10086 and label_name like %B輪標(biāo)簽%; 

但如果未在數(shù)據(jù)庫 jdbc-url 配置中指定編碼 characterEncoding=utf8 就會導(dǎo)致 like 模糊查詢無法命中數(shù)據(jù)，這是因?yàn)樵诳蛻舳瞬僮鲾?shù)據(jù)的編碼與 MySQL Server 存儲引擎使用的編碼格式不一致導(dǎo)致的。

問題復(fù)現(xiàn)

首先，通過日志定位有問題的 SQL：

select id,label_name from t_touch_label where merchant_id=10086 and label_name like %B輪標(biāo)簽%; 

并在測試庫上驗(yàn)證 SQL 是否正常查詢、過濾數(shù)據(jù)，令我震驚的是一切正常。

接下來本地查看 MyBatis 生成的 SQL 日志，確實(shí)查詢出來的是 0 條數(shù)據(jù)，而且代碼也正常運(yùn)行沒有報(bào)錯，奇了個怪。

于是我產(chǎn)生了一個大膽的猜測：可能是中文參數(shù)導(dǎo)致的問題，我把參數(shù)改為英文字符會不會就正常呢?

果不其然，當(dāng)參數(shù)輸入為英文字符時，一切都變得如此正常了，同時也驗(yàn)證了我那“大膽的猜測”是正確的。

按照這個思路，我檢查了 MySQL 服務(wù)端：

• character_set_client：客戶端使用的字符集，可通過 characterEncoding 配置，客戶端未配置 characterEncoding 時，會自動檢測并使用 MySQL 服務(wù)器的 character_set_server 變量值。
• character_set_results：將查詢結(jié)果返回給客戶端使用的字符集，包括結(jié)果數(shù)據(jù)(例如列值)，結(jié)果元數(shù)據(jù)(例如列名)和錯誤消息。
• character_set_system：MySQL 服務(wù)器存儲元數(shù)據(jù)的字符集，始終為 utf8。
• character_sets_dir：字符集的安裝目錄。
• character_set_connection：該字符集用于不帶字符集描述的常量及用于數(shù)字到字符串的轉(zhuǎn)換，
• character_set_server：服務(wù)器默認(rèn)字符集編碼。
• character_set_database：默認(rèn)數(shù)據(jù)庫使用的字符集，每當(dāng)默認(rèn)數(shù)據(jù)庫更改時，服務(wù)器都會設(shè)置此變量，如果沒有默認(rèn)數(shù)據(jù)庫，則該變量的值與 character_set_server 相同。
• character_set_filesystem：文件系統(tǒng)字符集編碼，主要用于解析文件名稱的字符串字面值，如 load data 、load file 以及 select into outfile 等語句。在打開文件之前，文件名稱會從 character_set_client 設(shè)定編碼轉(zhuǎn)換為 character_set_filesystem 指定的編碼，默認(rèn)值為 binary 二進(jìn)制，即不進(jìn)行任何轉(zhuǎn)換。

在項(xiàng)目中，配置的 MySQL 數(shù)據(jù)庫連接池：

觀察上面 jdbc-url 項(xiàng)的配置不難發(fā)現(xiàn)，在 url 中是沒有配置 characterEncoding 字符集編碼。

于是在數(shù)據(jù)庫的 jdbc-url 中添加配置 characterEncoding=utf8 后再次使用中文模糊查詢就恢復(fù)正常。

由此可見，MySQL 使用 like 模糊查詢時，無法命中數(shù)據(jù)的問題是由于 MySQL 服務(wù)端與客戶端字符集編碼集不一致導(dǎo)致的。

編碼不一致，無法命中數(shù)據(jù)?

MySQL 在存儲數(shù)據(jù)與查詢數(shù)據(jù)時，對數(shù)據(jù)編解碼流程如下：

• MySQL Client 根據(jù) jdbc-url 中設(shè)定的 characterEncoding 字符編碼(沒有配置則使用 MySQL Server 配置的 character_set_server )轉(zhuǎn)換成二進(jìn)制流，并傳輸?shù)?MySQL Server。
• MySQL Server 收到請求時將請求數(shù)據(jù) data 從 character_set_client 轉(zhuǎn)換為 character_set_connection。
• 在內(nèi)部操作數(shù)據(jù)前將請求數(shù)據(jù)從 character_set_connection 轉(zhuǎn)換為內(nèi)部操作的字符集，其編碼確定順序：
  • 使用表字段的 character set 值。
  • 當(dāng)上述值不存在，則使用數(shù)據(jù)表的 default character set 值。
  • 若上述值不存在，則使用數(shù)據(jù)庫的 default character set 值。
  • 若上述值不存在，則使用 character_set_server 值。
• 引擎層讀寫存儲文件，涉及內(nèi)部操作字符集與二進(jìn)制流之間的相互轉(zhuǎn)換;
• 將操作結(jié)果從內(nèi)部操作字符集轉(zhuǎn)換為 character_set_results 。
• MySQL Client 接收到數(shù)據(jù)后，根據(jù)本地配置的字符編碼 characterEncoding 渲染查詢結(jié)果。

數(shù)據(jù)文件到存儲引擎的編解碼：執(zhí)行 select left(name,2) from table 語句時，存儲引擎加載數(shù)據(jù)文件時讀入的 name 值是 E4B8ADE69687，而 left(name,2) 操作需要對內(nèi)容進(jìn)行分詞處理：

• 如果按照GBK 編碼，該值則分割成E4B8、ADE6、9687 三個字，并返回客戶端的值是 E4B8ADE6;
• 如果按照 UTF8 編碼，就會分割成E4B8AD、E69687，返回客戶端為 E4B8ADE69687 兩個字。

由此可見，從數(shù)據(jù)文件讀入數(shù)據(jù)后，如果不進(jìn)行編解碼，存儲引擎內(nèi)部是無法進(jìn)行字符維度的操作。

MySQL 存取數(shù)據(jù)亂碼

除了上述編碼不一致導(dǎo)致無法命中數(shù)據(jù)外，還可能引起存取數(shù)據(jù)亂碼的問題,例如向數(shù)據(jù)表字符集為 utf8 插入 utf8 編碼的數(shù)據(jù)，查詢時設(shè)置連接字符集為 utf8。

另外在 MySQL Server 的字符集配置中，character_set_client、character_set_results、character_set_connection 等變量的默認(rèn)值均為 latin1。

插入操作的數(shù)據(jù)將經(jīng)過 latin1 -> latin1 -> utf8 的字符集轉(zhuǎn)換流程，過程中每個漢字會從原始的 3 個字節(jié)變成 6 個字節(jié)存儲。

查詢的數(shù)據(jù)將經(jīng)過 utf8 -> utf8 的字符集轉(zhuǎn)換，將保存的 6 個字節(jié)返回，產(chǎn)生亂碼。

當(dāng)單個流程中編碼不一致，且兩個字符集之間無法進(jìn)行無損編碼轉(zhuǎn)換，也會出現(xiàn)亂碼。

例如 MySQL Client 使用的編碼是 utf8，而 MySQL Server 的 character_set_client 為 gbk，表的字符集為 utf8，則一定出現(xiàn)亂碼。

客戶端的字符編碼和最終表的字符編碼格式雖然不同，但是只要保證存儲和查詢兩個操作的字符集編碼一致且能無損編碼轉(zhuǎn)換時，就不會產(chǎn)生亂碼的問題。

避免編碼不一致的措施

1、在建立數(shù)據(jù)庫、表結(jié)構(gòu)或者數(shù)據(jù)庫操作時，應(yīng)盡量顯式指定使用的字符集。而 character_set_client、character_set_result、character_set_connection 等變量值與庫表字段字符集定義相同，不依賴于MySQL 的默認(rèn)設(shè)置，否則升級 MySQL 時可能帶來很大困擾。

2、當(dāng)數(shù)據(jù)庫和連接字符集都使用 latin1 時，大部分情況下可以解決亂碼問題，但缺點(diǎn)是無法以字符為單位來進(jìn)行 SQL 操作。一般情況下將數(shù)據(jù)庫和連接字符集都置為 utf8 ，可避免出現(xiàn)編碼問題。

3、my.cnf 文件中的 default_character_set 設(shè)置只影響命令連接服務(wù)器時的連接字符集。

4、對字段進(jìn)行的 SQL 函數(shù)操作時，通常都是以內(nèi)部操作字符集進(jìn)行的，不受連接字符集設(shè)置的影響。

5、SQL 語句中的字符串會受到連接字符集或 introducer (即在 SQL 中對查詢列直接指定字符集)設(shè)置的影響，因此對比較的操作可能產(chǎn)生不同的結(jié)果。