PostgreSQL 模糊查询 (~ like ilike) 使用区别

发表于

在 PostgreSQL 数据库中，~、LIKE、ILIKE 都可以用于模糊查询，本文简单介绍三者使用区别。

LIKE

标准的模糊查询，支持 %(任意个字符)，_(任意单个字符)，并区分大小写，使用实例：

1
2
3
4
select 'abcDEF' like '%cD%'; -- true
select 'abcDEF' like '%cd%'; -- false
select 'abcDEF' like '%c_E%'; -- true
select 'abcDEF' like '_bcDE_'; -- true

ILIKE

与 LIKE 使用方式相似，前面的 i 代表 ignore，即与 LIKE 的区别就是忽略大小写：

1
select 'abcDEF' like '%cd%'; -- true

~

~ 相关的表示正则匹配，当然也可以用来做模糊查询。可使用的方式有：~，~*，!~，!~*。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 正则匹配，区分大小写（~）
select 'abcDEF' ~ 'cD'; -- true
select 'abcDEF' ~ 'cd'; -- false
select 'abcDEF' ~ '^[a-z]'; -- true
select 'abcDEF' ~ '[a-z]$'; -- false

-- 取反（!~）
select 'abcDEF' !~ 'cD'; -- false
select 'abcDEF' !~ '^[a-z]'; -- false

-- 不区分大小写（~*）
select 'abcDEF' ~* 'cd'; -- true
select 'abcDEF' ~* '[a-z]$'; -- true

-- 不区分大小写并且取反（!~*）
select 'abcDEF' !~* 'cd'; -- false

~~

上面说的是一个~ 的操作符，代表正则匹配。PostgreSQL 中还可以使用两个~（~~），两个~ 就和 like 的意义一样，并且也有对应的：~~，~~*，!~~，!~~*。

~~ 对应 LIKE，~~* 对应 ILIKE，!~~ 对应 NOT LIKE，!~~* 对应 NOT ILIKE。

1
2
3
4
5
select 'abcDEF' ~~ 'cD'; -- false
select 'abcDEF' ~~ '%cD%'; -- true
select 'abcDEF' ~~ '%cd%'; -- false
select 'abcDEF' ~~* '%cd%'; -- true
select 'abcDEF' !~~* '%cd%'; -- false