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