MySQL全面瓦解8：查询的正则匹配

爱新觉羅丶高

发布于 2022-8-1 18:52

浏览

0收藏

概述
上一章查询的过滤条件，我们了解了MySQL可以通过 like % 通配符来进行模糊匹配。同样的，它也支持其他正则表达式的匹配，我们在MySQL中使用 REGEXP 操作符来进行正则表达式匹配。用法和like相

似，但又强大很多，能够实现一些很特殊的、复杂的规则匹配。正则表达式使用REGEXP命令进行匹配时，如果符合返回1，不符合返回0。如果默认不加任何匹配规则REGEXP相当于like '%%'。在前面加上NOT（NOT REGEXP）相当于NOT LIKE。

匹配模式分析
下面有个表格，罗列了可应用于 REGEXP 操作符中正则匹配模式，描述相对比较详细了，后面我们一个一个来测试。

MySQL全面瓦解8：查询的正则匹配-鸿蒙开发者社区

匹配模式^
从字符串首部分进行匹配，这边匹配s开头的，匹配符合返回1，不符合返回0。应用到表中，既符合返回匹配到的数据。

 mysql> select 'selina' REGEXP '^s';
 +----------------------+
 | 'selina' REGEXP '^s' |
 +----------------------+
 |                    1 |
 +----------------------+
 1 row in set
 
 mysql> select 'aelina' REGEXP '^s';
 +----------------------+
 | 'aelina' REGEXP '^s' |
 +----------------------+
 |                    0 |
 +----------------------+
 1 row in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set 
 mysql> select * from user2 where name REGEXP '^s';
 +----+--------+-----+---------+-----+
 | id | name   | age | address | sex |
 +----+--------+-----+---------+-----+
 |  3 | sol    |  21 | xiamen  |   0 |
 |  5 | selina |  25 | NULL    |   0 |
 +----+--------+-----+---------+-----+

2 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

匹配模式$
从字符串尾部进行匹配，这边匹配名称以d结尾的数据。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'd$';
 +----+-------+-----+---------+-----+
 | id | name  | age | address | sex |
 +----+-------+-----+---------+-----+
 |  1 | brand |  21 | fuzhou  |   1 |
 +----+-------+-----+---------+-----+
 1 row in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

匹配模式.

. 是匹配任意单个字符，下面脚本匹配 n并且后面带一个任意字符的条件

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'n.';
 +----+--------+-----+---------+-----+
 | id | name   | age | address | sex |
 +----+--------+-----+---------+-----+
 |  1 | brand  |  21 | fuzhou  |   1 |
 |  4 | weng   |  33 | guizhou |   1 |
 |  5 | selina |  25 | NULL    |   0 |
 +----+--------+-----+---------+-----+
 3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

匹配模式[...]
指匹配括号内的任意单个字符，只要有一个字符符合条件即可。下面例子能匹配到b、w、z的只有brand、weng 两个名称。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP [bwz];
 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL rver version for the right syntax to use near '[bwz]' at line 1
 mysql> select * from user2 where name REGEXP '[bwz]';
 +----+-------+-----+---------+-----+
 | id | name  | age | address | sex |
 +----+-------+-----+---------+-----+
 |  1 | brand |  21 | fuzhou  |   1 |
 |  4 | weng  |  33 | guizhou |   1 |
 +----+-------+-----+---------+-----+
 2 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

匹配模式[^...]

[^...]取反的意思，指匹配未包含的任意字符。例如， '[^brand]' 可以匹配 "helen" 中的'h'，"sol" 的 "s"，"weng" 的 "w"，"selina" 的 "s"，但无法匹配"brand"，所以被过滤了。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP '[^brand]';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 4 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

匹配模式[n-m]
匹配m到n之间的任意单个字符，例如[0-9],[a-z],[A-Z]，下方代码中，任何元素不在a - e之间的"sol" 被过滤了。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP '[a-e]';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 4 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

匹配模式 *

匹配前面的子表达式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等价于{0,}。下面的 "e*g" 可以匹配的只有 "weng" 这个名称。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 5 rows in set
 
 mysql> select * from user2 where name REGEXP 'e*g';
 +----+------+-----+---------+-----+
 | id | name | age | address | sex |
 +----+------+-----+---------+-----+
 |  4 | weng |  33 | guizhou |   1 |
 +----+------+-----+---------+-----+
 1 row in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

