F.7. citext

citext模块提供不区分大小写字符串类型,citext。 从本质上讲,当比较值时,它内部调用lower。 否则,它的操作很像text

F.7.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.7.2. 如何使用它

这是用法的一个简单例子:

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

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

SELECT * FROM users WHERE nick = 'Larry';

SELECT语句将返回一个元组, 尽管nick列被设置为larry, 并且查询为Larry

F.7.3. 字符串比较操作

citext通过转换每个字符串到小写执行比较 (尽管调用lower) 并且然后通常比较结果。因此,比如, 考虑两个字符串相等,如果 lower为了它们可能产生相同结果。

为了尽可能地模拟不区分大小写排序规则, 有一些字符串处理操作符和函数的citext特定版本。 所以,比如,当应用于citext:他们不区分大小写匹配, 正则表达式运算符~~*表现出相同操作。 对于!~ and !~*以及 LIKE运算符~~~~*!~~!~~*同样是真,如果你想要匹配 大小写敏感,你可以投射运算符的参数给text

类似地,如果它们参数是citext,所有下面的函数执行不区分大小写匹配:

  • regexp_replace()

  • regexp_split_to_array()

  • regexp_split_to_table()

  • replace()

  • split_part()

  • strpos()

  • translate()

对于正则表达式函数, 如果你想要匹配大小写敏感, 你可以声明"c"标记以 强迫大小写匹配。否则,如果你想要大小写敏感操作, 你必须在使用这些函数之一前投射到text

F.7.4. 限制

  • citext的折叠操作依赖于 你的数据库的LC_CTYPE设置。当创建数据库时, 决定如何比较值。通过Unicode标准定义 的术语中不是真的大小写不敏感。 实际上,这意味着, 只要你对你的排序规则满意, 你应该对citext的比较感到满意。 但是如果你有数据以不同语言存储在数据库中, 如果排序规则为另外一种语言, 那么一种语言的用户可能发现查询结果不如预期。

  • 作为PostgreSQL 9.1, 你可以附属COLLATE规范到citext 列或者数据值。 当比较折叠字符串时, citext运算符将接受非缺省COLLATE规范, 但小写的起初折叠 总是按照数据库的LC_CTYPE设置被执行 (即,即使给定COLLATE "default" )。 这可能在未来版本中被改变,因此这两个步骤遵循输入COLLATE 规范。

  • citext不像text一样有效, 因为运算符函数和B树比较函数必须开始数据拷贝 ,并且为了比较将它转换为小写。然而, 它比起使用lower获取大小写不敏感匹配 更加有效。

  • 如果你需要数据比较某些情况中 大小写敏感和其他情况中大小写不敏感, citext没有太大帮助。 当你需要不区分大小写比较时, 标准答案是使用text类型 并且手动使用lower函数。 如果很少需要不区分大小写比较, 那么它执行正确。 如果你需要不区分大小写行为大多数时间并且很少不区分大小写, 当你想要区分大小写比较时, 考虑存储数据为citext 并且明确投射该列到text。 如果你想要快速搜索的两个类型, 在这两种情况下,你将需要两个索引。

  • 包含citext运算符的模式 必须在当前的search_path (通常public)中; 如果不是,相反调用正常的大小写敏感text运算符。

F.7.5. 作者

David E. Wheeler <[[email protected]](mailto:[email protected])>

灵感来源于Donald Fraser的最初的citext模块。