Mysql入门系列五：模糊匹配过滤数据

上一节介绍的过滤数据的方式都是在已知条件下，例如知道生产商的ID，价格的范围等等，但是有的时候我们只知道部分条件，上一节的操作符就不起作用了，所以需要使用通配符以及正则表达式来进行更复杂的过滤。

本节对应《Mysql必知必会》八、九章

用通配符进行过滤

通配符（wildcard）  用来匹配值的一部分的特殊字符

搜索模式（search  pattern）  由字面值、通配符或两者组合构成的搜索条件。

LIKE操作符

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号( % )通配符

#查找以jet开头的产品名的相关信息
select prod_id,prod_name,prod_price from products where prod_name like 'jet%';

% 表示Mysql接受jet后面的任何字符

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符

#查询产品名中包含 anvi 的产品信息
select prod_id,prod_name,prod_price from products where prod_name like '%anvi%';

只要注意%可以代表0个、1个或多个字符，但是不能匹配NULL

下划线( _ )通配符

下划线( _ )也是匹配字符，但是只能匹配一个字符

#查找以 ton anvil结尾的产品
select prod_id,prod_name from products where prod_name like '% ton anvil';

下面用下划线测试一下

select prod_id,prod_name from products where prod_name like '_ ton anvil';

可以看出 .5 ton anvil没有查询出来，是因为下划线只匹配单个字符

使用通配符的技巧

通配符虽然好用但是效率很低，所以能不用就不用，下面给出几条使用技巧

1.不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2.在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

3.仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

用正则表达式进行搜索

正则表达式在各个程序设计语言、文本编辑器，操作系统都有，用正则表达式进行字符串匹配十分方便，但是Mysql只支持正则表达式的子集。

基本字符匹配

#查询产品名包含1000的产品信息
select prod_name,prod_price from products where prod_name regexp '1000' order by prod_name;

REGEXP表示后面跟的是正则表达式，需要注意的是regexp和like是不一样的，下面看个例子

select prod_name,prod_price from products where prod_name like '1000' order by prod_name;

可以看到查询结果为空，原因是like是精准匹配，找的是产品名是 '1000'，而regexp表示名字中包含字符串 '1000'

上面提到下划线( _ )表示任意一个字符，正则表达式中则是用点( . )

#查询商品名中包含000的商品信息
select prod_name,prod_price from products where prod_name regexp '.000' order by prod_name;

 

可以看出1000和2000都匹配上了

注：Mysql默认正则表达式也是不区分大小写的，如果需要区分大小写需要加BINARY关键字

#查询商品名中包含JetPack 000的商品信息
select prod_name,prod_price from products where prod_name regexp binary 'JetPack .000' order by prod_name;

如果换成小写

#查询商品名中包含JetPack 000的商品信息
select prod_name,prod_price from products where prod_name regexp binary 'jetpack .000' order by prod_name;

结果为空

进行OR匹配

为了搜索多个字符串之一，使用 | ，表示逻辑或

#查询商品名中包含1000或2000的商品信息
select prod_name,prod_price from products where prod_name regexp '1000|2000';

匹配几个字符之一

用 [ 和 ] 表示多个候选

#查询产品名包含 1 Ton 、 2 Ton 、 3 Ton 的产品信息
select prod_name,prod_price from products where prod_name regexp '[123] Ton' order by prod_name;

[123]表示的是[1|2|3]，相当于OR，但需要特别注意 '[123] Ton'不要写成了'1|2|3 Ton'，后者表示匹配1或2或3 Ton

select prod_name,prod_price from products where prod_name regexp '1|2|3 Ton' order by prod_name;

字符串集合也可以被否定，例如[^123]表示匹配除1 2 3之外的字符

#查询产品名不包含 1 Ton 、 2 Ton 、 3 Ton 的产品信息
select prod_name,prod_price from products where prod_name regexp '[^123] Ton' order by prod_name;

匹配范围

[0-9]表示[0123456789]，为了简化表示，用 - 表示范围，[a-z]表示任意字母字符

#查询1-5 Ton的产品信息
select prod_name,prod_price from products where prod_name regexp '[1-5] Ton';

匹配特殊字符

有些特殊字符例如 '.' ，是不能直接匹配的，举个例子

#想查找供应商名字包含.的，但实际结果不对
select vend_name from vendors where vend_name regexp '.' order by vend_name;

'.' 表示任意字符，所以所有行都被匹配出来了

为了匹配特殊字符，需要用 \\ 来进行转义，需要注意的是高级语言一般用一个 \ 表示转义，mysql是两个

select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

如果需要匹配 \ ，需要用 \\\

\\ 也可以用来引用元字符(具有特殊含义的字符)

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配多个实例

上面的匹配都是单次出现，现在用正则表达式重复元字符来完成

元字符	说明
*	0个或多个匹配
+	1个或多个匹配(等于{1，})
?	0个或1个匹配(等于{0,1})
{n}	指定数目n的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围(m不超过255)

举个例子

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

这个sql语句的意思是找出产品名字中包含格式为 (数字 stick或sticks) 的产品名

\\( 和 \\) 对应的是括号，[0-9]表示的是任意一个数字，sticks?表示最后的字母s出现0次或1次

另一个例子，匹配连在一起的四位数字

select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;

[:digit:]表示[0-9]，{4}表示出现四次，所以是任意一个四位数字

上述sql语句与下面的sql语句等价

select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]' order by prod_name;

定位符

上面的都是匹配任意位置的文本，现在学习匹配特定位置的文本

定位元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

举个例子，我们现在想找以数字(或小数点开始的数)开始的所有产品，假如用[0-9\\.]来匹配

select prod_name from products where prod_name regexp '[0-9\\.]';

结果发现并不是我们想要的结果，后面四条结果是错误的

所以需要用 ^[0-9\\.]来进行匹配，它的意思是只有开始是数字或小数点开始的数字才会匹配

select prod_name from products where prod_name regexp '^[0-9\\.]';

需要注意的是 ^ 有两个用途，集合里面( [和] )表示否定该集合，否则是表示字符串的开始

可以在不使用数据库的情况下用SELECT来测试正则表达式，REGEXP匹配上返回1，否则返回0

select 'hello' regexp '[0-9]';

hello字符串里面没有数字，所以返回0

下一节讲述创建计算字段和使用数据处理函数