PostgreSQL 教程

Appearance

F.10. citext — 不区分大小写的字符字符串类型# ​

F.10.1. 基本原理
F.10.2. 如何使用
F.10.3. 字符串比较行为
F.10.4. 限制
F.10.5. 作者

citext模块提供了一种不区分大小写的字符字符串类型citext。从本质上讲，它在比较值时内部调用lower。除此之外，它的行为几乎与text完全相同。

提示

考虑使用非确定性排序规则（请参见第 24.2.2.4 节），而不是此模块。它们可用于不区分大小写的比较、不区分重音的比较以及其他组合，并且可以正确处理更多 Unicode 特殊情况。

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上拥有CREATE权限的非超级用户安装。

F.10.1. 基本原理# ​

在PostgreSQL中进行不区分大小写匹配的标准方法是在比较值时使用lower函数，例如

SELECT * FROM tab WHERE lower(col) = LOWER(?);

这种方法效果不错，但有一些缺点

  • 它会使 SQL 语句变得冗长，并且你必须始终记住对列和查询值都使用 lower。

  • 它不会使用索引，除非你使用 lower 创建一个函数索引。

  • 如果你将一个列声明为 UNIQUE 或 PRIMARY KEY，则隐式生成的索引是区分大小写的。因此，它对不区分大小写的搜索毫无用处，并且它不会强制不区分大小写地保证唯一性。

数据类型citext允许您消除 SQL 查询中的lower调用，并允许主键不区分大小写。citext具有区域感知性，就像text一样，这意味着大小写字符的匹配取决于数据库LC_CTYPE设置的规则。同样，此行为与在查询中使用lower相同。但是，由于数据类型会透明地执行此操作，因此您不必记住在查询中执行任何特殊操作。

F.10.2. 如何使用# ​

以下是一个简单的用法示例

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );

SELECT * FROM users WHERE nick = 'Larry';

SELECT语句将返回一个元组，即使nick列设置为larry而查询为Larry。

F.10.3. 字符串比较行为# ​

citext通过将每个字符串转换为小写（就像调用lower一样）然后正常比较结果来执行比较。因此，例如，如果lower为两个字符串生成相同的结果，则这两个字符串被认为是相等的。

为了尽可能地模拟不区分大小写的排序规则，有许多字符串处理运算符和函数的citext特定版本。因此，例如，当应用于citext时，正则表达式运算符~和~*表现出相同的行为：它们都匹配不区分大小写。!~和!~*也是如此，LIKE运算符~~和~~*以及!~~和!~~*也是如此。如果您想匹配区分大小写，可以将运算符的参数强制转换为text。

类似地，如果其参数为citext，则以下所有函数都执行不区分大小写的匹配

  • regexp_match()

  • regexp_matches()

  • regexp_replace()

  • regexp_split_to_array()

  • regexp_split_to_table()

  • replace()

  • split_part()

  • strpos()

  • translate()

对于 regexp 函数，如果您想匹配区分大小写，可以指定“c”标志以强制进行区分大小写的匹配。否则，如果您希望区分大小写，则必须在使用其中一个函数之前将其强制转换为text。

F.10.4. 限制# ​

  • citext 的大小写折叠行为取决于数据库的 LC_CTYPE 设置。因此，它如何比较值是在创建数据库时确定的。它并不是真正地不区分大小写，按照 Unicode 标准定义的术语来说。实际上，这意味着，只要你对你的排序规则感到满意，你应该对 citext 的比较感到满意。但是，如果你的数据库中存储了不同语言的数据，则一种语言的用户可能会发现，如果排序规则是针对另一种语言的，则他们的查询结果与预期不符。

  • 从 PostgreSQL 9.1 开始，你可以将 COLLATE 规范附加到 citext 列或数据值。目前，citext 运算符将在比较大小写折叠的字符串时遵守非默认的 COLLATE 规范，但最初折叠为小写始终根据数据库的 LC_CTYPE 设置进行（即，好像给出了 COLLATE "default"）。这可能会在未来版本中更改，以便这两个步骤都遵循输入 COLLATE 规范。

  • citext 不如 text 高效，因为运算符函数和 B 树比较函数必须复制数据并将其转换为小写以进行比较。此外，只有 text 可以支持 B 树重复数据删除。但是，citext 比使用 lower 来获取不区分大小写的匹配稍微高效一些。

  • 如果你需要在某些上下文中以区分大小写的方式比较数据，而在其他上下文中以不区分大小写的方式比较数据，citext 帮不了多少忙。标准答案是使用 text 类型，并在需要不区分大小写地比较时手动使用 lower 函数；如果只需要不频繁地进行不区分大小写的比较，这样做就可以了。如果你大多数时候需要不区分大小写的行为，而很少需要区分大小写的行为，请考虑将数据存储为 citext，并在需要区分大小写比较时将列显式转换为 text。在任何一种情况下，如果你希望两种类型的搜索都很快，你将需要两个索引。

  • 包含 citext 运算符的模式必须在当前 search_path（通常为 public）中；如果不是，将调用正常的区分大小写的 text 运算符。

  • 将字符串转换为小写以进行比较的方法无法正确处理某些 Unicode 特殊情况，例如当一个大写字母有两个小写字母等效项时。出于这个原因，Unicode 区分了 大小写映射 和 大小写折叠。使用非确定性排序规则代替 citext 来正确处理这一点。

F.10.5. 作者# ​

David E. Wheeler<[[email protected]](/cdn-cgi/l/email-protection#345055425d50745f5d5a51405d575b50511a575b59)>

灵感来自 Donald Fraser 创作的原始citext模块。