使用SQL的LIKE条件搜索德语字符

Question

我正在处理一个使用德语名字的数据库，例如 Fürst。现在我建立了一个带有搜索框的 HTML 表单。使用了 htmlentities 和一些测试来确保没有输入“坏东西”，然后在查询之前使用了 html_entity_decode。一个简单的查询看起来像这样：

SELECT * FROM user_table WHERE firstname LIKE "%fü%" OR lastname LIKE "%fü%" 

然而，这个发现不仅包括 Fürst，还有 Furt。我该如何解决？数据库和表格都是使用 utf8_unicode_ci，必须保持这样的格式。

- yangsunny

---

在比较中添加一个排序规则子句，类似于WHERE firstname collate german_ci like...。 - jarlh

能具体一点吗？没有german_ci。当我使用latin1_german2_ci时，它会显示：#1253 - COLLATION 'latin1_german2_ci' is not valid for CHARACTER SET 'utf8'。当我尝试使用utf8_unicode_ci时，结果与原帖相同。 - yangsunny

我不知道你的数据库管理系统产品的排序规则名称。也许你应该标记一下你正在使用的数据库管理系统，这样你就能得到一个适合你的答案。 - jarlh

服务器正在运行phpmyadmin 4.5.2，使用10.1.9-MariaDB在debian8.3上。这是您所指的吗？ - yangsunny

Answer 1

这里是每个字符集的排序规则

SELECT * FROM user_table WHERE firstname LIKE "%fü%" COLLATE utf8mb4_german2_ci            
                          OR lastname LIKE "%fü%" COLLATE utf8mb4_german2_ci

"utf8mb4_unicode_ci" 可能也可以使用。

- Mihai

---

1. 你是如何想出utf8_bin的？
2. 它不起作用，出现“＃1253 -排序规则'latin1_german2_ci'不适用于字符集'utf8mb4'”

- yangsunny

@yangsunny 我以为你说表格是在 utf8_unicode_ci 中。对于 utf8_unicode_ci 是有效的。而且那个错误消息似乎并不涉及到这个查询。你从哪里得到 latin1_german2_ci 的呢？ - Mihai

抱歉，我指的是“#1253-COLLATION 'utf8_bin'不适用于字符集'utf8mb4'”。而且该数据库肯定是使用了utf8_unicode_ci。 - yangsunny

@yangsunny 在MariaDB中，最低级别字符集具有优先权，列排序覆盖表格排序，表格排序又覆盖数据库排序等。 - Mihai

Answer 2

（对于简单的评论来说，内容太长了。）

COLLATION 名称以 CHARACTER SET 名称开头。本主题已经提到了 utf8、utf8mb4 和 latin1 的 CHARACTER SETs。您必须为给定的 CHARACTER SET 使用一个 COLLATION。

首先选择一个 CHARACTER SET。 utf8、utf8mb4 和 latin1 都可以处理德语。但如果您想处理其他语言，请考虑：

• latin 只支持西欧语言，例如无法处理希腊语。(latin1每个字符需要1个字节)
• utf8mb4 包含所有已知的语言，并可容纳更多。(每个字符最多4个字节) 这种 CHARACTER SET 直到版本5.5.3才可用。
• utf8 是 utf8mb4 的子集，略去了一些中文字符和新的 Emoji。(每个字符最多3个字节)

CHARACTER SET 提供字符的字节编码。 COLLATION 指定两个字符串的比较方式，例如是否将 ss 和 ß 视为相等。

在选择字符集后，选择COLLATION。或者让id为“default”：latin1_swedish_ci/utf8_general_ci/utf8mb4_general_ci。这些默认值对于多语言使用来说有点好，但这是有争议的。在所有这些默认值中，ss！= ß。

从现在开始，“最佳”解决方案是utf8mb4和utf8mb4_unicode_520_ci，适用于整体多语言数据。

如果您只关注德语，则可能要考虑..._german2_ci排序规则。例如：

utf8_german2_ci     A=a=ª=À=Á=Á=Â=Ã=Å=à=á=á=â=ã=å=Ā=ā=Ą=ą     < ae=Ä=Æ=ä=æ < az < B
utf8_unicode_520_ci A=a=ª=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą < ae=Æ=æ     < az < B
utf8_unicode_ci     A=a=ª=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą < ae         < az < Æ=æ < B
utf8_general_ci     A=a=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą   < Z < Æ=æ

(Ditto for utf8mb4_... collations.)的意思是，对于utf8mb4_...排序规则也是一样的。

因此，如果你真的关心æ如何比较，你需要仔细选择排序规则。(utf8和utf8mb4的行为相同。) 更多排序规则比较。

对于..._german2_ci排序规则来说，ue=Ü=ü，但其他排序规则可能没有。其他排序规则(大多数情况下)将U=u=Ü=ü视为相同，而将ue视为两个字母进行比较。

- Rick James

---

在周末，我提出了一种解决方案，使用COLLATE latin1_german2_ci为表中的列和Default CHARSET=utf8 COLLATE=utf8_unicode_ci为表本身。这可以解决我的问题，但我不知道选择列和表的不同排序规则是否可行，或者这只是一个快速而肮脏的解决方案... - yangsunny

我的数据库只包含德语，因此我必须区分A和Ä。我还需要从数据库中获取数据并在HTML中显示。当我使用latin1_german2_ci（它是字符集/排序设置）时，我必须进行一些utf8_encode / decode操作。为了避免这种情况，我想我的选择将是utf8_german2_ci或utf8mb4_german2_ci。非常感谢您对字符集和排序的详细解释。 - yangsunny

我现在将所有的表和列都转换为utf8_german2_ci了。正如你所提到的，按照这种排序规则，A=a < ae=Ä=ä，这意味着如果我搜索ae，应该可以找到ä，对吗？然而，实际上却不行...为什么呢？ - yangsunny

我知道REGEXP在这个领域是不够的，但我认为LIKE还可以。 SET NAMES utf8 COLLATE utf8_german2_ci; select 'ue' = 'ü', 'ue' LIKE 'ü', 'ü' LIKE 'ue'; --> 1,0,0。所以，我关于它们如何比较的说法并不能完全转移到LIKE中。请考虑向http://bugs.mysql.com报告`LIKE`和排序规则的错误。 - Rick James