结构化查询语言SQL介绍及基本操作之正则表达式的使用详解_sql数据库正则表达式查询

用正则表达式进行搜索

本章将学习如何在MySQL WHERE 子句内使用正则表达式来更好地控制数据过滤。

正则表达式介绍

前两章中的过滤例子允许用匹配、比较和通配操作符寻找数据。对于基本的过滤(或者甚至是某些不那么基本的过滤),这样就足够了。但随着过滤条件的复杂性的增加, WHERE 子句本身的复杂性也有必要增加。这也就是正则表达式变得有用的地方。正则表达式是用来匹配文本的特殊的串(字符集合)。

如果你想从一个文本文件中提取电话号码,可以使用正则表达式。如果你需要查找名字中间有数字的所有文件,可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词,可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些URL的实际HTML链接,也可以使用一个正则表达式(对于最后这个例子,或者是两个正则表达式)。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。有见识的程序员和网络管理员已经关注作为他们技术工具重要内容的正则表达式很长时间了。正则表达式用正则表达式语言来建立,正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种特殊语言。与任意语言一样,正则表达式具有你必须学习的特殊的语法和指令。

完全覆盖正则表达式的内容超出了MySQL数据库教学的范围。虽然基础知识都在这里做了介绍,但对正则表达式更为透彻的介绍我们在前面shell课程中有。此处不再赘述。

使用MySQL正则表达式

那么,正则表达式与MySQL有何关系?已经说过,正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较。MySQL用 WHERE 子句对正则表达式提供了初步的支持,允许你指定正则表达式,过滤 SELECT 检索出的数据。

仅为正则表达式语言的一个子集 如果你熟悉正则表达式,需要注意:MySQL仅支持多数正则表达式实现的一个很小的子集。本章介绍MySQL支持的大多数内容。

1.基本字符匹配

产品名称中匹配1000的

MariaDB [test]> select prod_name from products where prod_name regexp '1000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

产品名称中匹配000的

MariaDB [test]> select prod_name from products where prod_name regexp '.000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name like '%000%';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

LIKE 匹配整个列。如果被匹配的文本在列值中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用通配符)。而 REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP 将会找到它,相应的行将被返回。这是一个非常重要的差别。

那么, REGEXP 能不能用来匹配整个列值(从而起与 LIKE 相同的作用)?答案是肯定的,使用 ^ 和 $ 定位符(anchor)即可

匹配不区分大小写 MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。

为区分大小写,可使用 BINARY 关键字,如 WHERE prod_name REGEXP BINARY 'JetPack .000' 。

MariaDB [test]> select prod_name from products where prod_name regexp 'jetpack';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name regexp binary 'jetpack';
Empty set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name regexp binary 'JetPack';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.进行OR匹配

为搜索两个串之一(或者为这个串,或者为另一个串),使用 |

产品名称中匹配1000或者2000的

MariaDB [test]> select prod_name from products where prod_name regexp '1000|2000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

产品名称中匹配 1000 或 2000 或 3000

MariaDB [test]> select prod_name from products where prod_name regexp '1000|2000|3000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

两个以上的 OR 条件 可以给出两个以上的 OR 条件。

3.匹配几个字符之一

匹配任何单一字符。但是,如果你只想匹配特定的字符,怎么办?可通过指定一组用 [ 和 ] 括起来的字符来完成

产品名称中匹配 1 或 2 或 3后面空格ton

MariaDB [test]> select prod_name from products where prod_name regexp '[123] ton';
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name regexp '[1|2|3] ton';
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name regexp '[1,2,3] ton';
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

4.匹配范围

集合可用来定义要匹配的一个或多个字符。例如,下面的集合将匹配数字0到9:[0123456789]

为简化这种类型的集合,可使用 - 来定义一个范围。

下面的式子功能上等同于上述数字列表:

范围不限于完整的集合, [1-3] 和 [6-9] 也是合法的范围。

此外,范围不一定只是数值的, [a-z] 匹配任意字母字符。

匹配 1 到 5 后面空格接ton

MariaDB [test]> select prod_name from products where prod_name regexp '[1-5] ton';
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

5.匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成。

我们已经看到 . 、[] 、| 和 - 等,还有其他一些字符。请问,如果你需要匹配这些字符,应该怎么办呢?

例如,如果要找出包含 . 字符的值,怎样搜索?

