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' |
| ~* 匹配正则表达式，大小写无关 | 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; 未经许可，禁止转载！