PostgreSQL插件之citext 大小写不敏感
我们知道，PG对于大小写是敏感的，那有什么办法不敏感呢？
可以使用lower函数，例如：
|
1
|
SELECT
*
FROM
table1
WHERE
lower
(col) =
LOWER
(?);
|
但是存在一些问题：
它让你的 SQL 语句冗长，并且你必须总是要记住在列和查询值上使用
lower。它不会使用一个索引，除非你使用
lower创建一个函数索引。如果你声明一个列为
UNIQUE或PRIMARY KEY，隐式生成的索引是大小写敏感的。因此，它对于大小写不敏感的搜索是没有用处的，并且它不会强制大小写不敏感的唯一性。
测试了一番：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
postgres=#
create
extension citext;
CREATE
EXTENSION
postgres=#
CREATE
TABLE
users (
postgres(# nick CITEXT
PRIMARY
KEY
,
postgres(# pass TEXT
NOT
NULL
postgres(# );
CREATE
TABLE
postgres=#
INSERT
INTO
users
VALUES
(
'larry'
, sha256(random()::text::bytea) );
ndom()::text::bytea) );
INSERT
INTO
users
VALUES
(
'Bjørn'
, sha256(random()::text::bytea) );
INSERT
0 1
postgres=#
INSERT
INTO
users
VALUES
(
'Tom'
, sha256(random()::text::bytea) );
INSERT
0 1
postgres=#
INSERT
INTO
users
VALUES
(
'Damian'
, sha256(random()::text::bytea) );
INSERT
0 1
postgres=#
INSERT
INTO
users
VALUES
(
'NEAL'
, sha256(random()::text::bytea) );
INSERT
0 1
postgres=#
INSERT
INTO
users
VALUES
(
'Bjørn'
, sha256(random()::text::bytea) );
INSERT
0 1
postgres=#
SELECT
*
FROM
users
WHERE
nick =
'Larry'
;
nick | pass
-------+--------------------------------------------------------------------
larry | \x479d5f5b2834ea4ee06c545965a828e0933365f3b35ad7d8bd93bdc996636627
(1 row)
|
即使nick列被设置为larry而查询是Larry，SELECT语句也将只返回一个元组。
使用了CITEXT类型后，PG的自带函数将以不敏感的方式匹配，例如regexp_*， replace等。
限制
citext的大小写折叠行为取决于你的数据库的LC_CTYPE设置。因此它如何比较值是在数据库被创建时决定的。在 Unicode 标准定义的术语中没有真正的大小写不敏感。实际上，它的含义是，只要你对你的排序规则满意，你就应该对citext的比较满意。但是如果在你的数据库中存储有不同语言的数据，当排序规则是用于一种语言时，另一种语言的用户可能会发现他们的查询结果并不是所期待的。自PostgreSQL 9.1 其，你可以为
citext列或数据值附加一个COLLATE说明。当前，在比较大小写折叠过的字符串时，citext操作符将尊重一种非默认的COLLATE说明，但是最初到小写形式的折叠是根据数据库的LC_CTYPE设置完成的（就是说，尽管给出了COLLATE "default"）。这可能在未来的发行中被改变，这样两步都能遵循输入的COLLATE说明。citext的效率不如text，因为操作符函数和 B 树比较函数必须创建数据的拷贝并且将它转换为小写形式来进行比较。不过，它比使用lower进行大小写不敏感的匹配的效率要略高。如果你在某些环境下需要以大小写敏感的方式比较数据并且在另一些环境下需要以大小写不敏感的方式比较数据，
citext就帮不上什么忙。标准的答案是使用text类型并且在你需要以大小写不敏感的方式比较时手工使用lower函数。如果大小写不敏感的比较需求不频繁，这会工作得不错。如果你大部分时间需要大小写不敏感的行为，考虑将数据存储为citext并且在进行大小写敏感比较时显式地将列造型为text。不管在那种情况下，你都需要两个索引来让两种类型的搜索更快。包含
citext操作符的模式必须在当前的search_path（通常是public）中。如果它不在搜索路径中，普通的大小写敏感的text操作符将会取而代之。
参考：http://www.postgres.cn/docs/11/citext.html
- 点赞
- 收藏
- 关注作者
评论（0）