首页 > 编程笔记 > MySQL笔记 阅读：226

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 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
  2. +------+-------+------------+---------+
  3. | f_id | s_id  | f_name     | f_price |
  4. +------+-------+------------+---------+
  5. | b1   |  101  | blackberry |   10.20 |
  6. | b2   |  104  | berry      |    7.60 |
  7. | t1   |  102  | banana     |   10.30 |
  8. +------+-------+------------+---------+
fruits 表中有 3 条记录的 f_name 字段值是以字母 b 开头的，返回结果有 3 条记录。

【实例】在 fruits 表中，查询 f_name 字段以“be”开头的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP '^be';
  2. +------+------+--------+---------+
  3. | f_id | s_id | f_name | f_price |
  4. +------+------+--------+---------+
  5. | b2   |  104 | berry  |    7.60 |
  6. +------+------+--------+---------+
只有 berry 是以“be”开头的，所以查询结果中只有 1 条记录。

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

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

【实例】在 fruits 表中，查询 f_name 字段以字母“y”结尾的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
  2. +------+------+------------+---------+
  3. | f_id | s_id | f_name     | f_price |
  4. +------+------+------------+---------+
  5. | b1   |  101 | blackberry |   10.20 |
  6. | b2   |  104 | berry      |    7.60 |
  7. | c0   |  101 | cherry     |    3.20 |
  8. | m2   |  105 | xbabay     |    2.60 |
  9. +------+------+------------+---------+
fruits 表中有 4 条记录的 f_name 字段值是以字母“y”结尾的，返回结果有 4 条记录。

【实例】在 fruits 表中，查询 f_name 字段以字符串“rry”结尾的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
  2. +------+------+------------+---------+
  3. | f_id | s_id | f_name     | f_price |
  4. +------+------+------------+---------+
  5. | b1   |  101 | blackberry |   10.20 |
  6. | b2   |  104 | berry      |    7.60 |
  7. | c0   |  101 | cherry     |    3.20 |
  8. +------+------+------------+---------+
fruits 表中有 3 条记录的 f_name 字段值是以字符串“rry”结尾的，返回结果有 3 条记录。

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

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

【实例】在 fruits 表中，查询 f_name 字段值包含字母“a”与“g”且两个字母之间只有一个字母的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
  2. +------+------+--------+---------+
  3. | f_id | s_id | f_name | f_price |
  4. +------+------+--------+---------+
  5. | bs1  |  102 | orange |   11.20 |
  6. | m1   |  106 | mango  |   15.70 |
  7. +------+------+--------+---------+
查询语句中“a.g”指定匹配字符中要有字母 a 和 g，且两个字母之间包含单个字符，并不限定匹配的字符的位置和所在查询字符串的总长度，因此 orange 和 mango 都符合匹配条件。

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

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

【实例】在 fruits 表中，查询 f_name 字段值以字母“b”开头且“b”后面出现字母“a”的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
  2. +------+------+------------+---------+
  3. | f_id | s_id | f_name     | f_price |
  4. +------+------+------------+---------+
  5. | b1   |  101 | blackberry |   10.20 |
  6. | b2   |  104 | berry      |    7.60 |
  7. | t1   |  102 | banana     |   10.30 |
  8. +------+------+------------+---------+
星号“*”可以匹配任意多个字符，blackberry 和 berry 中字母 b 后面并没有出现字母 a，但是也满足匹配条件。

【实例】在 fruits 表中，查询 f_name 字段值以字母“b”开头且“b”后面出现字母“a”至少一次的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
  2. +------+------+--------+---------+
  3. | f_id | s_id | f_name | f_price |
  4. +------+------+--------+---------+
  5. | t1   |  102 | banana |  10.30  |
  6. +------+------+--------+---------+
“a+”匹配字母 a 至少一次，只有 banana 满足匹配条件。

匹配指定字符串

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

【实例】在 fruits 表中，查询 f_name 字段值包含字符串“on”的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
  2. +------+------+---------+---------+
  3. | f_id | s_id | f_name  | f_price |
  4. +------+------+---------+---------+
  5. | bs2  |  105 | melon   |   8.20  |
  6. | l2   |  104 | lemon   |   6.40  |
  7. | o2   |  103 | coconut |   9.20  |
  8. +------+------+---------+---------+
可以看到，f_name 字段的 melon、lemon 和 coconut 3个值中都包含有字符串“on”，满足匹配条件。

【实例】在 fruits 表中，查询 f_name 字段值包含字符串“on”或者“ap”的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
  2. +------+------+---------+---------+
  3. | f_id | s_id | f_name  | f_price |
  4. +------+------+---------+---------+
  5. | a1   |  101 | apple   |    5.20 |
  6. | a2   |  103 | apricot |    2.20 |
  7. | bs2  |  105 | melon   |    8.20 |
  8. | l2   |  104 | lemon   |    6.40 |
  9. | o2   |  103 | coconut |    9.20 |
  10. | t2   |  102 | grape   |    5.30 |
  11. +------+------+---------+---------+
