MySQL系列之正则函数regexp

1. 概述

MySQL5系列最高版本号是5.7；没有MySQL 6，7系列。

MySQL 8.0+才引入regexp_replace，regexp_like，regexp_instr，regexp_substr四个函数，在低于此版本的MySQL客户端执行这四个函数，报错：FUNCTION regexp_xxx does not exist

2. 正则

2.1 like/not like

like本来是模糊匹配，此处引申一下。like（not like），MySQL用于模式匹配的运算符，将列与给定值进行比较，并返回与模式相同（不同）的列。功能远远没有正则强大。

2.2 regexp/not regexp

MySQL regexp匹配所使用的字符，与常规正则表达式相同：

模式	模式匹配什么
^	匹配字符串开头
$	匹配字符串结尾
.	匹配任意单个字符
[...]	匹配方括号间列出的任意字符
[^...]	匹配方括号间未列出的任意字符
p1|p2|p3	交替匹配任意 p1 或 p2 或 p3
*	匹配前面的元素的零次或多次
+	匹配前面的元素的一次或多次
{n}	匹配前面的元素 n 次
{m,n}	匹配前面的元素 m 至 n 次

示例： 查询所有以元音开头并以ok结尾的name：SELECT name FROM person WHERE name REGEXP '^[aeiou].*ok$';

2.3 regexp_replace

regexp_replace()函数用于通过匹配字符来替换给定的字符串。
语法：REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

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

常规使用：select regexp_replace('str', 'character', 'new_character');
将str里面含有character的字符替换成new_character。
例：

# 将name字段中的数字去掉
select regexp_replace(name, '[0-9]', '') FROM table where  date='20220101'

2.4 regexp_like

语法：regexp_like(expr, pat[, match_type])
用于比较给定的字符串expr是否满足模式pat，如果字符串相同则返回 1，否则返回 0。可选项match_type接受如下字符：

• c：大小写敏感匹配；
• i：大小写不敏感匹配，默认不敏感；
• m：多行模式。识别字符串中的行终止符。默认是仅在字符串表达式的开头和结尾匹配行终止符
• n：与.行终止符匹配
• u：仅匹配 Unix 的行结尾。只有换行符被识别为以 .，^和$结尾的行

如执行：select regexp_like('MCA', 'mca');输出1；
执行：select regexp_like('MCA', 'mca', 'c');输出0；

2.5 regexp_substr

语法：regexp_substr(expr, pat[, pos[, occurrence[, match_type]]])

用于从给定的字符串str中返回满足match_type子字符串。

select regexp_substr('str', 'match_type', occurrence, position);

如执行：select regexp_substr('my sql function', '[a-z]+', 1, 3);，输出：function

2.6 regexp_instr

语法：regexp_instr(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

regexp_instr()函数返回源字符串expr与正则表达式模式pat匹配的子字符串的起始索引。索引从 1 开始。如果不匹配，则返回 0。

• pos 为可选参数，标识开始匹配的位置，默认为 1
• occurrence 为可选参数，标识匹配的次数，默认为 1
• return_option 为可选参数，指定返回值的类型。如果为 0，则返回匹配的第一个字符的位置。如果为 1，则返回匹配的最后一个位置，默认为0

执行select regexp_instr('dog cat dog', 'dog');返回1；
selectregexp_instr('dog cat dog', 'dog', 2);返回9；

2.7 regexp_extract

非MySQL函数，而是Hive函数。

3. 参考

官方文档：https://dev.mysql.com/doc/refman/8.0/en/regexp.html

4. 相关文章

• Linux中mysql的安装|配置|操作|卸载
• 联合索引、索引覆盖和索引下推详解