MariaDB [test]> select vend_name from vendors where vend_name regexp '.';
+----------------+
| vend_name      |
+----------------+
| Anvils R Us    |
| LT Supplies    |
| ACME           |
| Furball Inc.   |
| Jet Set        |
| Jouets Et Ours |
+----------------+
6 rows in set (0.00 sec)

MariaDB [test]> select vend_name from vendors where vend_name regexp '\.';
+----------------+
| vend_name      |
+----------------+
| Anvils R Us    |
| LT Supplies    |
| ACME           |
| Furball Inc.   |
| Jet Set        |
| Jouets Et Ours |
+----------------+
6 rows in set (0.00 sec)

MariaDB [test]> select vend_name from vendors where vend_name regexp '\\.';
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

这才是期望的输出。 \\. 匹配 . ,所以只检索出一行。这种处理就是所谓的 转义(escaping),

正则表达式内具有特殊意义的所有字符都必须以这种方式转义。

这包括 . 、 | 、 [] 以及迄今为止使用过的其他特殊字符。

为了匹配特殊字符,必须用 \\ 为前导。 \\- 表示查找 - , \\. 表示查找 . 。

匹配 \ 为了匹配反斜杠(\)字符本身,需要使用 \\\ 。

\\ 也用来引用元字符(具有特殊含义的字符),如下表所列。

\ 或 \\? 多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。

但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。

6.匹配字符类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。

为更方便工作,可以使用预定义的字符集,称为 字符类(character class)

下表列出字符类以及它们的含义：

7.匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。

如果存在一个匹配,该行被检索出来,如果不存在,检索不出任何行。

但有时需要对匹配的数目进行更强的控制。

例如,你可能需要寻找所有的数,不管数中包含多少数字,或者你可能想寻找一个单词并且还能够适应一个尾随的 s (如果存在),等等。

这可以用下表列出的正则表达式重复元字符来完成。

prod_name列中匹配包括括号，并且括号中的内容依次为 一位数字，一个空格，stick，s有或者没有

MariaDB [test]> select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

prod_name列中匹配4个数字

MariaDB [test]> select prod_name from products where prod_name regexp '[[:digit:]]{4}';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec）
1
2
3
4
5
6
7
8

需要注意的是,在使用正则表达式时,编写某个特殊的表达式几乎总是有不止一种方法。上面的例子也可以如下编写:

MariaDB [test]> select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

8.定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,需要使用下表列出的定位符。

找出以一个数(包括以小数点开始的数)开始的所有产品

MariaDB [test]> select prod_name from products where prod_name regexp '[0-9\\.]';+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
7 rows in set (0.00 sec)

MariaDB [test]> select prod_name from products where prod_name regexp '^[0-9\\.]';
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

简单搜索 [0-9\.] (或 [[:digit:]\.] )不行,因为它将在文本内任意位置查找匹配。解决办法是使用 ^ 定位符

^ 的双重用途 ^ 有两种用法。在集合中(用 [ 和 ] 定义),用它来否定该集合,否则,用来指串的开始处。

使 REGEXP 起类似 LIKE 的作用 本章前面说过, LIKE 和 REGEXP的不同在于, LIKE 匹配整个串而 REGEXP 匹配子串。

利用定位符,通过用 ^ 开始每个表达式,用 $ 结束每个表达式,可以使REGEXP 的作用与 LIKE 一样。

简单的正则表达式测试 可以在不使用数据库表的情况下用SELECT 来测试正则表达式。

REGEXP 检查总是返回 0 (没有匹配)或 1 (匹配)。可以用带文字串的 REGEXP 来测试表达式,并试验它们。相应的语法如下:

测试文本 hello 中是否能匹配到数字

MariaDB [test]> select 'hello' regexp '[0-9]';
+------------------------+
| 'hello' regexp '[0-9]' |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

测试文本中是否能匹配到制定数量的o

MariaDB [test]> select 'booboo' regexp '[o]{3,}';
+---------------------------+
| 'booboo' regexp '[o]{3,}' |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select 'boobooo' regexp '[o]{3,}';
+----------------------------+
| 'boobooo' regexp '[o]{3,}' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

小结

本章介绍了正则表达式的基础知识,学习了如何在MySQL的 SELECT语句中通过 REGEXP 关键字使用它们。