匹配模式 +

匹配前面的子表达式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等价于 {1,}。如下方的脚本，符合条件的是1到多个的n加上一个d的组合，只有 "brand" 和 "annd" 符合。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'n+d';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 2 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

匹配模式 ?
匹配前面的子表达式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等价于 {0,1}。e为1个或者0个，后面再用 l 限制，所以符合的只有三个。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'e?l';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 3 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

匹配模式 a1| a2|a3

匹配 a1 或 a2 或 a3。例如下方，'nn|en' 能分别匹配到 "anny" 、"annd" 和 "helen"、"weng"。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'nn|en';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  2 | helen |  20 | quanzhou |   0 |
 |  4 | weng  |  33 | guizhou  |   1 |
 |  6 | anny  |  23 | shanghai |   0 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 4 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

匹配模式 {n} {n,} {n,m} {,m}
n 和 m 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。m为空代表>=n的任意数，n为空代表0。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP 'n{2}';
 +----+------+-----+----------+-----+
 | id | name | age | address  | sex |
 +----+------+-----+----------+-----+
 |  6 | anny |  23 | shanghai |   0 |
 |  7 | annd |  24 | shanghai |   1 |
 +----+------+-----+----------+-----+
 2 rows in set
 
 mysql> select * from user2 where name REGEXP 'n{1,2}';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 6 rows in set
 
 mysql> select * from user2 where name REGEXP 'l{1,}';
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  5 | selina |  25 | NULL     |   0 |
 +----+--------+-----+----------+-----+
 3 rows in set1.
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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

匹配模式(...)
假设括号内容为abc，则是将abc作为一个整体去匹配，符合这个规则的数据被过滤出来。下面以an为例子，配合上面学过的知识。

 mysql> select * from user2;
 +----+--------+-----+----------+-----+
 | id | name   | age | address  | sex |
 +----+--------+-----+----------+-----+
 |  1 | brand  |  21 | fuzhou   |   1 |
 |  2 | helen  |  20 | quanzhou |   0 |
 |  3 | sol    |  21 | xiamen   |   0 |
 |  4 | weng   |  33 | guizhou  |   1 |
 |  5 | selina |  25 | NULL     |   0 |
 |  6 | anny   |  23 | shanghai |   0 |
 |  7 | annd   |  24 | shanghai |   1 |
 +----+--------+-----+----------+-----+
 7 rows in set
 
 mysql> select * from user2 where name REGEXP '(an)+';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  6 | anny  |  23 | shanghai |   0 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 3 rows in set
 
 mysql> select * from user2 where name REGEXP '(ann)+';
 +----+------+-----+----------+-----+
 | id | name | age | address  | sex |
 +----+------+-----+----------+-----+
 |  6 | anny |  23 | shanghai |   0 |
 |  7 | annd |  24 | shanghai |   1 |
 +----+------+-----+----------+-----+
 2 rows in set
 
 mysql> select * from user2 where name REGEXP '(an).*d{1,2}';
 +----+-------+-----+----------+-----+
 | id | name  | age | address  | sex |
 +----+-------+-----+----------+-----+
 |  1 | brand |  21 | fuzhou   |   1 |
 |  7 | annd  |  24 | shanghai |   1 |
 +----+-------+-----+----------+-----+
 2 rows in set1.
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.
34.
35.
36.
37.
38.
39.
40.
41.

匹配特殊字符 \\
正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、 []、|、*、+ 等，那我们是怎么匹配这些字符的。如下示例，我们使用 \\ 来匹配特殊字符，\\为前导， \\-表示查找-， \\.表示查找.。

 mysql> select * from user3;
 +----+------+-------+
 | id | age  | name  |
 +----+------+-------+
 |  1 |   20 | brand |
 |  2 |   22 | sol   |
 |  3 |   20 | helen |
 |  4 | 19.5 | diny  |
 +----+------+-------+
 4 rows in set
 
 mysql> select * from user3 where age REGEXP '[0-9]+\\.[0-9]+';
 +----+------+------+
 | id | age  | name |
 +----+------+------+
 |  4 | 19.5 | diny |
 +----+------+------+
 1 row in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.