MySQL使用正则表达式匹配

支持的匹配模式（常用）

定位符

符号	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
[[:<:]]	词的开始
[[:>:]]	词的结尾

匹配模式

模式	描述
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用'[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	负值字符集合。匹配未包含的任意字符。例如， `'[^abc]'` 可以匹配 "plain" 中的'p'。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，'z\|food' 能匹配 "z" 或 "food"。'(z\|f)ood'则匹配 "zood" 或 "food"。

匹配字符类

模式	描述
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表符（同`[\\t]`）
[:digit:]	任意数字（同[0-9]）
[:lower:]	任意小写字母（同[a-z]）
[:upper:]	任意大写字母（同[A-Z]）
[:space:]	包括空格在内的任意空白字符（同 `[\\f\\n\\t\\r\\v]`）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:print:]	任意可打印字符
[:graph:]	与[:print:]相同，但不包括空格
[:punct:]	既不在 [:alnum:] 又不在 [:cntrl:] 中的任意字符
[:xdigit:]	任意十六进制数字（同 [a-fA-F0-9]）

量词修饰

元字符	描述
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
?	0个或1个匹配（等于 {0, 1}）
	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
	不少于指定数目的匹配
	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。m不超过255

创建测试数据

drop table if exists test_t;
create table test_t(id int,name varchar(100));
insert into test_t values
(1,'zhangsan'),
(2,'lisi'),
(3,'wangwu'),
(4,'zhangsan123'),
(5,'sunqi'),
(6,'zhannnngsan'),
(7,'wujiu'),
(8,'Zhangsan'),
(9,'li si');

MySQL 5.7使用正则表达式

使用 REGEXP 进行模式匹配

REGEXP 是用于进行正则表达式匹配的运算符。

REGEXP 用于检查一个字符串是否匹配指定的正则表达式模式，以下是 REGEXP 运算符的基本语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';

使用 RLIKE 进行模式匹配

RLIKE 是 MySQL 中用于进行正则表达式匹配的运算符，与 REGEXP 是一样的，RLIKE 和 REGEXP 可以互换使用，没有区别。

以下是使用 RLIKE 进行正则表达式匹配的基本语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name RLIKE 'pattern';

说明：在MySQL中，like、rlike和regexp都不区分大小写，如果需要区分，可以在WHERE后添加关键字段binary

查询示例：

匹配开头是z的name记录，不区分大小写

mysql> select id,name from test_t where name regexp '^z';
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | zhangsan    |
|    4 | zhangsan123 |
|    6 | zhannnngsan |
|    8 | Zhangsan    |
+------+-------------+
4 rows in set (0.00 sec)

匹配开头是z的name记录，区分大小写

mysql> select id,name from test_t where binary name regexp '^z';
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | zhangsan    |
|    4 | zhangsan123 |
|    6 | zhannnngsan |
+------+-------------+
3 rows in set (0.00 sec)

匹配开头z结尾至少一个数字的name记录

mysql> select id,name from test_t where name regexp '^z.*[0-9]+$';
+------+-------------+
| id   | name        |
+------+-------------+
|    4 | zhangsan123 |
+------+-------------+
1 row in set (0.00 sec)

匹配开头是z或者s

select id,name from test_t where name rlike '^z|^s';
select id,name from test_t where name rlike '^(z|s)';
select id,name from test_t where name rlike '^[zs]';

+------+-------------+
| id   | name        |
+------+-------------+
|    1 | zhangsan    |
|    4 | zhangsan123 |
|    5 | sunqi       |
|    6 | zhannnngsan |
|    8 | Zhangsan    |
+------+-------------+

匹配name字段中至少2个n

mysql> select id,name from test_t where name rlike 'n{2}';
+------+-------------+
| id   | name        |
+------+-------------+
|    6 | zhannnngsan |
+------+-------------+
1 row in set (0.00 sec)

MySQL 8.0使用正则表达式

MySQL 8.0+ 引入了regexp_replace，regexp_like，regexp_instr，regexp_substr四个正则函数。

REGEXP_LIKE

语法：

REGEXP_LIKE(expr, pat[, match_type])

如果字符串 expr 与模式 pat 指定的正则表达式匹配，则返回1，否则返回0。如果 expr 或 pat 为 NULL，则返回值为 NULL。

模式可以是扩展的正则表达式，其语法在正则表达式语法中进行了讨论。模式不需要是文字字符串，它也可以指定为字符串表达式或表列。

可选的 match_type 参数是一个字符串，它可以包含指定如何执行匹配的以下任何字符的组合：

match_type 参数值	说明
c	区分大小写
i	不区分大小写（默认）
m	多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符（不匹配换行符）
n	dotall 模式，字符 . 匹配换行符。默认行为是 . 匹配在换行符处停止。
u	仅 Unix 的行尾。只有换行符被 `.`，`^` 和`$`匹配运算符识别为行尾。

