MySQL正则表达式查询
精华
小牛编辑
164浏览
2023-03-14
正则表达式主要用来查询和替换符合某个模式(规则)的文本内容。例如,从一个文件中提取电话号码,查找一篇文章中重复的单词、替换文章中的敏感语汇等,这些地方都可以使用正则表达式。正则表达式强大且灵活,常用于非常复杂的查询。
MySQL 中,使用 REGEXP 关键字指定正则表达式的字符匹配模式,其基本语法格式如下:
方括号
例如,a{2,} 表示字母 a 连续出现至少 2 次,也可以大于 2 次;a{2,4} 表示字母 a 连续出现最少 2 次,最多不能超过 4 次。
MySQL 中,使用 REGEXP 关键字指定正则表达式的字符匹配模式,其基本语法格式如下:
属性名 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、abcn |
+ | 匹配前面的字符 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、bbbbbbb |
字符串 {n,m} |
匹配前面的字符串至少 n 次, 至多 m 次 | 'b{2,4}' 匹配最少 2 个,最多 4 个 b | bbb、bbbb |
MySQL 中的正则表达式与 Java 语言、PHP 语言等编程语言中的正则表达式基本一致。
查询以特定字符或字符串开头的记录
字符^
用来匹配以特定字符或字符串开头的记录。
例 1
在 tb_students_info 表中,查询 name 字段以“J”开头的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '^J'; +----+------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+------+------+------+--------+-----------+ | 4 | Jane | 22 | 男 | 162 | 3 | | 5 | Jim | 24 | 女 | 175 | 2 | | 6 | John | 21 | 女 | 172 | 4 | +----+------+------+------+--------+-----------+ 3 rows in set (0.01 sec)
例 2
在 tb_students_info 表中,查询 name 字段以“Ji”开头的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '^Ji'; +----+------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+------+------+------+--------+-----------+ | 5 | Jim | 24 | 女 | 175 | 2 | +----+------+------+------+--------+-----------+ 1 row in set (0.00 sec)
查询以特定字符或字符串结尾的记录
字符$
用来匹配以特定字符或字符串结尾的记录。
例 3
在 tb_students_info 表中,查询 name 字段以“y”结尾的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP 'y$'; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | | 3 | Henry | 23 | 女 | 185 | 1 | | 7 | Lily | 22 | 男 | 165 | 4 | +----+-------+------+------+--------+-----------+ 3 rows in set (0.00 sec)
例 4
在 tb_students_info 表中,查询 name 字段以“ry”结尾的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP 'ry$'; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 3 | Henry | 23 | 女 | 185 | 1 | +----+-------+------+------+--------+-----------+ 1 row in set (0.00 sec)
替代字符串中的任意一个字符
字符.
用来替代字符串中的任意一个字符。
例 5
在 tb_students_info 表中,查询 name 字段值包含“a”和“y”,且两个字母之间只有一个字母的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP 'a.y'; +----+------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | +----+------+------+------+--------+-----------+ 1 row in set (0.00 sec)
匹配多个字符
字符*
和
+
都可以匹配多个该符号之前的字符。不同的是,
+
表示至少一个字符,而
*
可以表示 0 个字符。
例 6
在 tb_students_info 表中,查询 name 字段值包含字母“T”,且“T”后面出现字母“h”的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '^Th*'; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 9 | Thomas | 22 | 女 | 178 | 5 | | 10 | Tom | 23 | 女 | 165 | 5 | +----+--------+------+------+--------+-----------+ 2 rows in set (0.00 sec)
例 7
在 tb_students_info 表中,查询 name 字段值包含字母“T”,且“T”后面至少出现“h”一次的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '^Th+'; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 9 | Thomas | 22 | 女 | 178 | 5 | +----+--------+------+------+--------+-----------+ 1 row in set (0.00 sec)
匹配指定字符串
正则表达式可以匹配字符串。当表中的记录包含这个字符串时,就可以将该记录查询出来。指定多个字符串时,需要用|
隔开。只要匹配这些字符串中的任意一个即可。
例 8
在 tb_students_info 表中,查询 name 字段值包含字符串“an”的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP 'an'; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | | 4 | Jane | 22 | 男 | 162 | 3 | | 8 | Susan | 23 | 男 | 170 | 5 | +----+-------+------+------+--------+-----------+ 3 rows in set (0.00 sec)
例 9
在 tb_students_info 表中,查询 name 字段值包含字符串“an”或“en”的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP 'an|en'; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | | 2 | Green | 23 | 男 | 158 | 2 | | 3 | Henry | 23 | 女 | 185 | 1 | | 4 | Jane | 22 | 男 | 162 | 3 | | 8 | Susan | 23 | 男 | 170 | 5 | +----+-------+------+------+--------+-----------+ 5 rows in set (0.00 sec)注意:字符串与
|
之间不能有空格。因为,查询过程中,数据库系统会将空格也当作一个字符,这样就查询不出想要的结果。
匹配指定字符串中的任意一个
使用方括号[ ]
可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符,该记录就会被查询出来。例如,通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。
例 10
在 tb_students_info 表中,查询 name 字段值包含字母“i”或“o”的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '[io]'; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 5 | Jim | 24 | 女 | 175 | 2 | | 6 | John | 21 | 女 | 172 | 4 | | 7 | Lily | 22 | 男 | 165 | 4 | | 9 | Thomas | 22 | 女 | 178 | 5 | | 10 | Tom | 23 | 女 | 165 | 5 | | 11 | LiMing | 22 | 男 | 180 | 7 | +----+--------+------+------+--------+-----------+ 6 rows in set (0.00 sec)从查询结果可以看到,所有返回记录的 name 字段值都包含字母 i 或 o,或者两个都有。
方括号
[ ]
还可以指定集合的区间。例如,“[a-z]”表示从 a~z 的所有字母;“[0-9]”表示从 0~9 的所有数字;“[a-z0-9]”表示包含所有的小写字母和数字;“[a-zA-Z]”表示匹配所有字符。
例 11
在 tb_students_info 表中,查询 name 字段值中包含 1、2 或 3 的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '[123]'; Empty set (0.00 sec)匹配集合“[123]”也可以写成“[1-3]”,即指定集合区间。
匹配指定字符以外的字符
[^字符集合]
用来匹配不在指定集合中的任何字符。
例 12
在 tb_students_info 表中,查询 name 字段值包含字母 a~t 以外的字符的记录,SQL 语句和执行过程如下。mysql> SELECT * FROM tb_students_info -> WHERE name REGEXP '[^a-t]' ; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | | 3 | Henry | 23 | 女 | 185 | 1 | | 7 | Lily | 22 | 男 | 165 | 4 | | 8 | Susan | 23 | 男 | 170 | 5 | +----+-------+------+------+--------+-----------+ 4 rows in set (0.00 sec)
使用{n,}或者{n,m}来指定字符串连续出现的次数
字符串{n,}
表示字符串连续出现 n 次;
字符串{n,m}
表示字符串连续出现至少 n 次,最多 m 次。
例如,a{2,} 表示字母 a 连续出现至少 2 次,也可以大于 2 次;a{2,4} 表示字母 a 连续出现最少 2 次,最多不能超过 4 次。
例 13
在 tb_students_info 表中,查询 name 字段值出现字母‘e’ 至少 2 次的记录,SQL 语句如下:mysql> SELECT * FROM tb_students_info WHERE name REGEXP 'e{2,}'; +----+-------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+-------+------+------+--------+-----------+ | 2 | Green | 23 | 男 | 158 | 2 | +----+-------+------+------+--------+-----------+ 1 row in set (0.00 sec)
例 14
在 tb_students_info 表中,查询 name 字段值出现字符串“i” 最少 1 次,最多 3 次的记录,SQL 语句如下:mysql> SELECT * FROM tb_students_info WHERE name REGEXP 'i{1,3}'; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 5 | Jim | 24 | 女 | 175 | 2 | | 7 | Lily | 22 | 男 | 165 | 4 | | 11 | LiMing | 22 | 男 | 180 | 7 | +----+--------+------+------+--------+-----------+ 3 rows in set (0.00 sec)