新梦幻诛仙怎么下载:PostgreSQL查詢中的對象名稱、數據內容大小寫敏感問題

wuxqing 8個月前發布 | 17K 次閱讀

梦幻诛仙手游丹青阁天书 www.rasug.icu 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    | [email protected]
(1 row)
?
Time: 0.491 ms

2、再看看使用LOWER函數的情況

SELECT username, email FROM auth_user WHERE LOWER(username) = LOWER('Admin') LIMIT 10;
 username |       email       
----------+-------------------
 Admin    | [email protected]
 admin    | [email protected]
(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    | [email protected]
 admin    | [email protected]
(2 rows)
?
Time: 1227.317 ms

結論:看起來比使用 LOWER/UPPER 還慢!!!

SELECT username, email FROM auth_user WHERE username ILIKE '%Admin%' LIMIT 10;
    username    |        email        
----------------+---------------------
 Admin          | [email protected]
 admin19880211  | [email protected]
 adminis啊      | [email protected]
 superadmins    | [email protected]
 admini123      | [email protected]
 usadmin   mj   | [email protected]
 admin          | [email protected]
 Adminbao       | [email protected]
 wfadmin        | [email protected]
 adminpang      | [email protected]
(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    | [email protected]
 admin    | [email protected]
(2 rows)
?
Time: 761.884 ms
SELECT username, email FROM auth_user WHERE username ~* 'admin' LIMIT 10;
    username    |        email        
----------------+---------------------
 Admin          | [email protected]
 admin19880211  | [email protected]
 adminis啊      | [email protected]
 superadmins    | [email protected]
 admini123      | [email protected]
 usadmin   mj   | [email protected]
 admin          | [email protected]
 Adminbao       | [email protected]
 wfadmin        | [email protected]
 adminpang      | [email protected]
(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???/h2>

使用示例如下:

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;

未經許可,禁止轉載!

 本文由用戶 wuxqing 自行上傳分享,僅供網友學習交流。所有權歸原作者,若您的權利被侵害,請聯系管理員。
 轉載本站原創文章,請注明出處,并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺,歡迎各類分享!