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