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;

未經許可，禁止轉載！