可以看到，f_name 字段的 melon、lemon 和 coconut 3 个值中都包含有字符串“on”，apple和apricot值中包含字符串“ap”，满足匹配条件。

实际上，LIKE 运算符也可以匹配指定的字符串，但与 REGEXP（正则表达式）不同，LIKE 匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。REGEXP 在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP 将会找到它，相应的行也会被返回。对比结果如下面的实例所示。

【实例】在 fruits 表中，使用 LIKE 运算符查询 f_name 字段值为“on”的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name LIKE 'on';
  2. Empty set (0.00 sec)
f_name 字段没有值为“on”的记录，返回结果为空。读者可以体会一下两者的区别。

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

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

【实例】在 fruits 表中，查找 f_name 字段中包含字母“o”或者“t”的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
  2. +------+------+----------+---------+
  3. | f_id | s_id | f_name   | f_price |
  4. +------+------+----------+---------+
  5. | a2   |  103 | apricot  |    2.20 |
  6. | bs1  |  102 | orange   |   11.20 |
  7. | bs2  |  105 | melon    |    8.20 |
  8. | l2   |  104 | lemon    |    6.40 |
  9. | m1   |  106 | mango    |   15.70 |
  10. | m3   |  105 | xxtt     |   11.60 |
  11. | o2   |  103 | coconut  |    9.20 |
  12. +------+------+----------+---------+
由查询结果可以看到，所有返回的记录的 f_name 字段的值中都包含有字母 o 或者 t，或者两个都有。

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

【实例】在 fruits 表中，查询 s_id 字段中包含 4、5 或者 6 的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
  2. +------+------+--------+---------+
  3. | f_id | s_id | f_name | f_price |
  4. +------+------+--------+---------+
  5. | b2   |  104 | berry  |    7.60 |
  6. | bs2  |  105 | melon  |    8.20 |
  7. | l2   |  104 | lemon  |    6.40 |
  8. | m1   |  106 | mango  |   15.70 |
  9. | m2   |  105 | xbabay |    2.60 |
  10. | m3   |  105 | xxtt   |   11.60 |
  11. +------+------+--------+---------+
在查询结果中，s_id 字段值中只要有 3 个数字中的 1 个，即为匹配记录字段。

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

匹配指定字符以外的字符

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

【实例】在 fruits 表中，查询 f_id 字段中包含字母 a~e 和数字 1~2 以外字符的记录，SQL 语句如下：
  1. mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
  2. +------+------+----------+----------+
  3. | f_id | s_id | f_name   | f_price  |
  4. +------+------+----------+----------+
  5. | b5   |  107 | xxxx     |    3.60  |
  6. | bs1  |  102 | orange   |   11.20  |
  7. | bs2  |  105 | melon    |    8.20  |
  8. | c0   |  101 | cherry   |    3.20  |
  9. | l2   |  104 | lemon    |    6.40  |
  10. | m1   |  106 | mango    |   15.70  |
  11. | m2   |  105 | xbabay   |    2.60  |
  12. | m3   |  105 | xxtt     |   11.60  |
  13. | o2   |  103 | coconut  |    9.20  |
  14. | t1   |  102 | banana   |   10.30  |
  15. | t2   |  102 | grape    |    5.30  |
  16. | t4   |  107 | xbababa  |    3.60  |
  17. +------+------+----------+----------+
返回记录中的 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 语句如下： 
  1. mysql>  SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
  2. +------+------+--------+---------+
  3. | f_id | s_id | f_name | f_price |
  4. +------+------+--------+---------+
  5. | b5   |  107 | xxxx   |   3.60  |
  6. | m3   |  105 | xxtt   |  11.60  |
  7. +------+------+--------+---------+
可以看到，f_name 字段的“xxxx”值包含了 4 个字母“x”，“xxtt”值包含两个字母“x”，均为满足匹配条件的记录。

【实例】在 fruits 表中，查询 f_name 字段值出现字符串“ba”最少 1 次、最多 3 次的记录，SQL 语句如下： 
  1. mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
  2. +------+------+---------+---------+
  3. | f_id | s_id | f_name  | f_price |
  4. +------+------+---------+---------+
  5. | m2   |  105 | xbabay  |    2.60 |
  6. | t1   |  102 | banana  |   10.30 |
  7. | t4   |  107 | xbababa |    3.60 |
  8. +------+------+---------+---------+
可以看到，f_name 字段的“xbabay”值中“ba”出现了 2 次，“banana”值中出现了 1 次，“xbababa”值中出现了 3 次，都是满足匹配条件的记录。

相关文章