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