SQL: LIKE vs = error?

Question

I have a very strange behavior that I cannot understand in my SQL DB (I use MySQL 5.5.8):

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

If you run sql as follows:

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

I get one result: joe, but this is wrong because in the table I don't have any "joe" (with a space at the end. I only have "joe" (no spaces)

However, if I run:

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

I get, as I expect: nothing. As far as I know, there should be an “exact” match, while, as more freely, you can use it with substrings and%.

What am I missing?

Answer 1

Intermediate spaces are not relevant for comparing CHAR or VARCHAR using = . See string comparison functions :

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed using the statement:

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

Answer 2

I believe this is due to the data type used.

The CHAR and VARCHAR types are similar, but differ in how they are stored and retrieved. Starting with MySQL 5.0.3, they also differ in maximum length and whether trailing spaces remain.

http://dev.mysql.com/doc/refman/5.0/en/char.html