I just discovered that string comparisons in MySQL ignore trailing whitespace in strings, CHAR, and VARCHAR fields (but not TEXT) - in short, use "like" instead of "=" for literal string comparison.

The strings 'a' and 'a ' (same thing followed by single space) are considered equivalent by MySQL, because trailing whitespace is ignored. But leading whitespace is significant, so 'a' is not equivalent to ' a'.

Why?

This is the kind of subtle, no-idea-until-you-discover-it behavior that makes Perl suck, and I'm disappointed that MySQL has behavior like that, too.

Principle of least surprise, anyone?

mysql> select 'a' = 'a ';
+------------+
| 'a' = 'a ' |
+------------+
|          1 | 
+------------+
1 row in set (0.00 sec)

mysql> select 'a' = ' a';
+------------+
| 'a' = ' a' |
+------------+
|          0 | 
+------------+
1 row in set (0.00 sec)

Using LIKE is a workaround.

mysql> select 'a' like 'a ';
+---------------+
| 'a' like 'a ' |
+---------------+
|             0 | 
+---------------+
1 row in set (0.00 sec)