Postgresql杂谈 22——Postgresql中的模糊匹配

Postgresql对模糊查询的支持，主要有三种方法：传统的like操作符、SQL99新增的SIMILAR TO操作符以及POSIX正则表达式。除了前面两种SQL标准的模糊查询手段，Postgresql对正则表达式具有强大的支持，这就使得Postgresql可以支持任意字符串的匹配查找，下面我们分别对这三种方法进行学习。一、LIKE操作符1.1 “%”代表0个或任意个数的任意字符%代表了0个以上的

HymanLiuTS

6274人浏览 · 2021-08-27 08:36:13

HymanLiuTS · 2021-08-27 08:36:13 发布

一、LIKE操作符

1.1 “%”代表0个或任意个数的任意字符

%代表了0个以上的任意字符，为了验证%的使用，下面先看下测试表里面的数据：

stock_analysis_data=# select * from users;
 id |    tag    
----+-----------
  1 | asdfghjkl
  2 | asdfgh
  3 | zxcvsdf
  4 | asdfasd
  5 | jklxcvb
  6 | qwsxdf
(6 rows)

查询tag以asd开头的所有的数据：

stock_analysis_data=# select * from users where tag like 'asd%';
 id |    tag    
----+-----------
  1 | asdfghjkl
  2 | asdfgh
  4 | asdfasd
(3 rows)

查询以asd结尾的所有的数据：

stock_analysis_data=# select * from users where tag like '%asd';
 id |   tag   
----+---------
  4 | asdfasd
(1 row)

1.2 “_”代表1个任意字符

“_”可以代表任意一个字符，相对于“%"，使用”_“进行查询时具有了一定的限制，首先就是能且仅能代表一个字符。然后在查询时，对于目标字段，在where条件中，要能提供”_“以外

精确的字符。

查询以任意字符开头，”xcvsdf“结尾的所有数据：

stock_analysis_data=# select * from users where tag like '_xcvsdf';
 id |   tag   
----+---------
  3 | zxcvsdf
(1 row)

查询以”asd“开头，字符个数为6的所有数据（查询条件中asd后面是3个"_"）：

stock_analysis_data=# select * from users where tag like 'asd___';
 id |  tag   
----+--------
  2 | asdfgh
(1 row)

1.3 使用“\”进行转义

当查询的字符串中如果包含了”%“或者”_“，可以使用”\“进行转义，比如查询第4个字符为”%“的所有数据：

stock_analysis_data=# select * from users where tag like 'asd\%%';
 id |   tag   
----+---------
  7 | asd%hjk
(1 row)

1.4 使用“ESCAPE”自定义转义字符

除了使用"\"进行转义之外，还可以使用escape自定义转义字符，比如还是上面的例子，查询第4个字符为”%“的所有数据，但是使用@作为转义字符：

stock_analysis_data=# select * from users where tag like 'asd@%%' escape '@';
 id |   tag   
----+---------
  7 | asd%hjk
(1 row)

又或者，查询字符串中以”\“开头的数据：

stock_analysis_data=# select * from users where tag like '@\%' escape '@';
 id |  tag   
----+--------
  9 | \ashjk
(1 row)

1.5 使用ILIKE查询时忽略大小写

ILIKE是Postgresql提供的，但是在标准SQL中没有提供的匹配操作符，和LIKE不同，使用ILIKE查询时忽略大小写，比如下面的例子，查询以asd开头的所有数据，并忽略大小写：

stock_analysis_data=# select * from users where tag ilike 'asd%';
 id |    tag     
----+------------
  1 | asdfghjkl
  2 | asdfgh
  4 | asdfasd
  7 | asd%hjk
  8 | asd_}}hjk
 10 | ASDsdsfdfg
 11 | ASD123sd
(7 rows)

二、similar to操作符

在SQL99的标准中增加了similar to操作符，这个操作符可以像like一样进行使用，也可以使用正则表达式。

2.1 similar to代替LIKE

下面，我们使用similar to查询使用like时的那几个例子。

查询以“asd”开头的所有数据：

stock_analysis_data=# select * from users where tag similar to 'asd%';
 id |    tag    
----+-----------
  1 | asdfghjkl
  2 | asdfgh
  4 | asdfasd
  7 | asd%hjk
  8 | asd_}}hjk
(5 rows)

从上面的查询结果中，也可以看到，similar to匹配时无法忽略大小写，大小写要严格和查询条件一致。

使用similar to时同样支持转义字符，查询第4个字符为”%“的所有数据：

stock_analysis_data=# select * from users where tag similar to 'asd\%%';
 id |   tag   
----+---------
  7 | asd%hjk
(1 row)

2.2 similar to后接正则表达式

similar to除了可以代替LIKE操作符之外，还可以后接正则表达式。比如正则表达式‘(z|a)\S*’表示查询以z或者a开头，任意长度的字符串，可以使用similar to后接该正则表达式进行查询：

stock_analysis_data=# select * from users where tag similar to '(z|a)\S*';
 id |    tag    
----+-----------
  1 | asdfghjkl
  2 | asdfgh
  3 | zxcvsdf
  4 | asdfasd
  7 | asd%hjk
  8 | asd_}}hjk
(6 rows)

查找以asd开头和结尾的所有数据：

stock_analysis_data=# select * from users where tag similar to 'asd\S*asd';
 id |   tag   
----+---------
  4 | asdfasd
(1 row)

三、~等操作符直接匹配正则表达式

除了LIKE、ILIKE和similar to，Postgrsq还支持以下四个操作符，直接作用于正则表达式：

~：匹配正则表达式，区分大小写
~*：匹配正则表达式，不区分大小写
!~：不匹配正则表达式，区分大小写
!~*：不匹配正则表达式，不区分大小写

查询以asd开头的所有数据，忽略大小写：

stock_analysis_data=# select * from users where tag ~* 'asd\S*';
 id |    tag     
----+------------
  1 | asdfghjkl
  2 | asdfgh
  4 | asdfasd
  7 | asd%hjk
  8 | asd_}}hjk
 10 | ASDsdsfdfg
 11 | ASD123sd
(7 rows)

这里使用的~*操作符，所以查询出来的结果忽略了大小写。