Mysql, space equals empty string

Question

It just took me 2 hours to fix the problem on my server.

The fact is that an empty string equals a space:

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

Interesting that

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

Can this be prevented? Is this a setting?

Answer 1

The reason for this is not explained in the docs here http://dev.mysql.com/doc/refman/5.0/en/char.html :

The values ​​in the CHAR and VARCHAR columns are sorted and compared to match the character set assigned to the column.

All MySQL mappings are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values ​​in MySQL are compared without any trailing spaces. “Comparison” in this context does not include the LIKE-template operator for which trailing spaces are significant.

One way to get around this could be done as BINARY

 SELECT BINARY '' = ' '; 0 

You can also use LIKE :

 SELECT '' LIKE ' '; 0