PostgreSQL不区分大小写和重音的搜索

Question

我有一个数据表，我想要筛选列。例如，通过全名搜索用户。

然而，我希望允许用户输入不区分大小写和重音符号的搜索短语。

因此，我查阅了以下（以及更多）资料和问题：

• https://dev59.com/R2gu5IYBdhLWcg3w4665#11007216
• 如何在PostgreSQL中忽略大小写行
• https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC

我认为非确定性排序可能是最终实现这一目标的正确方式，但不幸的是我不知道如何：

• 将不区分大小写和不考虑重音符号组合成一个排序
• 如何在WHERE子句中只允许使用子字符串进行搜索（例如，仅通过字符串“joh”查找“Jóhn Doe”），因为非确定性排序不支持LIKE或正则表达式
• 使用哪个索引

我会非常感激任何关于如何最终解决这种问题的建议。

谢谢！

- Lunack

Answer 1

创建不区分大小写和重音的ICU排序规则非常简单：

CREATE COLLATION english_ci_ai (
   PROVIDER = icu,
   DETERMINISTIC = FALSE,
   LOCALE = "en-US-u-ks-level1"
);

或者等价地（该语法也适用于旧版本的ICU：

CREATE COLLATION english_ci_ai (
   PROVIDER = icu,
   DETERMINISTIC = FALSE,
   LOCALE = "en-US@colStrength=primary"
);

请参阅ICU文档了解详情，以及我的文章进行详细讨论。

但是你的问题是你想进行子字符串搜索。因此，你应该创建一个trigram索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE INDEX ON tab USING gin (unaccent(doc) gin_trgm_ops);

然后您可以像这样搜索：

SELECT * FROM tab
WHERE unaccent(doc) ILIKE unaccent('%joh%');

请注意，如果您希望搜索操作更加高效，必须强制设置搜索字符串的最小长度为4个字符或以上。

- Laurenz Albe

---

谢谢，您的文章帮助我理解了如何创建自定义ICU排序规则。然而，不幸的是，我的问题仍然存在，即无法使用非确定性ICU根据字符串的一部分进行搜索。 - Lunack

谢谢更新，我原本以为我得一直使用这个没有重音的解决方案。现在我只需要按照我第一个链接中提到的方法创建一个不可变版本的unaccent即可。谢谢！ - Lunack