内容摘要:学习如何在 MySQL WHERE 子句内使用正则表达式来更好地控制数据过滤。
9.1 正则表达式介绍
正则表达式:用来匹配文本的特殊的串。所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。
9.2 使用 MySQL 正则表达式
注意:MySQL 仅支持多数正则表达式实现的一个很小的子集。
9.2.1 基本字符匹配
REGEXP(
regular expression,正则表达式)
:正则表达式操作符。
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY prod_name; // 找出所有产品名包含文本为 1000 的产品
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP ‘.000’ ORDER BY prod_name; // 找出所有产品名包含文本为 “多少 000” 的产品,如 1000、2000 等均匹配
点号( . ):正则表达式中,表示匹配任意一个字符。
LIKE 和 REGEXP 的区别:
- LIKE 匹配整个列。如 prod_name 为 “JetPack 1000”,则 LIKE 将无法找到它;
- REGEXP 在列的值内进行匹配。同样为“JetPack 1000”,REGEXP 可以找到并返回。
注意:正则表达式不区分大小写。若需要区分大小写可使用 BINARY 关键字,如:
WHERE prod_name REGEXP BINARY ‘JetPack .000’;
9.2.2 进行 OR 匹配
竖线( | ):正则表达式的 OR 操作符,匹配两个串其中之一。
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP ‘1000|2000’ ORDER BY prod_name; // 找出所有产品名包含文本为 1000 或 2000 的产品
9.2.3 匹配几个字符之一
中括号( [ ] ):匹配特定的字符。
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name; // 找出所有产品名包含文本为 1 Ton 或 2 Ton 或 3 Ton 的产品
正则表达式 [123] Ton 是 [ 1 | 2 | 3 ] Ton 的缩写,也可以使用后者,但不可用 1 | 2 | 3 Ton 代替,否则 MySQL 将假定你的意思是 ‘1’ 或 ‘2’ 或 ‘3 Ton’,将返回非必要结果。
^ :代表否定(一个字符集)。
SELECT prod_id,prod_name FROM products WHERE prod_name REGEXP ‘[^123] Ton’ ORDER BY prod_name; // 找出所有产品名
不
包含文本为 1 Ton 或 2 Ton 或 3 Ton 的产品
9.2.4 匹配范围
连接符( - ):定义一个范围。
[0123456789] 可简化为 [0-9]
[a-z]:匹配任意字母字符
9.2.5 匹配特殊字符
正则表达式由某些特殊含义的字符构成,但如何匹配这些字符本身?
转义( \\ ):正则表达式具有特殊意义的所有字符都必须以这种方式转义。
SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\\.’ ORDER BY vend_name; // 找出所有产品名
包含文本为点(.)的产品
\\ 也用来引用元字符(具有特殊意义的字符)
元字符 | 说明 |
\\f
| 换页 |
\\n
| 换行 |
\\r
| 回车 |
\\t
| 制表 |
\\v
| 纵向制表 |
\\\ :匹配反斜杠( \ )本身
说明:多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身,但 MySQL 要求两个反斜杠,因为 MySQL 自己解释一个,正则表达式解释一个。
9.2.6 匹配字符类
字符类:预定义的字符集。
类 | 说明 |
[:alnum:]
| 任意字母和数字(同 [a-zA-Z0-9] ) |
[:alpha:]
| 任意字符(同 [a-zA-Z] ) |
[:blank:]
| 空格和制表(同 [\\t] ) |
[:cntrl:]
| ASCII 控制字符(ASCII 从 0 到 31 和 137) |
[: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]) |
9.2.7 匹配多个实例
目前为止的正则表达式都试图匹配单次出现,有时需要对匹配的数目进行更强的控制。
正则表达式重复元字符:
元字符 | 说明 |
*
|
0 个或多个匹配
|
+
|
1 个或多个匹配(等于{1,})
|
?
|
0 个或 1 个匹配(等于{0,1})
|
{n}
| 指定数目的匹配 |
{n,}
| 不少于指定数目的匹配 |
{n,m}
| 匹配数目的范围(m 不超过 255) |
举例说明:
输入:SELECT prod_name FROM products WHERE prod_name REGEXP ‘
\\([0-9] sticks?\\)
’ ORDER BY prod_name;
解释说明:
\\(
匹配左圆括号
[0-9]
匹配任意数字
sticks?
匹配 stick 或 sticks 等结果(因为‘?’为 0 个或 1 个匹配)
\\)
匹配右圆括号
输出结果可能有:
TNT (1 stick)
TNT (5 sticks)
输入:SELECT prod_name FROM products WHERE prod_name REGEXP ‘
[[:
digit
:]]{4}
’ ORDER BY prod_name;
解释说明:
[:digit:]
匹配任意数字(其实就是任意一个数字)
[[:digit:]]
相当于
[0-9]
(problem:此处为何需要两层?一层是否可行)
{4}
出现 4 次
[[:digit:]]{4} 匹配连在一起的任意四位数字匹配
输出结果可能有:
JetPack 1000
JetPack 3475 等
输入:SELECT prod_name FROM products WHERE prod_name REGEXP ‘
[0-9][0-9][0-9][0-9]
’ ORDER BY prod_name; // 与上一个案例相同
9.2.8 定位符
目前为止所有的例子都是匹配一个串中任意位置的文本。
匹配特定位置的定位符:
元字符 | 说明 |
^
| 文本的开始 |
$
| 文本的结尾 |
[[:<:]]
| 词的开始 |
[[:<:]]
| 词的结尾 |
SELECT prod_name FROM products WHERE prod_name REGEXP ‘
^
[0-9\\.]
’ ORDER BY prod_name; // 检索所有产品名以数字开头(包括以小数点开头)开始的所有产品
^ 有两种用法:在集合(用中括号 [ ] 定义)中用来否定该集合,否则用来指串的开始处。
(problem:如何区分两者?)
利用定位符,通过 ^ 开始每个表达式,通过 $ 结束每个表达式,可以使 REGEXP 的作用与 LIKE 相同,因为 LIKE 匹配整个字符串。