MySQL 比较运算符，空格

Question

如果数据库行是这样的:country = 'usa' 并且我查询 "select * from data where country = 'usa'" 它也返回这一行.所以它不是完全匹配。

为什么 MySQL 这样做？在其他情况下，当它不是真的时，它也会返回 TRUE？

Answer 1

如 the manual 中所述:

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.

在LIKE的定义中运营商，它指出:

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

如 this answer 中所述:

This behavior is specified in SQL-92 and SQL:2008. For the purposes of comparison, the shorter string is padded to the length of the longer string.

From the draft (8.2 <comparison predicate>):

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

除了其他优秀的解决方案:

select binary 'a' = 'a   '