F.7. citext · PostgreSQL 中文文档 9.3

# 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 `<[david@kineticode.com](mailto:david@kineticode.com)>` 灵感来源于Donald Fraser的最初的`citext`模块。