MySQL REGEXP：用正则表达式查询（附带实例）

正则表达式通常用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。

例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式。下表列出了 REGEXP 操作符中常用的字符匹配。

表：正则表达式常用字符匹配

选项	说明	例子	匹配值示例
^	匹配文本的开始字符串	'^b' 匹配以字母 b 开头的字符串	book, big, banana, bike
$	匹配文本的结束字符串	'st$' 匹配以 st 结尾的字符串	test, resist, persist
.	匹配任何单个字符	'b.t' 匹配任何有字符 b 或 t 的字符串	bit, bat, but, bite
*	匹配 0 个或多个在它前面的字符	'f*n' 匹配字符 n 前面有任意个字符f的字符串	fn, fan, faan, fabcn
+	匹配前面的字符 1 次或多次	'ba+' 匹配以 b 开头后面紧跟至少一个 a 的字符串	ba, bay, bare, battle
<字符串>	匹配包含指定的字符串的文本	'fa' 匹配包含 fa 的字符串	fan, afa, faad
[字符集合]	匹配字符集合中的任何一个字符	'[xz]' 匹配包含 x 或者 z 的字符串	dizzy, zebra, x-ray, extra
[^]	匹配不在括号中的任何字符	'[^abc]' 匹配任何不包含 a、b 或 c 的字符串	desk, fox, f8ke
字符串{n,}	匹配前面的字符串至少 n 次	b{2} 匹配 2 个或更多的 b	bbb, bbbb, bbbbbbbb
字符串{n,m}	匹配前面的字符串至少 n 次，至多 m 次。如果 n 为 0，此参数为可选参数	b{2,4} 匹配含最少 2 个，最多 4 个 b 的字符串	bb, bbbb, bbbbb

下面将详细介绍如何在 MySQL 中使用正则表达式。

MySQL查询以特定字符或字符串开头的记录

字符“^”匹配以特定字符或者字符串开头的文本。

【实例 1】在表 fruits 中，查询 f_name 字段中以字母“b”开头的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
+------+-------+------------+---------+
| f_id | s_id  | f_name     | f_price |
+------+-------+------------+---------+
| b1   | 101   | blackberry | 10.20   |
| b2   | 104   | berry      | 7.60    |
| t1   | 102   | banana     | 10.30   |
+------+-------+------------+---------+

表 fruits 中有 3 条记录的 f_name 字段值是以字母 b 开头的，因此返回结果中有 3 条记录。

【实例 2】在表 fruits 中，查询 f_name 字段中以“be”开头的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^be';
+------+-------+--------+---------+
| f_id | s_id  | f_name | f_price |
+------+-------+--------+---------+
| b2   | 104   | berry  | 7.60    |
+------+-------+--------+---------+

只有 berry 是以“be”开头的，因此查询结果中只有 1 条记录。

MySQL查询以特定字符或字符串结尾的记录

字符“$”匹配以特定字符或者字符串结尾的文本。

【实例 3】在表 fruits 中，查询 f_name 字段中以字母“y”结尾的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
+------+-------+------------+---------+
| f_id | s_id  | f_name     | f_price |
+------+-------+------------+---------+
| b1   | 101   | blackberry | 10.20   |
| b2   | 104   | berry      | 7.60    |
| c0   | 101   | cherry     | 3.20    |
| m2   | 105   | xbabay     | 2.60    |
+------+-------+------------+---------+

表 fruits 中有 4 条记录的 f_name 字段值是以字母“y”结尾的，因此返回结果中有 4 条记录。

【实例 4】在表 fruits 中，查询 f_name 字段中以字符串“rry”结尾的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
+------+-------+------------+---------+
| f_id | s_id  | f_name     | f_price |
+------+-------+------------+---------+
| b1   | 101   | blackberry | 10.20   |
| b2   | 104   | berry      | 7.60    |
| c0   | 101   | cherry     | 3.20    |
+------+-------+------------+---------+

表 fruits 中有 3 条记录的 f_name 字段值是以字符串“rry”结尾的，因此返回结果中有 3 条记录。

MySQL用符号“.”来替代字符串中的任意一个字符

字符“.”匹配任意一个字符。

【实例 5】在表 fruits 中，查询 f_name 字段中包含字母“a”与“g”且两个字母之间只有一个字母的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
+------+-------+------0-+---------+
| f_id | s_id  | f_name | f_price |
+------+-------+--------+---------+
| bs1  | 102   | orange | 11.20   |
| ml   | 106   | mango  | 15.70   |
+------+-------+--------+---------+

在查询语句中，“a.g”指定匹配字符串中要有字母 a 和 g，且两个字母之间包含单个字符，并不限定匹配的字符的位置和所在查询字符串的总长度，因此 orange 和 mango 都符合匹配条件。

MySQL使用“*”和“+”来匹配多个字符

星号（*）匹配前面的字符任意多次，包括 0 次。加号（+）匹配前面的字符至少一次。

【实例 6】在表 fruits 中，查询 f_name 字段中以字母“b”开头且“b”后面出现紧跟字母“a”的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
+------+-------+------------+---------+
| f_id | s_id  | f_name     | f_price |
+------+-------+------------+---------+
| b1   | 101   | blackberry | 10.20   |
| b2   | 104   | berry      | 7.60    |
| t1   | 102   | banana     | 10.30   |
+------+-------+------------+---------+

星号（*）可以匹配任意多个字符，因此 blackberry 和 berry 中字母“b”后面虽然没有出现字母“a”，但是也满足匹配条件。

【实例 7】在表 fruits 中，查询 f_name 字段中以字母“b”开头且“b”后面字母“a”至少出现一次的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
+------+-------+--------+---------+
| f_id | s_id  | f_name | f_price |
+------+-------+--------+---------+
| t1   | 102   | banana | 10.30   |
+------+-------+--------+---------+

