mysql-5.7 - mysql，空格等于空字符串

Question

我只花了 2 个小时就解决了后端的问题。

原因是空字符串等于空格:

SELECT ' ' = '';
-> 1

SELECT STRCMP(' ', '');
-> 0 /* means equal */

有趣的是，

SELECT '' REGEXP '[ ]';
-> 0
SELECT '' REGEXP ' ';
-> 0
SELECT ' ' REGEXP ' ';
-> 1

我可以防止这种情况吗？是设定吗？

Answer 1

此处的文档中解释了失败的原因 http://dev.mysql.com/doc/refman/5.0/en/char.html :

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

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL 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.

解决此问题的一种方法是强制转换为 BINARY

SELECT BINARY '' = ' ';
0

您也可以使用 LIKE :

SELECT '' LIKE ' ';
0