mysql, space equals empty string

Question

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?

Reaper

Answer 1

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

Implosive

interesting. But normally '' and ' ' are placeholders, I don't really want to use LIKE instead of = in all my queries... – Reaper

Bu why it is happening? Why '' and ' ' is not false. I have tried this PostgreSQL and it is showing correct output False but not MySQL – Intertexture

@Code-Monk - I added an explanation in answer. – Implosive

For some reason LIKE does not break my indexes. I think I'll use it as a temporary solution in a few specific queries... – Reaper

Sorry for accepting this answer that late, I don't know why I didn't do it before – Reaper

Answer 2

Not a vanilla MySQL user, but I was having this problem with MariaDB 10.2.9 as well. I solved it by changing my VARCHAR column collation from utf8mb4_unicode_ci to utf8mb4_unicode_nopad_ci.

SELECT '' = ' ' COLLATE utf8mb4_unicode_ci;
Result: 1

SELECT '' = ' ' COLLATE utf8mb4_unicode_nopad_ci;
Result: 0

Ganges