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

1个月前

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'
~* 匹配正则表达式,大小写无关 username ~* 'Admin'
!~ 不匹配 正则表达式,大小写相关 username !~ 'Admin'
!~* 不匹配 正则表达式,大小写无关 username !~* 'Admin'

上面的例子是全模糊查询,如果要全匹配,可以这样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;

未经许可,禁止转载!