如何在 PostgreSQL 中实现不区分重音的搜索？

PostgreSQL 的不区分重音的搜索方法

与某些数据库（如 Microsoft SQL Server）不同，PostgreSQL 本身并不支持不区分重音的排序规则。 虽然 PostgreSQL 12 引入了非确定性 ICU 排序规则，不区分大小写和重音，但这些排序规则会带来性能权衡和操作限制。

PostgreSQL 中不区分重音的查询策略

有几种方法可以在 PostgreSQL 中实现不区分重音的搜索：

1。 unaccent 模块：

该模块提供unaccent()函数，从字符串中删除重音符号。 这允许查询如下：

<code class="language-sql">SELECT * FROM users WHERE unaccent(name) = unaccent('João');</code>

但是，unaccent() 不是 IMMUTABLE，这会阻止其在表达式索引中使用，并且它不会扩展连字（例如“Œ”）。

2。优化的 C 函数包装器：

为了解决 unaccent() 的限制，更有效的解决方案涉及创建 IMMUTABLE C 函数包装器：

<code class="language-sql">CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN public.immutable_unaccent(regdictionary 'public.unaccent', );</code>

这允许创建表达式索引：

<code class="language-sql">CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));</code>

然后使用包装函数进行查询：

<code class="language-sql">SELECT * FROM users WHERE f_unaccent(name) = f_unaccent('João');</code>

3。利用 pg_trgm 进行模式匹配和连字：

为了更灵活的模式匹配和连字处理，带有三元组索引的 pg_trgm 模块提供了强大的解决方案。 trigram GIN 索引支持不区分大小写的搜索和相似性检测：

<code class="language-sql">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') || '%');</code>

请注意，pg_trgm 索引比标准 B 树索引更耗费资源。

选择最佳方法取决于应用程序的具体需求，平衡查询性能与索引维护成本以及连字处理的要求。

以上是如何在 PostgreSQL 中实现不区分重音的搜索？的详细内容。更多信息请关注PHP中文网其他相关文章！