PostgreSQL：不区分大小写的字符串比较

发布时间：2020-12-13 16:45:03 所属栏目：百科来源：网络整理

导读：PostgreSQL有一个简单的忽略大小写比较？我要替换： SELECT id,user_name FROM users WHERE lower(email) IN (lower('adamB@a.com'),lower('eveA@b.com')); 有类似的东西： SELECT id,user_name FROM users WHERE email IGNORE_CASE_IN ('adamB@a.com','eve

PostgreSQL有一个简单的忽略大小写比较？

我要替换：

SELECT id,user_name 
    FROM users 
        WHERE lower(email) IN (lower('adamB@a.com'),lower('eveA@b.com'));

有类似的东西：

SELECT id,user_name 
    FROM users 
        WHERE email IGNORE_CASE_IN ('adamB@a.com','eveA@b.com');

编辑：like和类似的运算符工作在单个值(例如像“adamB@a.com”)，而不是集。

有任何想法吗？

亚当

首先，什么不做，不使用ilike …

create table y
(
id serial not null,email text not null unique
);

insert into y(email) 
values('iSteve.jobs@apple.com'),('linus.Torvalds@linUX.com');
insert into y(email) 
select n from generate_series(1,1000) as i(n);
create index ix_y on y(email);

explain select * from y 
where email ilike 
    ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']);

执行计划：

memdb=# explain select * from y where email ilike ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Seq Scan on y  (cost=0.00..17.52 rows=1 width=7)
   Filter: (email ~~* ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::text[]))
(2 rows)

这是你创建一个索引的低级表达式…

create function lower(t text[]) returns text[]
as
$$
select lower($1::text)::text[]
$$ language sql;

create unique index ix_y_2 on y(lower(email));

explain select * from y 
where lower(email) = 
    ANY(lower(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']));

…其中正确使用索引：

memdb=# explain select * from y where lower(email) = ANY(lower(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']));
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on y  (cost=22.60..27.98 rows=10 width=7)
   Recheck Cond: (lower(email) = ANY ((lower(('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::text[])::text))::text[]))
   ->  Bitmap Index Scan on ix_y_2  (cost=0.00..22.60 rows=10 width=0)
         Index Cond: (lower(email) = ANY ((lower(('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::text[])::text))::text[]))
(4 rows)

或者使用citext数据类型…

create table x
(
id serial not null,email citext not null unique
);

insert into x(email) 
values('iSteve.jobs@apple.com'),('linus.Torvalds@linUX.com');
insert into x(email) 
select n from generate_series(1,1000) as i(n);
create index ix_x on x(email);

explain select * from x 
where email = 
ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']::citext[]);

…即使您不在表达式上创建索引(例如，在yyy(下(字段))上创建索引zzz，也可以正确使用索引)：

memdb=# explain select * from x where email = ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']::citext[]);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on x  (cost=8.52..12.75 rows=2 width=7)
   Recheck Cond: (email = ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::citext[]))
   ->  Bitmap Index Scan on ix_x  (cost=0.00..8.52 rows=2 width=0)
         Index Cond: (email = ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::citext[]))
(4 rows)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!