Why is = different from LIKE when using utf8_unicode_ci index?

Question

I have a database table with column name defined as VARCHAR(255) COLLATE utf8_unicode_ci with a unique index. It contains the name "Grosse".

The following statement returns no rows:

SELECT name FROM authors WHERE name LIKE 'Große' COLLATE 'utf8_unicode_ci'

I've tried both with and without the COLLATE addition (which is probably not necessary).

The following statement returns the correct row:

SELECT name FROM authors WHERE name = 'Große' 

Why do I get different results, shouldn't both statements return the row?

Answer 1

The LIKE Operator performs a match Character by Character. Of Course Große and Grosse are not even equal in length, so it can't match.

Try SELECT 'ß' LIKE 'ss' COLLATE 'utf8_unicode_ci' and you will receive 'false' (0)

Try SELECT 'ß' = 'ss' COLLATE 'utf8_unicode_ci' and you will receive 'true' (1)

The same behaviour applies for whitespaces btw:

'x' LIKE 'x ' will not match, while 'x'='x ' will match.

Please note, that = in mysql is not of the same meaning as == is in any programing language. = in mysql has the meaning of .equal(). If you want to mime the programming languages == you should perform a binary comparision: SELECT BINARY 'ss'='ß' -> false.