MySQL中使用正则表达式（非常详细，附带大量实例）

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

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

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

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

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	'^b' 匹配以字母 b 开头的字符串	book, big, banana, bike
$	匹配文本的结束字符	'st$' 匹配以 st 结尾的字符串	test, resist, persist
.	匹配任何单个字符	'b.t' 匹配任何 b 和 t 之间有一个字符的字符串	bit, bat, but, bite
*	匹配零个或多个在它前面的字符	'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, bbbbb, bbbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次，至多 m 次。如果 n 为 0，此参数为可选参数	b{2,4} 匹配含最少 2 个、最多 4 个 b 的字符串	bb, bbb, bbbb

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

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

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

【实例】在 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 条记录。

【实例】在 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 条记录。

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

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

【实例】在 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 条记录。

【实例】在 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 条记录。

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

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

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

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

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

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

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

【实例】在 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，但是也满足匹配条件。

【实例】在 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  |
+------+------+--------+---------+

“a+”匹配字母 a 至少一次，只有 banana 满足匹配条件。

匹配指定字符串

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

【实例】在 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”，满足匹配条件。

【实例】在 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 将会找到它，相应的行也会被返回。对比结果如下面的实例所示。

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

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

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

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

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

【实例】在 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，或者两个都有。

方括号“[]”还可以指定数值集合。

【实例】在 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 字段值中只要有 3 个数字中的 1 个，即为匹配记录字段。

匹配集合“[456]”也可以写成“[4-6]”，即指定集合区间。例如，“[a-z]”表示集合区间为从a~z的字母，“[0-9]”表示集合区间为所有数字，读者可以自行修改本例测试一下。

匹配指定字符以外的字符

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

【实例】在 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 之间，满足匹配条件。

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

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

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

【实例】在 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”，均为满足匹配条件的记录。

【实例】在 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 |
+------+------+---------+---------+

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