MySQL字段等值查询时,尾部有空格也能匹配上的坑

一、现象

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    '); #四个空格

 

如上有个user_info表,我们分别插入'adu'、'adu '、'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  '; #两个空格
SELECT * FROM user_info WHERE user_name = 'adu  '; #四个空格
 

无论查询中尾部带有几个空格,结果是一样的,都会命中'adu'、'adu '、'adu    '三个用户

 

二、原因

查询MySQL的官方文档(https://dev.mysql.com/doc/refman/5.7/en/char.html),原来MySQL的校对规则基于PAD SPACE,这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较(“=”)会忽略掉尾部的空格

 

而且这也符合SQL标准,无需设置也无法改变(参考:https://stackoverflow.com/questions/10495692/mysql-comparison-operator-spaces)。

 

三、想要精确查询的解决方案

3.1 like

like不会忽略尾部的空格。

SELECT * FROM user_info WHERE user_name LIKE 'adu';
SELECT * FROM user_info WHERE user_name LIKE 'adu '; #一个空格
SELECT * FROM user_info WHERE user_name LIKE 'adu    '; #四个空格

 

3.2 binary

binary不是函数,是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解成精确匹配。

SELECT * FROM user_info WHERE user_name = BINARY 'adu';
SELECT * FROM user_info WHERE user_name = BINARY 'adu '; #一个空格
SELECT * FROM user_info WHERE user_name = BINARY 'adu    '; #四个空格

注:这里的BINARY关键字要放在“=”的后边,以便有效利用该字段的索引。

四、结论

  • MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比较("=")时,基于PAD SPACE校对规则,会忽略掉尾部的空格
  • 在存储时,不会自动截断尾部的空格,会按原值存储
  • 如果想要精确查询就不能用等值查询(“=”),而应改用like或binary

五、参考

 

posted @ 2020-05-11 19:44  waterystone  阅读(4575)  评论(0编辑  收藏  举报