MySQL匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如果要匹配多个字符串，则多个字符串之间使用“|”隔开。

【实例 8】在表 fruits 中，查询 f_name 字段中包含字符串“on”的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| bs2  | 105  | melon   |   8.20  |
| l2   | 104  | lemon   |   6.40  |
| o2   | 103  | coconut |   9.20  |
+------+------+---------+---------+

可以看到，f_name 字段的 melon、lemon 和 coconut 3 个值中都包含有字符串“on”，满足匹配条件。

【实例 9】在表 fruits 中，查询 f_name 字段中包含字符串“on”或者“ap”的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
+------+-------+---------+---------+
| f_id | s_id  | f_name  | f_price |
+------+-------+---------+---------+
| a1   | 101   | apple   | 5.20    |
| a2   | 103   | apricot | 2.20    |
| bs2  | 105   | melon   | 8.20    |
| l2   | 104   | lemon   | 6.40    |
| o2   | 103   | coconut | 9.20    |
| t2   | 102   | grape   | 5.30    |
+------+-------+---------+---------+

可以看到，f_name 字段的 melon、lemon 和 coconut 3 个值中都包含有字符串“on”，apple 和 apricot 中包含字符串“ap”，满足匹配条件。

之前介绍过，LIKE 运算符也可以匹配指定的字符串，但与 REGEXP 不同，LIKE 匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。REGEXP 在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP 将会找到它，相应的行也会被返回。对比结果如【实例 10】所示。

【实例 10】在表 fruits 中，使用 LIKE 运算符查询 f_name 字段中值为“on”的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name LIKE 'on';
Empty set (0.00 sec)

f_name 字段中没有值为“on”的记录，返回结果为空。读者可以体会一下两者的区别。

MySQL匹配指定字符中的任意一个

方括号（[]）指定一个字符集合，只要匹配其中任何一个字符，即为所查找的文本。

【实例 11】在表 fruits 中，查找 f_name 字段中包含字母“o”或者“t”的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a2   | 103  | apricot |    2.20 |
| bs1  | 102  | orange  |   11.20 |
| bs2  | 105  | melon   |    8.20 |
| l2   | 104  | lemon   |    6.40 |
| m1   | 106  | mango   |   15.70 |
| m3   | 105  | xxtt    |   11.60 |
| o2   | 103  | coconut |    9.20 |
+------+------+---------+---------+

由查询结果可以看到，所有返回的记录的 f_name 字段的值中都包含有字母 o 或者 t，或者两个都有。

方括号（[]）还可以指定数值集合。

【实例 12】在表 fruits 中，查询 s_id 字段中包含 4、5 或者 6 的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
+------+------+--------+----------+
| f_id | s_id | f_name | f_price  |
+------+------+--------+----------+
| b2   |  104 | berry  |    7.60  |
| bs2  |  105 | melon  |    8.20  |
| l2   |  104 | lemon  |    6.40  |
| m1   |  106 | mango  |   15.70  |
| m2   |  105 | xbabay |    2.60  |
| m3   |  105 | xxtt   |   11.60  |
+------+------+--------+----------+

在查询结果中，s_id 字段值中只要有 4、5、6 这 3 个数字中的 1 个，即满足匹配条件。

匹配集合“[456]”也可以写成“[4-6]”，即指定集合区间。例如，“[a-z]”表示集合区间为 a~z 的字母，“[0-9]”表示集合区间为所有数字。

MySQL匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符。

【实例 13】在表 fruits 中，查询 f_id 字段中包含字母 a~e 和数字 1~2 以外字符的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| b5   | 107  | xxxx    |   3.60  |
| bs1  | 102  | orange  |  11.20  |
| bs2  | 105  | melon   |   8.20  |
| c0   | 101  | cherry  |   3.20  |
| l2   | 104  | lemon   |   6.40  |
| m1   | 106  | mango   |  15.70  |
| m2   | 105  | xbabay  |   2.60  |
| m3   | 105  | xxtt    |  11.60  |
| o2   | 103  | coconut |   9.20  |
| t1   | 102  | banana  |  10.30  |
| t2   | 102  | grape   |   5.30  |
| t4   | 107  | xbababa |   3.60  |
+------+------+---------+---------+

返回记录中的 f_id 字段值中包含指定字母和数字以外的值，如 s、m、o、t 等，这些字母均不在 a~e 与 1~2 中，满足匹配条件。

MySQL使用{n,}或者{n,m}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配 n 次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于 n 次，不多于 m 次。

例如，“a{2,}”表示字母“a”至少连续出现 2 次，也可以大于 2 次；“a{2,4}”表示字母“a”最少连续出现 2 次，最多不能超过 4 次。

【实例 14】在表 fruits 中，查询 f_name 字段中字母“x”至少出现 2 次的记录，SQL 语句如下：

mysql>  SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5   | 107  | xxxx   |   3.60  |
| m3   | 105  | xxtt   |  11.60  |
+------+------+--------+---------+

可以看到，f_name 字段的“xxxx”包含了 4 个字母“x”，“xxtt”包含两个字母“x”，均满足匹配条件。

【实例 15】在表 fruits 中，查询 f_name 字段中字符串“ba”最少出现 1 次、最多出现 3 次的记录，SQL 语句如下：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
+------+------+---------+---------+
|f_id  | s_id | f_name  | f_price |
+------+------+---------+---------+
| m2   |  105 | xbabay  |   2.60  |
| t1   |  102 | banana  |  10.30  |
| t4   |  107 | xbababa |   3.60  |
+------+------+---------+---------+

可以看到，“ba”在 f_name 字段的 xbabay 值中出现了 2 次，在 banana 中出现了 1 次，在 xbababa 中出现了 3 次，均满足匹配条件。