PostgreSQL 是否支持“不区分重音”排序规则？

Question

在 Microsoft SQL Server 中，可以指定“不区分重音”排序规则（用于数据库、表或列），这意味着可以进行类似的查询

SELECT * FROM users WHERE name LIKE 'João'

查找具有 Joao 名称的行。

我知道可以使用 unaccent_string contrib 函数从 PostgreSQL 中的字符串中去除重音符号，但我想知道 PostgreSQL 是否支持这些“重音不敏感”排序规则，所以上面的 SELECT 可以工作。

原文由 Daniel Serodio 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

Postgres 12 或更高版本的更新

Postgres 12 添加了 不确定的 ICU 排序规则，支持不区分大小写和不区分重音的分组和排序。 手册：

只有在构建 PostgreSQL 时配置了对 ICU 的支持，才能使用 ICU 语言环境。

如果是这样，这对你有用：

 CREATE COLLATION ignore_accent (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

CREATE INDEX users_name_ignore_accent_idx ON users(name COLLATE ignore_accent);

SELECT * FROM users WHERE name = 'João' COLLATE ignore_accent;

小提琴

阅读手册了解详情。 Laurenz Albe 的这篇博文 可能有助于理解。

但ICU排序规则也有 缺点。 手册：

[…] 它们也有一些缺点。最重要的是，它们的使用会导致性能下降。请特别注意，B 树不能对使用非确定性排序规则的索引使用重复数据删除。此外，某些操作对于非确定性排序规则是不可能的，例如模式匹配操作。因此，它们应仅在特别需要的情况下使用。

我的“遗留”解决方案可能仍然更好：

对于所有版本

为此使用 unaccent 模块- 这与您链接的内容完全不同。

unaccent 是一个文本搜索字典，可以从词位中删除重音符号（变音符号）。

每个数据库安装一次：

 CREATE EXTENSION unaccent;

如果您收到如下错误：

>  ERROR: could not open extension control file
> "/usr/share/postgresql/<version>/extension/unaccent.control": No such file or directory
>
> ```

按照相关答案中的说明在您的数据库服务器上安装 contrib 包：

- [在 PostgreSQL 上创建非重音扩展时出错](https://stackoverflow.com/questions/12100638/error-when-creating-unaccent-extension-on-postgresql)

除其他外，它提供了功能 `unaccent()` 您可以在您的示例中使用（其中 `LIKE` 似乎不需要）。

SELECT * FROM users WHERE unaccent(name) = unaccent(‘João’);

## 指数

要为此类查询使用 [索引，请在表达式上创建索引](https://www.postgresql.org/docs/current/indexes-expressional.html)。 **但是**，Postgres 只接受 `IMMUTABLE` 索引函数。如果函数可以为相同的输入返回不同的结果，则索引可能会静默中断。

### `unaccent()` 只有 `STABLE` 不是 `IMMUTABLE`

不幸的是， `unaccent()` 只是 `STABLE` ，而不是 `IMMUTABLE` 。根据 [pgsql-bugs 上的这个线程](https://www.postgresql.org/message-id/flat/[e-mail]#[e-mail])，这是由于 _三个_ 原因：

1. 这取决于字典的行为。
2. 这本词典没有硬连线连接。
3. 因此，它还取决于当前的 `search_path` ，这很容易改变。

网络上的 [一些教程](https://django-orm.readthedocs.org/en/latest/orm-pg-fulltext.html) 指示只需将函数波动性更改为 `IMMUTABLE` 。这种蛮力方法可以在某些条件下破坏。

其他人建议一个 [简单的 `IMMUTABLE` 包装函数](https://www.postgresql.org/message-id/flat/[e-mail]#[e-mail])（就像我过去自己做的那样）。

是否 [使用两个参数](https://www.postgresql.org/docs/12/unaccent.html#id-1.11.7.52.7) `IMMUTABLE` 显式声明使用的字典存在一个持续的争论。阅读 [这里](https://www.postgresql.org/message-id/flat/[e-mail]#[e-mail]) 或 [这里](https://www.postgresql.org/message-id/flat/CAFj8pRDi0LRcmgyvd0ttC9J=[e-mail]#CAFj8pRDi0LRcmgyvd0ttC9J=[e-mail])。

另一种选择是这个模块具有 [IMMUTABLE `unaccent()` 由 Musicbrainz 提供的功能](https://github.com/metabrainz/postgresql-musicbrainz-unaccent)，在 Github 上提供。自己没有测试过。我想我想出了一个 _**更好的主意**_：

## 现在最好

这种方法 **比其他漂浮的解决方案更有效，也更安全**。

创建一个 `IMMUTABLE` SQL 包装函数，执行带有硬连接、模式限定函数和字典的双参数形式。

由于嵌套非不可变函数会禁用函数内联，因此它也基于声明为 `IMMUTABLE` 的 C 函数的副本（假）。它的 _唯一_ 目的是在 SQL 函数包装器中使用。不打算单独使用。

需要复杂性，因为无法在 C 函数的声明中硬连线字典。 （需要破解 C 代码本身。）SQL 包装函数执行此操作，并允许函数内联 _和_ 表达式索引。

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text) RETURNS text LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS ‘$libdir/unaccent’, ‘unaccent_dict’;

CREATE OR REPLACE FUNCTION public.f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS \(func\) SELECT public.immutable_unaccent(regdictionary ‘public.unaccent’, \(1) \)func$;

从 Postgres 9.5 或更早版本的两个函数中删除 `PARALLEL SAFE` 。

`public` 是您安装扩展的架构（ `public` 是默认值）。

显式类型声明 ( `regdictionary` ) 可防御恶意用户对函数的超载变体的假设攻击。

_之前我提倡基于unaccent模块自带的 `STABLE` 函数 `unaccent()` 的封装函数。那个禁用的 [函数内联](https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions)。这个版本的执行速度比我之前在这里的简单包装函数 **快十倍**。_

_这已经比在函数中添加 `SET search_path = public, pg_temp` 的第一个版本快两倍 \- 直到我发现字典也可以是模式限定的。 [仍然（Postgres 12）从文档中不太明显。](https://www.postgresql.org/docs/12/unaccent.html#id-1.11.7.52.7)_

**如果** 您缺乏创建 C 函数的必要权限，您将回到次优实现： `IMMUTABLE` 围绕 `STABLE` `unaccent()` 提供的函数的函数包装器模块：

CREATE OR REPLACE FUNCTION public.f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS \(func\) SELECT public.unaccent(‘public.unaccent’, \(1) -- schema-qualify function and dictionary \)func$;

最后，使查询 _快速_ 的 **表达式索引**：

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

请记住在对函数或字典进行任何更改后 **重新创建涉及此函数的索引**，例如不会重新创建索引的就地主要版本升级。最近的主要版本都有 `unaccent` 模块的更新。

调整查询以匹配索引（因此查询计划器将使用它）：

SELECT * FROM users WHERE f_unaccent(name) = f_unaccent(‘João’);

我们不需要运算符右侧的表达式中的函数。在那里，我们还可以直接提供像 `'Joao'` 这样的非重音字符串。

使用 **[表达式 index](https://www.postgresql.org/docs/current/indexes-expressional.html)** ，更快的函数不会转换为更快的查询。索引查找对预先计算的值进行操作，并且无论哪种方式都非常快。但是不使用索引的索引维护和查询有好处。而 [位图索引扫描](https://www.postgresql.org/docs/current/indexes-bitmap-scans.html) 等访问方法可能必须 _重新检查_ 堆中的值（主关系），这涉及执行底层函数。看：

- [带有位图索引扫描的查询计划中的“重新检查条件：”行](https://dba.stackexchange.com/q/106264/3684)

Postgres 10.3 / 9.6.8 等版本加强了客户端程序的安全性。您 _需要_ 对函数和字典名称进行模式限定，如在任何索引中使用时所示。看：

- [postgres 日志中的“文本搜索字典“unaccent”不存在”条目，据说是在自动分析期间](https://dba.stackexchange.com/questions/202490/text-search-dictionary-unaccent-does-not-exist-entries-in-postgres-log-supp?noredirect=1#comment421020_202490)

### 连字

在 Postgres **9.5 或更早版本** 中，必须手动扩展诸如 'Œ' 或 'ß' 之类的连字（如果需要），因为 `unaccent()` 总是替换 _一个_ 字母：

SELECT unaccent(‘Œ Æ œ æ ß’);

unaccent

E A e a S

在 Postgres **9.6** 中，您会喜欢 [这个不重音的更新](https://www.postgresql.org/message-id/flat/[e-mail]#[e-mail])：

> 扩展 `contrib/unaccent` 的标准 `unaccent.rules` 文件以处理 Unicode 已知的所有变音符号，并 **正确扩展连字**（Thomas Munro，Léonard Benedetti）

大胆强调我的。现在我们得到：

SELECT unaccent(‘Œ Æ œ æ ß’);

unaccent

OE AE oe ae ss

### 模式匹配

对于 **`LIKE`** 或 **`ILIKE`** 具有任意模式，将其与 PostgreSQL 9.1 或更高版本中的模块 [**`pg_trgm`**](https://www.postgresql.org/docs/current/pgtrgm.html) 结合使用。创建三元组 GIN（通常更可取）或 GIST 表达式索引。杜松子酒的例子：

CREATE INDEX users_unaccent_name_trgm_idx ON users USING gin (f_unaccent(name) gin_trgm_ops);

可用于以下查询：

SELECT * FROM users WHERE f_unaccent(name) LIKE (‘%’ || f_unaccent(‘João’) || ‘%’);

”`

GIN 和 GIST 索引比普通 B-tree 更昂贵（维护）：

• GiST和GIN索引的区别

对于左锚模式有更简单的解决方案。有关模式匹配和性能的更多信息：

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配

pg_trgm 还 为“相似性”（ % ）和“距离”（ <-> ）提供了有用的运算符。

Trigram 索引还支持带有 ~ 等的简单正则表达式。和 不区分大小写 的模式匹配 ILIKE ：

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

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我很确定 PostgreSQL 依赖于底层操作系统进行排序。它 确实 支持 创建新排序规则 和 自定义排序规则。不过，我不确定这对你来说有多少工作量。 （可能很多。）

原文由 Mike Sherrill ’Cat Recall‘ 发布，翻译遵循 CC BY-SA 3.0 许可协议