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() 所述相同。

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

有关如何进行匹配的其它信息,参阅 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)    收藏  举报