《MySQL必知必会》--正则表达式

正则表达式

由于MySQL的基础语句已经在上一系列学习过，因此，本系列重在补充尚未涉足之处。

正则表达式介绍

在学习python，尤其是学习数据清理时，我们就经常听到正则表达式。
我们知道，正则表达式是用来过滤检索数据的。
那么，在SQL语句中，我们应该如何使用正则表达式呢？

在之前，我们已经学过使用LIKE操作符来过滤检索数据了。
但是，正如我们所看见的，该方法使用% _等来进行检索过滤明显是过于简单。

比如，当我们需要检索所有包括数字的文本块时，或者查找并替换所有URL时，
LIKE操作符这种只能过滤具体某个字母、数字或字符串的方法就显得不合适了。

这时就需要我们的正则表达式登场了。

使用正则表达式

正则表达式作为一种相对独立的语言，具有自己特殊的语法和指令。
在MySQL语句仅支持部分正则表达式语句，本章介绍的也只是该部分中的大多数。

基本字符匹配

1
2
3
4
5
6
7
-- ---------------------------
-- 检索包含数字 ‘12’ 的prod_name
-- ---------------------------
select prod_name
from products
where prod_name regexp '12'
order by prod_name;

如果使用LIKE则形式如下：

1
2
3
4
select prod_name
from products
where prod_name like '%12%'
order by prod_name;

进行OR匹配

在REGEXP（正则表达式）中使用|代表OR。

1
2
3
4
select prod_name
from products
where prod_name regexp '8|12'
order by prod_name;

如果使用LIKE则应：

1
2
3
4
5
select prod_name
from products
where prod_name like '%8%'
or prod_name like '%12%'
order by prod_name;
1
2
3
4
5
6
7
-- ------------
-- MySQL不支持LIKE '[]'
-- ------------
select prod_name
from products
where prod_name like '%[8,12]%' 
order by prod_name;

匹配特定的字符

在正则表达式中使用[ ]来指定特定的字符。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select prod_name
from products
where prod_name regexp '[12|18|8] inch'
order by prod_name;

select prod_name
from products
where prod_name regexp '[128] inch'
order by prod_name;
-- 这两个结果是一样的，即[128]=[1|2|8]=[12|18|8]=[12188]

select prod_name
from products
where prod_name regexp '[^128] inch'
order by prod_name;
-- ^ = not

匹配范围

使用-代表范围：

1
2
3
4
5
6
7
8
9
select prod_name
from products
where prod_name regexp '[12345678] inch'
order by prod_name;

select prod_name
from products
where prod_name regexp '[1-8] inch'
order by prod_name;

匹配特殊字符

由于很多特殊字符如- .等在正则表达式中常代表特殊含义，直接使用正则表达式来匹配数据中包含.等特殊字符的本身形式会引发错误的匹配。
因此，需要使用\\转义符号来强制匹配特殊字符。

1
2
3
4
5
6
7
select prod_name
from products
where prod_name regexp '\\.'
order by prod_name;
----------------------
-- 检索带有 . 的数据
----------------------

在正则表达式中使用\来代表转义符号，而在SQL中需要再带一个\来代表转义。一个是正则表达式本身来识别，一个是给SQL来识别。
下面常用空白元字符：
\\f 代表换页
\\n 代表换行
\\r 代表回车
\\t 代表制表
\\v 纵向制表

匹配字符类

为简化字符集合的使用，正则表达式预定义了一些字符集合，成为字符类。

类 说明
[:alnum:] 任意字母和数字（同[a-zA-Z0-9])
[:alpha:] 任意字符（同[a-zA-Z])
[:blank:] 空格和制表（同[\\t]）
[:cntrl:] ASCII控制字符(ASCII 0-31 127)
[:digit:] 任意数字[0-9]
[:graph:] 与[:print:]相同，但不包含空格
[:lower:] 任意小写字母[a-z])
[:print:] 任意可打印字符
[:punct:] 即不在[:alnum:]也不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符[\\f\\n\\r\\t\\v]
[:upper:] 任意大写字母[A-Z]
[:xdigit:] 任意十六制数字[a-fA-F0-9]

匹配多个实例

重复元字符

元字符 说明
* 0个或多个匹配
+ 1个或多个匹配
? 0各或1各匹配
{n} 制定数目匹配
{n,} 不少于制定数目的匹配
{n,m} 匹配数目的范围(m=<255)

举个例子

1
2
3
4
5
6
select prod_name
from products
where prod_name regexp '[0-9] inch?';
-- -----------
-- ? 代表紧邻inch后可以有0个或1各字母，如inchs
-- -----------
1
2
3
4
5
SELECT prod_name
from products
where prod_name REGEXP '[[:digit:]]{4}'
order by prod_name;
-- 匹配连在一起的4个数字

定位符

定位元字符

元字符 说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾 
1
2
3
4
5
select prod_name
from products
where prod_name REGEXP '^[0-9\\.]'
order by prod_name;
-- 找出以数字（包含小数点）开头的数据

> 在[ ]之内表示否定NOT
> 在[ ]之前表示文本开始处

小结

除去简单介绍就是各种元字符。