SQL: LIKE vs = bug? [duplicate]

Question

I have a very strange behaviour that I cannot understand in my SQL DB (I am using MySQL 5.5.8):

I have in a table a varchar(10) a name: joe.

If a run a sql like this:

SELECT ID FROM `names` WHERE `name` = 'joe '

I get one result: joe but this is wrong since in the table I do not have any 'joe ' (with a space at the end. I only have 'joe' (no space)

However if I execute:

SELECT ID FROM `names` WHERE `name` LIKE 'joe '

I get as I expect: nothing. As far as I know = should be "exact" matching while like is more loose to be able to use it with sub strings and %.

What am I missing ?

Answer 1

Trailing spaces are not significant for CHAR or VARCHAR comparison using =. See string comparison functions:

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

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