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

Question

我正在寻找一种性能良好的方法来支持不区分大小写、不区分重音的搜索。到目前为止，我们使用MSSql server在这方面没有问题，在Oracle上我们必须使用OracleText，现在我们在PostgreSQL上需要它。

我找到了这篇关于它的帖子，但我们需要将它与不区分大小写结合起来。我们还需要使用索引，否则性能可能会受到影响。有没有关于大型数据库的最佳方法的实际经验？

Answer 1

默认情况下，FTS自然不区分大小写，

将标记转换为词素。词素是一个字符串，就像一个标记一样，但它已被规范化，以便使同一单词的不同形式变得相似。例如，规范化几乎总是包括将大写字母折叠成小写字母，并且经常涉及到后缀的删除（例如英语中的s或es）。

您可以使用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)

另请参见

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

Answer 2

如果您需要“结合不区分大小写”，根据您的具体要求，有许多选项。

也许最简单的方法是使表达式索引不区分大小写。

基于引用答案中列出的函数f_unaccent（）：

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

CREATE INDEX users_lower_unaccent_name_idx ON users(lower(f_unaccent(name)));

然后：

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

或者（特别是如果您需要进行模糊模式匹配），您可以使用附加模块提供的三角图索引，该模块基于上述功能构建，还支持ILIKE。细节：

• LOWER LIKE vs iLIKE

我在参考答案中添加了一条注释。

或者您可以使用附加模块citext（但我宁愿避免）：

• 可延迟、不区分大小写的唯一约束