MySQL - Why are COLLATION rules ignored by LIKE op

Question

I'm running the following select statements on MySQL 5.0.88 with utf8 charset and utf8_unicode_ci collation:

SELECT * FROM table WHERE surname = 'abcß';

+----+-------------------+------+
| id | forename    | surname    |
+----+-------------------+------+
|  1 | a           | abcß       |
|  2 | b           | abcss      |
+----+-------------+------------+

---

SELECT * FROM table WHERE surname LIKE 'abcß';

+----+-------------------+------+
| id | forename    | surname    |
+----+-------------------+------+
|  1 | a           | abcß       |
+----+-------------+------------+

According to http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html the german special char ß = ss for utf8_unicode_ci, but why does it only work with the "=" operator and not with LIKE? I have a phone book application and I desperately need both things working together.

Answer 1

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

Source: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like