1.問題背景

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

柞水網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、APP開發(fā)、響應式網(wǎng)站開發(fā)等網(wǎng)站項目制作，到程序開發(fā)，運營維護。創(chuàng)新互聯(lián)公司成立與2013年到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設就選創(chuàng)新互聯(lián)公司。

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

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

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

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

大G：不信你試試！

我：試試就試試！

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

2.驗證

2.1 數(shù)據(jù)準備

首先在測試庫里建表，并準備相關的原數(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    '; #四個空格

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

太神奇了！

2.2.2 頭部空格驗證

那如果把空格放在前面呢？再來一把，結果如下：這下又匹配不上了?？崭穹旁诤筮吙梢裕旁谇斑叢豢梢?，這太神奇了??！

2.2.3 唯一索引驗證

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

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

2.2.4 長度驗證

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

確實長度也不一樣。

2.3 驗證小結

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

3.分析原因

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

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

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

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

4.精確查詢的方法

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

4.1 LIKE

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

那么我們再使用LIKE進行等值查詢，結果還真可以！

4.2 BINARY

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

那么我們再使用BINARY進行等值查詢，結果也是可以的。

5.總結

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

關于作者

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

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

本文名稱：什么？MySQL的等值查詢竟然出錯了？
文章來源：http://uogjgqi.cn/article/cogosgc.html