1.問題背景

前段時間，一個業(yè)務(wù)線的小伙伴大G找過來，如下是我倆的對話。

大G：云杰，聽說你MySQL挺厲害的，我最近遇到一個奇怪問題，不知道你遇到過沒，請教你下。

我：請教不敢當(dāng)，我也就是個MySQL入門級選手，說來看看。

大G：WHERE條件去等值查詢字符串，結(jié)果卻查出來幾條尾部有空格的，明明不相等。

我：不會吧？這么神奇，這個真沒遇到過！

大G：不信你試試！

我：試試就試試！

抱著求知的心態(tài)，開啟了本篇的探索之旅。

2.驗證

2.1 數(shù)據(jù)準(zhǔn)備

首先在測試庫里建表，并準(zhǔn)備相關(guān)的原數(shù)據(jù)。創(chuàng)建個user_info表，分別插入'adu'(無空格)、'adu '(一個空格)、'adu    '(四個空格)三個用戶。

CREATE TABLE `user_info` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵自增ID',
  `user_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '名字',
 
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
 
INSERT INTO user_info(user_name) values('adu'); #無空格
INSERT INTO user_info(user_name) values('adu '); #一個空格
INSERT INTO user_info(user_name) values('adu    '); #四個空格

2.2 問題驗證

2.2.1 尾部空格驗證

SELECT * FROM user_info WHERE user_name = 'adu'; #無空格
SELECT * FROM user_info WHERE user_name = 'adu '; #一個空格
SELECT * FROM user_info WHERE user_name = 'adu  '; #兩個空格
SELECT * FROM user_info WHERE user_name = 'adu    '; #四個空格

我們使用如上條件去查，還真復(fù)現(xiàn)了！無論查詢中尾部帶有幾個空格，結(jié)果是一樣的，都會命中'adu'、'adu '、'adu    '三個用戶，結(jié)果如下圖所示（紅框圈起來的表示我們認(rèn)為不應(yīng)該出現(xiàn)的異常結(jié)果）：

太神奇了！

2.2.2 頭部空格驗證

那如果把空格放在前面呢？再來一把，結(jié)果如下：這下又匹配不上了。空格放在后邊可以，放在前邊不可以，這太神奇了?。?/p>

2.2.3 唯一索引驗證

那如果在user_name字段上建唯一索引，還能插入這三條記錄嗎？再來一把，結(jié)果如下：

也不行，被唯一索引約束住了。

2.2.4 長度驗證

那這三條記錄的user_name長度又分別是多少呢？

確實長度也不一樣。

2.3 驗證小結(jié)

從結(jié)果上來看，明明是三個長度不同的字符串，空格放在前邊被認(rèn)為是不同，放在后邊又被認(rèn)為是相同，而且唯一索引也沖突。我們有充足的理由懷疑MySQL忽略字符串尾部的空格，把'adu'、'adu '、'adu    '都當(dāng)成'adu'來處理。這確實超出了已有的認(rèn)知，那背后的原因究竟又是什么呢？

3.分析原因

查詢MySQL的官方文檔^[1]，原來跟字符串的校對規(guī)則有關(guān)。

原來MySQL的校對規(guī)則基于PAD SPACE，這就意味著CHAR、VARCHAR、TEXT等字符串的等值比較（“=”）會忽略掉尾部的空格，而且官網(wǎng)也說了，適用于所有MySQL版本，并且不會改變。這。。。

既然MySQL官網(wǎng)說的這么肯定，那么自信來自哪里呢？我們繼續(xù)追查SQL規(guī)范，原來SQL規(guī)范還真對這塊做了特別說明^[2]，如下所示：

既然規(guī)范都這樣要求了，等值查詢“=”不能精確查詢，那么到底該如何精確地進(jìn)行等值查詢呢？

4.精確查詢的方法

通過調(diào)研，我們可以通過以下兩種方式進(jìn)行精確等值查詢。

4.1 LIKE

LIKE是基于逐個字符進(jìn)行比較的，這樣就不會忽略尾部的空格，官網(wǎng)對這塊也有特別的說明。

那么我們再使用LIKE進(jìn)行等值查詢，結(jié)果還真可以！

4.2 BINARY

BINARY不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符，它用來強(qiáng)制它后面的字符串轉(zhuǎn)為二進(jìn)制字節(jié)，再逐個字節(jié)比較，也可以理解成精確匹配，官網(wǎng)^[4]對這塊也有特別的說明。

那么我們再使用BINARY進(jìn)行等值查詢，結(jié)果也是可以的。

5.總結(jié)

• MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比較（"="）時，基于PAD SPACE校對規(guī)則，會忽略掉尾部的空格；
• 在存儲時，不會自動截斷尾部的空格，會按原值存儲；
• 如果想要精確查詢就不能用等值查詢（"="），而應(yīng)改用LIKE或BINARY；
• 認(rèn)知有界，而求知無界。

關(guān)于作者

杜云杰，高級架構(gòu)師，轉(zhuǎn)轉(zhuǎn)架構(gòu)部負(fù)責(zé)人，轉(zhuǎn)轉(zhuǎn)技術(shù)委員會執(zhí)行主席，騰訊云TVP。負(fù)責(zé)服務(wù)治理、MQ、云平臺、APM、IM、分布式調(diào)用鏈路追蹤、監(jiān)控系統(tǒng)、配置中心、分布式任務(wù)調(diào)度平臺、分布式ID生成器、分布式鎖等基礎(chǔ)組件。

道阻且長，擁抱變化；而困而知，且勉且行。