如果在 match_type 中指定了指定矛盾选项的字符，则最右边的字符优先。

默认情况下，正则表达式操作在决定字符类型和执行比较时使用 expr 和 pat 参数的字符集和排序规则。如果参数具有不同的字符集或排序规则，则使用强制性转换，参见https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html。可以使用显式指定排序规则参数，以更改比较行为。

使用示例：

name字段开始是z的记录，不区分大小写

mysql> select id,name from test_t where regexp_like(name,'^z');   -- 不带match_type选项，默认不区分大小写
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | zhangsan    |
|    4 | zhangsan123 |
|    6 | zhannnngsan |
|    8 | Zhangsan    |
+------+-------------+
4 rows in set (0.00 sec)

name字段开始是Z的记录，区分大小写

mysql> select id,name from test_t where regexp_like(name,'^Z','c');
+------+----------+
| id   | name     |
+------+----------+
|    8 | Zhangsan |
+------+----------+
1 row in set (0.00 sec)

name字段开始是Z的记录，区分大小写

mysql> select id,name from test_t where regexp_like(name,'^Z' COLLATE utf8mb4_0900_as_cs);
+------+----------+
| id   | name     |
+------+----------+
|    8 | Zhangsan |
+------+----------+
1 row in set (0.00 sec)

match_type 可以用 c 或 i 字符指定是否区分大小写。

如果有参数是二进制字符串，则即使 match_type 包含 i 字符，参数也会作为二进制字符串以区分大小写的方式处理。

注意，MySQL 在字符串中使用 C 转义语法（例如，\n 表示换行符）。如果希望 expr 或 pat 参数包含一个斜杠文本 \，则必须写两个斜杠 \\。除非启用了 NO_BACKSLASH_ESCAPES sql_mode，在这种情况下不会使用转义符。

mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line','n');
+--------------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line','n') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+
 
mysql> SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
|                         1 |
+---------------------------+
mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
|                              0 |
+--------------------------------+

REGEXP_INSTR

语法：

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

返回字符串 expr 中与模式 pat 指定的正则表达式匹配的子字符串的起始位置，如果不匹配，则返回 0。如果 expr 或 pat 为 NULL，则返回值为 NULL。字符位置从 1 开始。

参数说明：

pos：expr 中开始搜索的位置。如果省略，则默认值为 1。
occurrence：要搜索匹配的第几个匹配项。如果省略，则默认值为1。
return_option：
- 如果该值为 0，REGEXP_INSTR() 返回匹配子字符串的第一个字符的位置。
- 如果此值为 1，REGEXP_INSTR() 返回匹配子字符串后面的位置。
- 如果省略，则默认值为0。
match_type：指定如何执行匹配的字符串。其含义与 REGEXP_LIKE() 所述相同。

有关如何进行匹配的其它信息，参阅 REGEXP_LIKE() 部分的描述。

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 0);
+---------------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 0) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 1);
+---------------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 1) |
+---------------------------------------------+
|                                           4 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+
1 row in set (0.00 sec)

REGEXP_REPLACE

语法：

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

将字符串 expr 中与模式 pat 指定的正则表达式匹配的字符串替换为字符串 repl，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL。

参数说明：

pos：expr 中开始搜索的位置。如果省略，则默认值为1。
occurrence：要替换第几个匹配项。如果省略，默认值为0，意思是 “替换所有匹配项”。
match_type：指定如何执行匹配的字符串，其含义与 REGEXP_LIKE() 所述相同。

在 MySQL 8.0.17 之前，此函数返回的结果使用了 UTF-16 字符集；在 MySQL 8.0.17 及更高版本中，使用了搜索匹配的表达式的字符集和排序规则。参见 bug#94203，bug#29308212。

有关如何进行匹配的其它信息，参阅 REGEXP_LIKE() 部分的描述。

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+
1 row in set (0.00 sec)

REGEXP_SUBSTR

语法：

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

返回字符串 expr 中与模式 pat 指定的正则表达式匹配的子字符串，如果不匹配，则返回 NULL。如果 expr 或 pat 为 NULL，则返回值为 NULL。

参数说明：

pos：expr 中开始搜索的位置。如果省略，则默认值为1。
occurrence：要搜索第几个匹配项。如果省略，则默认值为1。
match_type：指定如何执行匹配的字符串，其含义与 REGEXP_LIKE() 所述相同。

有关如何进行匹配的其它信息，参阅 REGEXP_LIKE() 部分的描述。

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+
1 row in set (0.00 sec)

正则表达式兼容性注意事项

在 MySQL 8.0.4 之前，MySQL 使用 Henry Spencer 库来支持正则表达式操作，而不是 Unicode 国际组件（International Components for Unicode，ICU）。以下讨论描述了 Spencer 和 ICU 库之间可能影响应用程序的差异。

多字节安全问题

