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

Question

在MicrosoftSQLServer中，可以指定重音不敏感排序规则（对于数据库、表或列），这意味着可以使用以下查询

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

查找具有Joao名称的行。

我知道可以使用uncent\u string contrib函数从PostgreSQL中的字符串中去除重音，但我想知道PostgreSQL是否支持这些“不区分重音”的排序规则，以便上面的选择可以工作。

Answer 1

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

Answer 2

PostgreSQL不支持这样的排序规则（不区分重音或不区分重音），因为除非二进制相等，否则任何比较都不能返回相等。这是因为在内部，它会给哈希索引之类的东西带来很多复杂性。因此，严格意义上的排序规则只影响排序，而不影响相等。

对于FTS，您可以使用uncent定义自己的字典，

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

然后可以使用函数索引对其进行索引，

-- Just some sample data...
CREATE TABLE myTable ( myCol )
  AS VALUES ('fóó bar baz'),('qux quz');

-- No index required, but feel free to create one
CREATE INDEX ON myTable
  USING GIST (to_tsvector('mydict', myCol));

您现在可以非常简单地查询它

SELECT *
FROM myTable
WHERE to_tsvector('mydict', myCol) @@ 'foo & bar'

    mycol    
-------------
 fóó bar baz
(1 row)

又见

• 在字段上创建不区分大小写且不区分重音/变音符号的搜索

unaccent模块也可以在没有FTS集成的情况下自行使用，请查看Erwin的答案

Answer 3

使用uncent模块实现这一点，这与您所链接的内容完全不同。

uncent是一种文本搜索词典，它可以从词素中删除重音符号（变音符号）。

每个数据库安装一次：

CREATE EXTENSION unaccent;

如果出现如下错误：

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

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

• 在PostgreSQL上创建非插入扩展时出错

除其他外，它提供了您可以在示例中使用的函数unaccent（）（其中like似乎不需要）。

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

若要对此类查询使用索引，请在表达式上创建索引。然而，Postgres只接受索引的不可变函数。如果函数可以为相同的输入返回不同的结果，则索引可能会自动中断。

不幸的是，uncent（）只是稳定的，而不是不变的。根据pgsql bugs上的此线程，这是由三个原因造成的：

1. 这取决于词典的行为
2. 此词典没有硬连线连接
3. 因此，它还取决于当前的搜索路径，该路径很容易更改

web上的一些教程指示只将函数volatility更改为不可变。这种蛮力方法在某些情况下可能会失效。

其他人建议使用一个简单的IMMUTABLE包装函数（就像我过去自己做的那样）。

是否让具有两个参数的变量不可变（明确声明使用的字典）一直存在争议。阅读此处或此处。

另一种选择是这个模块，该模块由Musicbrainz提供的一个不可变的uncent（）函数，由Github提供。我自己还没有测试过。我想我想出了一个更好的主意：

与其他解决方案相比，这种方法更有效，也更安全
创建一个不可变的SQL包装函数，使用硬连线的模式限定函数和字典执行双参数表单。

由于嵌套非不可变函数会禁用函数内联，因此它基于C函数的副本，（假）声明的IMMUTABLE也是如此。它的唯一目的是用于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或更早版本的两个函数中删除并行安全。

public是安装扩展的架构（public是默认设置）。

显式类型声明（regdictionary）可以防止恶意用户通过重载函数变体进行假设性攻击。

_{以前，我提倡基于unaccent模块附带的STABLE函数unaccent（）的包装函数。那个禁用的函数内联。这个版本的执行速度比我之前在这里使用的简单包装函数快十倍。
这已经是第一个版本的两倍了，它添加了SETsearch_path=public，pg_temp到函数中——直到我发现字典也可以是模式限定的。尽管如此（Postgres 12）从留档来看并不太明显。}

如果您缺乏创建C函数所需的权限，那么就回到了第二个最佳实现：围绕模块提供的稳定函数的不可变函数包装器：

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

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

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”。

使用表达式索引时，更快的函数不能转换为更快的查询。它基于预先计算的值进行操作，并且已经非常快了。但索引维护和查询不使用索引的好处。

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

• postgres日志中的“文本搜索词典“uncent”不存在”条目，可能是在自动分析期间

在Postgres 9.5或更早版本的连字符中，如“”或“”必须手动展开（如果您需要的话），因为unaccent（）总是替换单个字母：

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
E A e a S

您会喜欢Postgres 9.6中的uncent更新：

扩展contrib/uncent的标准。规则文件来处理Unicode已知的所有变音符号，并正确扩展连字（Thomas Munro，Léonard Benedetti）

粗体强调我的。现在我们得到：

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
OE AE oe ae ss

对于具有任意模式的LIKE或ILIKE，请将其与PostgreSQL 9.1或更高版本中的模块pg\u trgm结合使用。创建三角图GIN（通常更可取）或GIST表达式索引。GIN示例：

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索引的维护成本高于普通btree：

• GiST和GIN索引之间的差异

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

• 在PostgreSQL中与赞、SIMILAR TO或正则表达式匹配的模式

pg\u trgm还为“相似性”（%）和“距离”（

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

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