mysql, space equals empty string

2018-07-02 04:14:05

Just took me 2 hours to troubleshoot an issue on my backend.

Cause was that of empty string being equal to space:

SELECT ' ' = '';
-> 1

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

Interestingly enough,

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

Can I prevent this? Is it a setting?

The reason this fails is explained in the docs here 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.

One way to work around this would be to cast as BINARY

SELECT BINARY '' = ' ';
0

You can also use LIKE :

SELECT '' LIKE ' ';
0

链接地址: http://www.djcxy.com/p/89698.html

上一篇: 为什么2/8888/2016在IE和Firefox中是有效的日期？

下一篇: mysql，空格等于空字符串