MySQL-为什么LIKE运算符会忽略德语ß字符的COLLATION规则 [英] MySQL - Why are COLLATION rules ignored by LIKE operator for German ß character
问题描述
我正在使用utf8字符集和utf8_unicode_ci归类在MySQL 5.0.88上运行以下select语句:
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ß |
+----+-------------+------------+
根据 http://dev.mysql.com/doc/refman /5.0/zh-CN/charset-unicode-sets.html utf8_unicode_ci的德语特殊charß= ss，但是为什么它只能与"="运算符一起使用而不能与LIKE一起使用?我有一个电话簿应用程序，我迫切需要两件事协同工作.
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.
推荐答案
根据SQL标准，LIKE会根据每个字符执行匹配， 因此它可以产生与=比较运算符不同的结果:
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 |
+--------------------------------------+
来源: http://dev. mysql.com/doc/refman/5.0/zh-CN/string-comparison-functions.html#operator_like
这篇关于MySQL-为什么LIKE运算符会忽略德语ß字符的COLLATION规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
