MySQL - 为什么德语 ß 的 LIKE 运算符会忽略 COLLATION 规则？特点

发布于 2024-12-12 05:49:12 字数 843 浏览 0 评论 0原文

我正在 MySQL 5.0.88 上使用 utf8 字符集和 utf8_unicode_ci 排序规则运行以下 select 语句：

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/en/charset-unicode-sets.html 德国特殊字符 ß = utf8_unicode_ci 的 ss ，但为什么它只适用于“=”运算符而不适用于 LIKE？我有一个电话簿应用程序，我迫切需要这两个应用程序一起工作。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉殇 2024-12-19 05:49:12

根据 SQL 标准，LIKE 基于每个字符执行匹配，
因此它可以产生与 = 比较运算符不同的结果：

mysql>选择 'ä' LIKE 'ae' 整理 latin1_german2_ci；
+----------------------------------------------------+
| 'ä' 像 'ae' 整理 latin1_german2_ci |
+----------------------------------------------------+
| 0 |
+----------------------------------------------------+
mysql>选择 'ä' = 'ae' 整理 latin1_german2_ci；
+--------------------------------------------------+
| 'ä' = 'ae' 整理 latin1_german2_ci |
+--------------------------------------------------+
| 1 |
+--------------------------------------------------+

来源：http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_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 |
+--------------------------------------+

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

回复收藏 0 原文

~没有更多了~