2025年彻底搞懂PostgreSQL的模糊查询-LIKE和ILIKE

彻底搞懂PostgreSQL的模糊查询-LIKE和ILIKE如果我们使用的是 PostgreSQL 数据库 那么我们可以使用 LIKE 和 ILIKE 做模糊查询 LIKE 语法是 SQL 标准而 ILIKE 是 PostgreSQL 的一个扩展 构造数据 先创建一张表 然后插入一些数据 create table test id serial name varchar 16

大家好,我是讯享网,很高兴认识大家。

如果我们使用的是PostgreSQL数据库,那么我们可以使用LIKE和ILIKE做模糊查询,LIKE语法是SQL标准而ILIKE是PostgreSQL的一个扩展。

构造数据

先创建一张表,然后插入一些数据;

create table test( id serial, name varchar(16) ); my_test_db=#select * from test; id | name ----+--------- 1 | One 2 | Two 3 | One_Two 4 | One/Two 5 | oNE 6 | 99% (6 rows) 

讯享网

基本使用

在使用 LIKE/ILIKE 时,有两个通配符:百分号 (%) 和下划线 (_)

  • % - 匹配任何零个或多个字符
  • _ - 匹配单个任意字符

先看一下这个的查询:

讯享网my_test_db=#select * from test where name like 'O%'; id | name ----+--------- 1 | One 3 | One_Two 4 | One/Two (3 rows) 

这个语句匹配所有以 O 打头的数据。

下面查询使用通配符 _

my_test_db=#select * from test where name like '_n_'; id | name ----+------ 1 | One (1 row) 

上面都是使用LIKE做模糊匹配,现在我们用一下ILIKE(除了不区分大小写外,和LIKE一样)。

讯享网my_test_db=#select * from test where name ilike 'O%'; id | name ----+--------- 1 | One 3 | One_Two 4 | One/Two 5 | oNE (4 rows) 

使用 lower() 函数和 LIKE 也能实现上面的效果

my_test_db=#select * from test where lower(name) like 'o%'; 

结果和上面sql的执行结果一样。

我推荐使用lower like,因为它的性能比ILIKE高出15%以上,下面做个测试:

首先创建一个表,然后通过脚本插入行随机数据:

讯享网require 'securerandom' inserts = [] .times do |i| inserts << "(1, 'fake', '#{SecureRandom.urlsafe_base64(64)}')" end sql = "insert into books (user_id, title, description) values #{inserts.join(', ')}" ActiveRecord::Base.connection.execute(sql) 

验证数据行数:

my_test_db=# select count(id) from books ; count ---------  

做个查询,看一下返回数据

讯享网my_test_db=# SELECT "books".* FROM "books" WHERE "books"."published" = 'f' my_test_db=# and (LOWER(description) LIKE '%abcde%') ; id | user_id | title | description | published ---------+---------+-------+----------------------------------------------------------------------------------------+------  | 1 | fake | 5WRGr7oCKABcdehqPKsUqV8ji61rsNGS1TX6pW5LJKrspOI_ttLNbaSyRz1BwTGQxp3OaxW7Xl6fzVpCu9y3fA | f  | 1 | fake | J6q0VkZ8-UlxIMZ_MFU_wsz_8MP3ZBQvkUo8-2INiDIp7yCZYoXqRyp1Lg7JyOwfsIVdpPIKNt1uLeaBCdelPQ | f  | 1 | fake | YubxlSkJOvmQo1hkk5pA1q2mMK6T7cOdcU3ADUKZO8s3otEAbCdEcmm72IOxiBdaXSrw20Nq2Lb383lq230wYg | f 

分别查看LOWER LIKE和ILIKE的执行计划

LOWER LIKE结果

my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (LOWER(description) LIKE '%abcde%') ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on books (cost=0.00..32420.14 rows=1600 width=117) (actual time=938.627..4114.038 rows=3 loops=1) Filter: ((NOT published) AND (lower(description) ~~ '%abcde%'::text)) Rows Removed by Filter:  Total runtime: 4114.098 ms 

ILIKE 结果

讯享网my_test_db=# EXPLAIN ANALYZE SELECT "books".* FROM "books" WHERE "books"."published" = 'f' and (description iLIKE '%abcde%') ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on books (cost=0.00..29920.11 rows=100 width=117) (actual time=1147.612..4986.771 rows=3 loops=1) Filter: ((NOT published) AND (description ~~* '%abcde%'::text)) Rows Removed by Filter:  Total runtime: 4986.831 ms 

从结果可以看到LOWER LIKE比ILIKE快了差不多17%,好了言归正传,继续聊LIKE和ILIKE。

如何匹配 % 或 _ 本身

使用默认的转义字符(反斜杠)来转义

select * from test where name like '%\%'; 

上面SQL语句查询以%结尾的数据。

讯享网select * from test where name like '%\_%'; 

这个SQL语句查询含有 _ 的数据。


讯享网

Notice:~~ 和 LIKE、~~* 和 ILIKE、!~~ 和 NOT LIKE、!~~* 和 NOT ILIKE是可以互换的。 

索引

索引用于加快搜索速度。 PostgreSQL自动为主键、唯一键等列创建索引。或者我们可以显式创建索引。

如果某列有可用的索引,如果不以 % 或 _ 开头,则 LIKE 会使用该索引(name LIKE ‘one%’ 会走索引,而 name like ‘%one’ 不会走索引)。

对于ILIKE,当且仅当以非字母字符(不受大小写转换影响的字符)开头时,才会走索引。

Mirrored Indexes

如果在使用LIKE的时候,查询通配符以 % 或 _ 开头,有没有什么办法走索引?

可以通过一下两步操作,让它走索引:

  1. 在该列上创建一个 reverse() 函数索引。
  2. 用reversed模式查询

举个例子,下面的查询不走索引

讯享网select * from test where name like '%wo'; 

那么,我们在name列上创建个索引

create index rev_idx on test(reverse(name)); 

将上面的查询SQL变成下面的SQL语句

讯享网select * from test where reverse(name) like reverse('%wo'); 

构造数据

先创建一张表,然后插入一些数据;

create table test( id serial, name varchar(16) ); my_test_db=#select * from test; id | name ----+--------- 1 | One 2 | Two 3 | One_Two 4 | One/Two 5 | oNE 6 | 99% (6 rows) 

基本使用

在使用 LIKE/ILIKE 时,有两个通配符:百分号 (%) 和下划线 (_)

  • % - 匹配任何零个或多个字符
  • _ - 匹配单个任意字符

先看一下这个的查询:

讯享网my_test_db=#select * from test where name like 'O%'; id | name ----+--------- 1 | One 3 | One_Two 4 | One/Two (3 rows) 

这个语句匹配所有以 O 打头的数据。

下面查询使用通配符 _

my_test_db=#select * from test where name like '_n_'; id | name ----+------ 1 | One (1 row) 

上面都是使用LIKE做模糊匹配,现在我们用一下ILIKE(除了不区分大小写外,和LIKE一样)。

讯享网my_test_db=#select * from test where name ilike 'O%'; id | name ----+--------- 1 | One 3 | One_Two 4 | One/Two 5 | oNE (4 rows) 

使用 lower() 函数和 LIKE 也能实现上面的效果

my_test_db=#select * from test where lower(name) like 'o%'; 

结果和上面sql的执行结果一样。

小讯
上一篇 2025-04-04 13:33
下一篇 2025-04-09 08:42

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/112387.html