PostgreSQL插件之citext 大小写不敏感

举报
大象数据库 发表于 2020/09/02 16:33:18 2020/09/02
【摘要】 我们知道,PG对于大小写是敏感的,那有什么办法不敏感呢?可以使用lower函数,例如:1SELECT * FROM table1 WHERE lower(col) = LOWER(?);但是存在一些问题:它让你的 SQL 语句冗长,并且你必须总是要记住在列和查询值上使用lower。它不会使用一个索引,除非你使用lower创建一个函数索引。如果你声明一个列为UNIQUE或PRIMARY KEY...

我们知道,PG对于大小写是敏感的,那有什么办法不敏感呢?

可以使用lower函数,例如:

1
SELECT  FROM  table1  WHERE  lower (col) =  LOWER (?);

但是存在一些问题:

  • 它让你的 SQL 语句冗长,并且你必须总是要记住在列和查询值上使用lower

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

  • 如果你声明一个列为UNIQUEPRIMARY 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而查询是LarrySELECT语句也将只返回一个元组。

使用了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


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。