MySQL使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配 文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单 词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵 活，可以应用于非常复杂的查询。

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

 

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

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

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

在 fruits 表中，查询 f_name 字段以字母‘b’开头的记录，SQL 语句如下： 

 SELECT * FROM fruits WHERE f_name REGEXP '^b';

 

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

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

 SELECT * FROM fruits WHERE f_name REGEXP '^be';

 

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

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

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

在 fruits 表中，查询 f_name 字段以字母‘y’结尾的记录，SQL 语句如下： 

 SELECT * FROM fruits WHERE f_name REGEXP 'y$';

 

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

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

SELECT * FROM fruits WHERE f_name REGEXP 'rry$';

 

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

• 用符号"."来替代字符串中的任意一个字符 

字符‘.’匹配任意一个字符。

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

SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

 

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

• 使用"*"和"+"来匹配多个字符 

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

在 fruits 表中，查询 f_name 字段值以字母‘b’开头且‘b’后面出现字母‘a’的 记录，SQL 语句如下： 

 SELECT * FROM fruits WHERE f_name REGEXP '^ba*';

 

 星号‘*’可以匹配任意多个字符，blackberry 和 berry 中字母 b 后面并没有出现字母 a，但是 也满足匹配条件。

在 fruits 表中，查询 f_name 字段值以字母‘b’开头且‘b’后面出现字母‘a’至 少一次的记录，SQL 语句如下：

 SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

 

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

• 匹配指定字符串 

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

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

SELECT * FROM fruits WHERE f_name REGEXP 'on';

 

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

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

 SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

 

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

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

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

SELECT * FROM fruits WHERE f_name LIKE 'on';

 

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

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

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

在 fruits 表中，查找 f_name 字段中包含字母‘o’或者‘t’的记录，SQL 语句如 下： 

SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

 

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

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

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

 SELECT * FROM fruits WHERE s_id REGEXP '[456]'; 

 

 在查询结果中，s_id 字段值中只要有 3 个数字中的 1 个即为匹配记录字段。 匹配集合“[456]”也可以写成“[4-6]”，即指定集合区间。例如，“[a-z]”表示集合区间为 从 a~z 的字母，“[0-9]”表示集合区间为所有数字。

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

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

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

 SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

 

返回记录中的 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 语句如下： 

SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

 

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

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

SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';

 

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