[译] POSTGRESQL 中不区分大小写的模式匹配

原文地址：CASE-INSENSITIVE PATTERN MATCHING IN POSTGRESQL
原文作者：Laurenz Albe

不区分大小写的搜索是一个非常需要的功能，部分原因是为了保持与Microsoft SQL Server的兼容性。该问题有多种解决方案，其中之一是使用不区分大小写的 ICU 排序规则。这就像一个魅力，除非你想执行模式匹配。因此，让我们仔细看看这个问题和可能的解决方案。

不区分大小写搜索的替代方法

在 PostgreSQL 中存在三种不区分大小写搜索的已知解决方案：

lower()用or显式转换upper()

使用此方法的查询如下所示：

SELECT id, col FROM tab
WHERE lower(col) = lower('search string');

这可以通过在 上的 B-tree 索引来快速完成lower(col)，但有两个缺点：

该解决方案是在应用程序级别实现的，也就是说，您必须为不区分大小写的搜索定制查询
如果数据库列包含长值，则必须将整个值转换为小写，即使只需要比较几个字符，也会导致性能不佳

使用citext扩展

该扩展citext提供了一种数据类型citext，代表“不区分大小写text”。该表定义为

CREATE TABLE tab (
   col citext,
   ...
);

查询很简单

SELECT id, col FROM tab
WHERE col = 'search string';

这既简单又方便，但也有一些缺点：

• 没有数据类型civarchar，因此您只能使用检查约束来实现它
• 较长值的性能也可能很差，因为在比较值之前citext内部调用lower(col COLLATE “default”)
• 正则表达式匹配不区分大小写，必须~*显式使用不区分大小写的运算符

使用不区分大小写的 ICU 排序规则

如果您使用的是 PostgreSQL v12 或更高版本，并且配置了 PostgreSQL --with-icu，您可以定义一个不区分大小写的排序规则，如下所示：

CREATE COLLATION english_ci (
   PROVIDER = icu,
   -- 'en-US@colStrength=secondary' for old ICU versions
   LOCALE = 'en-US-u-ks-level2',
   DETERMINISTIC = FALSE
);

该表将被定义为

CREATE TABLE tab (
   col text COLLATE english_ci,
   ...
);

并且查询再次很简单：

SELECT id, col FROM tab
WHERE col = 'search string';

有关 ICU 排序规则的更多详细信息，请阅读我关于该主题的文章

（地址：https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/）

模式匹配和不区分大小写的排序规则的问题

不区分大小写的排序规则是该问题最优雅的解决方案。这种方法有两个缺点：

• 尽管 PostgreSQL v15 允许您使用 ICU 排序规则作为默认数据库排序规则，但这不会扩展到不区分大小写的排序规则，因此您仍然必须为每个列定义显式指定排序规则
• 您不能将不区分大小写的排序规则与模式匹配一​​起使用

第一个问题只是一个烦恼，但第二个问题需要更多的思考。以下是展示该问题的两个示例：

SELECT id, long FROM perf_coll WHERE long LIKE 'abcd%';
ERROR:  nondeterministic collations are not supported for LIKE
 
SELECT id, long FROM perf_coll WHERE long ~ '^abcd';
ERROR:  nondeterministic collations are not supported for regular expression

为什么不支持？

德国足球的困境

ICU 文档详细说明了为什么正确的不区分大小写的模式匹配很困难。一个很好的例子是德语字母“ß”，它传统上没有大写等效项。因此，使用良好的德语排序规则（来自 GNU C 库的排序规则在这方面不好），您将得到如下结果：

SELECT upper('Fußball' COLLATE "de-DE-x-icu");
 
  upper  
══════════
 FUSSBALL
(1 row)

现在，在不区分大小写的排序规则中，以下查询的正确结果是什么？

SELECT 'Fußball' LIKE 'FUS%';

你可以争辩说它应该是TRUE，因为那是你会得到的upper(‘Fußball’) LIKE ‘FUS%’。另一方面，

SELECT lower('FUSSBALL' COLLATE "de-DE-x-icu");
 
  lower  
══════════
 fussball
(1 row)

所以你也可以争辩结果应该是FALSE. 为简单起见，ICU 库与第二种解决方案一起使用。这两种解决方案都很难在 PostgreSQL 中实现，所以我们暂时放弃了这个问题。比较邮件列表中的此报价：

ICU 还提供正则表达式匹配，但不支持排序规则，因为
基于字符的模式不能很好地与排序规则的概念配合使用。
关于潜在的排序感知 LIKE，它看起来很难实现，
因为目前在 like_match.c 中使用的算法似乎纯粹
基于字符。AFAICS 无法将对 usearch_*
函数的调用插入其中，它需要从头开始单独重新设计。

除了符号学

正确的德语没有普遍接受的权威。例如，讲德语的瑞士人在 20 世纪废除了 ß。另一方面，“Rat für deutsche Rechtschreibung”（德语拼写委员会）在 2017 年引入了大写字母 ẞ，但这种简化问题的尝试却被德语世界广泛忽视。简化德语，多么离谱的想法！

最终结果比以前更令人困惑：

SELECT upper('ß' COLLATE "de-DE-x-icu"),
       lower('ẞ' COLLATE "de-DE-x-icu");
 
 upper │ lower
═══════╪═══════
 SS    │ ß
(1 row)

一种不区分大小写的模式匹配的解决方案

我们需要一个切实可行的解决方案。当然，我们可以使用lower()或强制转换为citext，但这会重新引入长字符串的性能问题。因此，我们通过显式使用不同的排序规则来规避问题。出于性能原因，并且为了避免德国足球的脑筋急转弯，我们强制转换为二进制排序规则，它会逐个字符地比较单词：

SELECT id, col FROM tab
WHERE col COLLATE "C" ILIKE 'search%pattern';
 
SELECT id, col FROM tab
WHERE col COLLATE "C" ~* '^search.*pattern';

请注意，要使其按预期工作，我们必须使用不区分大小写的版本LIKE和正则表达式匹配 operator ~。

当然，这个解决方案并不像我们希望的那么简单：同样，应用程序必须编写明确指定不区分大小写处理的查询。另一个困难是性能：虽然 B-tree 索引可以支持区分大小写的模式匹配，但不区分大小写的模式匹配需要三元索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX tab_col_pattern_idx ON tab USING gin
   (col gin_trgm_ops);

这样的索引可以加快上述两个语句的速度，但它会变大并且比普通的 B-tree 索引更新慢。

不区分大小写比较的性能测试

在这个测试中，我使用了一千万行的表和一个包含 320 个字符的随机字符串的列。列上没有索引。在表中perf_citext，列定义为citext。其中perf_lower，数据类型为text，比较使用lower()。表与上面定义的排序规则一起perf_coll使用。使用的数据库是带有美国英语排序规则的 PostgreSQL v15。所有表都缓存在共享缓冲区中。textenglish_ci

在这个测试中，citext领先，但不区分大小写的排序规则是一个不错的亚军。使用lower()效果不佳。

结论

在 PostgreSQL 中，不区分大小写的排序规则没有实现模式匹配，在这种情况下正确的行为是什么是一个难题。通过显式使用不区分大小写的模式匹配运算符和二进制排序规则，我们有一个不错的解决方法，但情况肯定远非完美。

如果您对有关 PostgreSQL 主题的更多博客感兴趣，请查看我的博客，了解如何取消长时间运行的查询。