MySQL全面瓦解之查询的正则匹配详解

吾爱主题阅读：131 2024-04-01 23:51:15 评论：0

概述

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

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

匹配模式分析

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

匹配模式	描述
^	匹配输入字符串的开始位置。如果设置了 REGEXP 对象的 Multiline 属性，^ 也匹配 ' ' 或 ' ' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了REGEXP 对象的 Multiline 属性，$ 也匹配 ' ' 或 ' ' 之前的位置。
.	匹配除 " " 之外的任何单个字符。要匹配包括 ' ' 在内的任何字符，请使用 '[. ]' 的模式。
[….]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	非匹配字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
[n-m]	匹配m到n之间的任意单个字符，例如[0-9],[a-z],[A-Z]
*	匹配前面的子表达式零次或多次。例如，a* 能匹配 "a" 以及 "ab"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，'a+' 能匹配 "ab" 以及 "abc"，但不能匹配 "a"。+ 等价于 {1,}。
?	匹配前面的子表达式一次或多次。例如，'a?' 能匹配 "ab" 以及 "a"。? 等价于 {0,1}。
a1\| a2\|a3	匹配 a1 或 a2 或 a3。例如，'z\|food' 能匹配 "z" 或 "food"。'(z\|f)ood' 则匹配 "zood" 或 "food"。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,}	匹配前面的子表达式n次到多次。例如，'o{2,}' 不仅能匹配 "food" ，也能匹配 "foood"。
{n,m}	n 和 m 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
{,m}	匹配前面的子表达式0次到m次
(….)	元素组合，即将模式元素组成单一元素，例如（do）*意思是匹配0个多或多个do

匹配模式^

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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 set

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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 set

匹配模式$

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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

匹配模式.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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 set

匹配模式[...]

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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 server 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

匹配模式[^...]

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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 set

匹配模式[n-m]

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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 set

匹配模式 *

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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

匹配模式 +

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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 set

匹配模式 ?

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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

匹配模式 a1| a2|a3

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 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 set

匹配模式 {n} {n,} {n,m} {,m}

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

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 34 35 36 37 38 39 40 41 42 43 44 45 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 set

匹配模式(...)

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

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 34 35 36 37 38 39 40 41 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 set

匹配特殊字符 \

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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