Mysql查询条件以空格结尾问题

睿之父

作者：Yunga
问题描述

最近在查询Mysql中数据时无意间在=条件字符串后增加了一个空格，发现查询依然是有效的，同时尝试增加更多的空格，发现依然能生效。
例如，我们有如下一张表：
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `txt` text,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;表里有三条数据：
id  name    txt
1   name1   txt1
2   name2   txt2
3   name3   txt3使用如下sql进行查询：
SELECT * FROM test WHERE `txt` = 'txt1 ';
SELECT * FROM test WHERE `name` = 'name1  ';都能查到如下这条数据：
id  name    txt
1   name1   txt1另外这个问题，反过来依然生效，比如我的 WHERE等于的条件末尾不带空格，但数据库中的数据是带有空格的，依然能够将数据查询处理。这种数据查询行为和我们的预期完全不一致，我们通常潜意识都会认为=匹配的条件必然是完全相等的，不管有无空格符号。
问题分析

通过翻阅我们对应所使用版本的 Mysql5.6参考手册 中提到的：

Values in CHAR, VARCHAR, and TEXT columns are sorted and compared according to the character set collation assigned to the column.

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

也就是说在Mysql中，CHAR、VARCHAR、TEXT类型值的比较是根据分配的字符集排序规则来排序和比较的，并且Mysql所有排序规则都是通过空格来填充的，这就意味着，比较所有类型为CHAR、VARCHAR、TEXT值的时候，都是不考虑结尾空格的，但是不包括使用LIKE进行匹配，LIKE匹配结尾的空格是非常重要的。
解决方案

方案一：使用LIKE匹配

使用LIKE进行字符串匹配，将不会忽略查询条件尾部空格，如下：
SELECT * FROM test WHERE `name` = BINARY 'name1 ';方案二：使用BINARY运算符

BINARY运算符会将表达式转换为二进制字符串，使用BINARY运算符后字符串将强制使用每个字节的数字值进行比较，而不是逐个比较每个字符，这样将使得查询条件的结尾空格也生效，同样的大小写也将生效。
如下在查询条件有空格的情况下，将无法获取到相应数据。
SELECT * FROM test WHERE `name` = BINARY 'name1 ';
SELECT * FROM test WHERE `txt` = BINARY 'txt  ';性能思考

我们都知道如果在列有索引的情况下LIKE匹配是在索引进行范围搜索，而=则是从索引进行常量匹配，因此=的性能是要比LIKE更好的，那么我们使用 BINARY运算符之后进行的=查找性能是否有变化呢？通过如下进行分析：
EXPLAIN SELECT * FROM test WHERE `name` = BINARY 'name1';查找的连接类型由直接使用 =的 const变成了和LIKE一样的范围搜索了，因此在使用 BINARY关键字后的=查询，性能也退化到和LIKE一样了。
官方手册参考链接

• https://dev.mysql.com/doc/refman/5.6/en/
  
• https://dev.mysql.com/doc/refman/5.6/en/char.html
  
• https://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html#operator_like
  
• https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#operator_binary
  
• https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-join-types
  

冷漠人

无论是不是沙发都得回复下