对于 Spencer 库，REGEXP 和 RLIKE 运算符以字节方式工作，因此它们不是多字节安全的，并且可能会对多字节字符集产生意外结果。此外，这些运算符通过字节值比较字符，重音字符可能不会被比较为相等，即使给定的排序规则将它们视为相等。

mysql> SELECT REGEXP_INSTR('🍣🍣b', 'b');
+--------------------------+
| REGEXP_INSTR('??b', 'b') |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4);
+--------------------------------+
| REGEXP_INSTR('??bxxx', 'b', 4) |
+--------------------------------+
|                              5 |
+--------------------------------+
1 row in set (0.00 sec)

Unicode 基本多语言平台中的字符，包括大多数现代语言使用的字符，在这方面是安全的：

mysql> SELECT REGEXP_INSTR('бжb', 'b');
+----------------------------+
| REGEXP_INSTR('бжb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT REGEXP_INSTR('עבb', 'b');
+----------------------------+
| REGEXP_INSTR('עבb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT REGEXP_INSTR('µå周çб', '周');
+------------------------------------+
| REGEXP_INSTR('µå周çб', '周')       |
+------------------------------------+
|                                  3 |
+------------------------------------+
1 row in set (0.00 sec)

但对于 Emoji 表情符号，如前两个例子中使用的（U+1F363）不包括在基本多语言平台中，而是包括在 Unicode 的补充多语言平台中。

当 REGEXP_SUBSTR() 或类似函数开始在字符中间搜索时，表情符号和其它 4 字节字符可能会出现另一个问题。

以下示例中的两个语句中的每一个都从第一个参数中的第二个 2 字节位置开始。第一条语句适用于仅由 2 字节（BMP）字符组成的字符串。第二条语句包含 4 字节字符，这些字符在结果中被错误地解释，因为前两个字节被剥离，因此字符数据的其余部分未对齐。

mysql> SELECT REGEXP_SUBSTR('周周周周', '.*', 2);
+----------------------------------------+
| REGEXP_SUBSTR('周周周周', '.*', 2)     |
+----------------------------------------+
| 周周周                                 |
+----------------------------------------+
1 row in set (0.00 sec)
 
mysql> SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2);
+--------------------------------+
| REGEXP_SUBSTR('????', '.*', 2) |
+--------------------------------+
| ?㳟揘㳟揘㳟揘                  |
+--------------------------------+
1 row in set (0.00 sec)

操作符

对于 . 操作符，Spencer 库匹配字符串表达式中任何位置的行尾字符（回车符、换行符），包括在中间。而要将字符串中间的行结束符字符与 ICU 匹配，要指定 n 匹配控制字符。

单词边界

Spencer 库支持单词开始和单词结束边界标记 [[:<:]] and [[:>:]] 。对于 ICU，可以使用 \b 来匹配单词边界，要写两个将反斜杠，因为 MySQL 将其解释为字符串中的转义符。

排序规则元素括号表达式

Spencer 库支持排序元素括号表达式 [.characters.]。ICU 没有。

重复计数

对于重复计数 {n} 和 {m,n}，Spencer 库的最大值为 255。ICU 没有这样的限制，尽管可以通过设置 regexp_time_limit 系统变量来限制匹配引擎的最大步骤数。

括号字符

ICU 将小括号解释为元字符。若要指定文字的左括号或右括号，在正则表达式中必须转义。

mysql> SELECT REGEXP_LIKE('(', '(');
ERROR 3691 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE('(', '\\(');
+-------------------------+
| REGEXP_LIKE('(', '\\(') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE(')', ')');
ERROR 3691 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE(')', '\\)');
+-------------------------+
| REGEXP_LIKE(')', '\\)') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

ICU 也将方括号解释为元字符，但只有左方括号需要转义才能用作文字字符。

mysql> SELECT REGEXP_LIKE('[', '[');
ERROR 3696 (HY000): The regular expression contains an unclosed bracket expression.
mysql> SELECT REGEXP_LIKE('[', '\\[');
+-------------------------+
| REGEXP_LIKE('[', '\\[') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE(']', ']');
+-----------------------+
| REGEXP_LIKE(']', ']') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

参考资料

MySQL 正则表达式

https://www.runoob.com/mysql/mysql-regexp.html

正则模式

https://www.cnblogs.com/javahr/p/9780650.html

MySQL 正则表达式函数

https://blog.csdn.net/wzy0623/article/details/130791381

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax

posted @ 2025-05-30 01:34 kahnyao 阅读(250) 评论(0) 收藏举报

刷新页面返回顶部

knlbase

MySQL使用正则表达式匹配

支持的匹配模式（常用）

MySQL 5.7使用正则表达式

MySQL 8.0使用正则表达式

REGEXP_LIKE

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

正则表达式兼容性注意事项

多字节安全问题

操作符

单词边界

排序规则元素括号表达式

重复计数

括号字符

参考资料