PostgreSQL查询中的对象名称、数据内容大小写敏感问题

PostgreSQL查询中的对象名称、数据内容大小写敏感问题

MySQL数据库中,查询数据是不区分大小写的,下面2条语句,获得的数据一样

SELECT username, email FROM auth_user WHERE username = 'admin' LIMIT 10;
SELECT username, email FROM auth_user WHERE username = 'Admin' LIMIT 10;

但是,PostgreSQL区分大小写,这样有时候就不方便了。那么,PostgreSQL查询如何做到忽略大小写?下面总结一些常见的方法:

一、使用 LOWER/UPPER 函数

使用LOWER/UPPER函数,把数据和条件值都转为小写或者大写,但是这样做索引会失效,如果是大数据量的情况下,性能会大幅下降

1、先做普通的查询,做为基准参考

SELECT username, email FROM auth_user WHERE username = 'Admin' LIMIT 10;
 username | email 
----------+-------------------
 Admin | 123@123.com
(1 row)
​
Time: 0.491 ms

2、再看看使用LOWER函数的情况

SELECT username, email FROM auth_user WHERE LOWER(username) = LOWER('Admin') LIMIT 10;
 username | email 
----------+-------------------
 Admin | 123@123.com
 admin | admin@123.org
(2 rows)
​
Time: 820.651 ms

结论:太慢了,慢了1600倍!!!

3、分析下索引的使用情况

EXPLAIN ANALYZE SELECT username, email FROM auth_user WHERE username = 'Admin' LIMIT 10;
 QUERY PLAN 
----------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.43..8.45 rows=1 width=28) (actual time=0.091..0.092 rows=1 loops=1)
 -> Index Scan using ix_auth_user_username on auth_user (cost=0.43..8.45 rows=1 width=28) (actual time=0.089..0.090 rows=1 loops=1)
 Index Cond: ((username)::text = 'Admin'::text)
 Planning time: 0.135 ms
 Execution time: 0.125 ms
(5 rows)

EXPLAIN ANALYZE SELECT username, email FROM auth_user WHERE LOWER(username) = LOWER('Admin') LIMIT 10;
 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..101.00 rows=10 width=28) (actual time=7.422..835.354 rows=2 loops=1)
 -> Seq Scan on auth_user (cost=0.00..84397.18 rows=8356 width=28) (actual time=7.420..835.349 rows=2 loops=1)
 Filter: (lower((username)::text) = 'admin'::text)
 Rows Removed by Filter: 1583840
 Planning time: 0.142 ms
 Execution time: 835.381 ms
(6 rows)

结论:可以看到,使用LOWER/UPPER函数后,索引失效了

二、使用ILIKE

1、看看效果

SELECT username, email FROM auth_user WHERE username ILIKE 'Admin' LIMIT 10;
 username | email 
----------+-------------------
 Admin | 123@123.com
 admin | admin@123.org
(2 rows)
​
Time: 1227.317 ms

结论:看起来比使用 LOWER/UPPER 还慢!!!

SELECT username, email FROM auth_user WHERE username ILIKE '%Admin%' LIMIT 10;
 username | email 
----------------+---------------------
 Admin | 17@q1.com
 admin19880211 | 114@q2.com
 adminis啊 | 1448@q1.com
 superadmins | 132@1631.com
 admini123 | 196@qq.com
 usadmin mj | 0037@163.com
 admin | 1236@q1.com
 Adminbao | 19218@12.com
 wfadmin | wf12@sa.com
 adminpang | dms@133.com
(10 rows)
​
Time: 53.805 ms

结论:模糊查询,速度还行

2、看下索引使用情况

EXPLAIN ANALYZE SELECT username, email FROM auth_user WHERE username LIKE 'Admin' limit 10;
 QUERY PLAN 
----------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.43..8.45 rows=1 width=28) (actual time=0.033..0.034 rows=1 loops=1)
 -> Index Scan using ix_auth_user_username on auth_user (cost=0.43..8.45 rows=1 width=28) (actual time=0.032..0.033 rows=1 loops=1)
 Index Cond: ((username)::text = 'Admin'::text)
 Filter: ((username)::text ~~ 'Admin'::text)
 Planning time: 0.075 ms
 Execution time: 0.051 ms
(6 rows)
​
Time: 0.510 ms

结论:LIKE是使用索引的

EXPLAIN ANALYZE SELECT username, email FROM auth_user WHERE username ILIKE 'Admin' LIMIT 10;
 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..4803.53 rows=10 width=28) (actual time=7.172..1340.952 rows=2 loops=1)
 -> Seq Scan on auth_user (cost=0.00..80218.99 rows=167 width=28) (actual time=7.172..1340.949 rows=2 loops=1)
 Filter: ((username)::text ~~* 'Admin'::text)
 Rows Removed by Filter: 1583850
 Planning time: 0.143 ms
 Execution time: 1340.984 ms
(6 rows)

结论:同样的 ILIKE 查询索引失效了

三、使用正则表达式

SELECT username, email FROM auth_user WHERE username ~* '^admin$' LIMIT 10;
 username | email 
----------+-------------------
 Admin | 1933088987@qq.com
 admin | admin@infopub.org
(2 rows)
​
Time: 761.884 ms

SELECT username, email FROM auth_user WHERE username ~* 'admin' LIMIT 10;
 username | email 
----------------+---------------------
 Admin | 17@q1.com
 admin19880211 | 114@q2.com
 adminis啊 | 1448@q1.com
 superadmins | 132@1631.com
 admini123 | 196@qq.com
 usadmin mj | 0037@163.com
 admin | 1236@q1.com
 Adminbao | 19218@12.com
 wfadmin | wf12@sa.com
 adminpang | dms@133.com
(10 rows)
​
Time: 39.301 ms

结论:速度上比ILIKE略好

随便说下几种匹配模式

上面的例子是全模糊查询,如果要全匹配,可以这样username ~* '^admin$'

四、创建索引

CREATE INDEX idx_auth_user_username ON auth_user lower(username);

# 如果希望LIKE也能使用
CREATE INDEX idx_auth_user_username ON auth_user lower(username varchar_pattern_ops);

这种方式,是创建了全部小写的索引,理论上是非常快的(最快的) 但是,某些情况下,你又想要大小写敏感了?那就不能用了如果需求就是大小写无关的,那么这是最好的方式

五、使用citext module模块

使用示例如下:

CREATE TABLE users (
 nick CITEXT PRIMARY KEY,
 pass TEXT NOT NULL
);
​
SELECT * FROM users WHERE nick = 'Larry';

citext通过转换每个字符串到小写执行比较,类似lower函数,速度也不会太快,但是使用上,SQL语句简单很多,详细参考这里:https://www.postgresql.org/docs/current/citext.html

六、表名和字段名的大小写敏感问题

PostgreSQL会自动把表名和字段名转成小写保存的,查询时也是会自动转成小写。一些程序或ORM会强制存为大小组合的名称,这个时候查询可能会报错,可以使用双引号让PostgreSQL不要做小写转换,示例如下:

SELECT username, email FROM "AuthUser" LIMIT 10;

未经许可,禁止转载!

• 数据库
• 容器化
• 测试