MySQL 正则表达式用法、模式和注意事项详解_正则表达式

mysql 正则表达式详细说明

mysql 支持正则表达式（regular expression），用于在查询中进行复杂的模式匹配。它主要通过 regexp 或 rlike 操作符实现（两者功能相同），以及 mysql 8.0 及以上版本提供的扩展函数。以下内容将逐步解释其用法、模式和注意事项。

语法示例：

select * from 表名 where 列名 regexp '模式';

mysql 正则表达式基于 posix 扩展正则表达式（ere）标准。以下是常见元字符和模式：

基本匹配：
- .：匹配任意单个字符（除换行符外），例如 'a.c' 匹配 "abc" 或 "aac"。
- ^：匹配字符串开头，例如 '^start' 匹配以 "start" 开头的字符串。
- $：匹配字符串结尾，例如 'end$' 匹配以 "end" 结尾的字符串。
字符类：
- [abc]：匹配 "a"、"b" 或 "c" 中的任意一个字符。
- [^abc]：匹配除 "a"、"b"、"c" 外的任意字符。
- [a-z]：匹配任意小写字母。
量词（控制匹配次数）：
- *：匹配前一个元素零次或多次，例如 'ab*c' 匹配 "ac"、"abc"、"abbc" 等。
- +：匹配前一个元素一次或多次，例如 'ab+c' 匹配 "abc"、"abbc"，但不匹配 "ac"。
- ?：匹配前一个元素零次或一次，例如 'ab?c' 匹配 "ac" 或 "abc"。
- {n}：匹配前一个元素恰好 $n$ 次，例如 'a{3}' 匹配 "aaa"。
- {n,}：匹配前一个元素至少 $n$ 次，例如 'a{2,}' 匹配 "aa"、"aaa" 等。
- {n,m}：匹配前一个元素 $n$ 到 $m$ 次，例如 'a{2,4}' 匹配 "aa"、"aaa" 或 "aaaa"。
分组和或操作：
- |：表示“或”，例如 'apple|banana' 匹配 "apple" 或 "banana"。
- ()：用于分组，例如 '(ab)+' 匹配 "ab"、"abab" 等。
转义字符：
- 使用 \\ 转义特殊字符，例如 '\\.' 匹配字面点号 "."。

从 mysql 8.0 开始，引入了更强大的函数：

regexp_like()：检查是否匹配模式，语法 regexp_like(字符串, '模式')。
```
select regexp_like('hello', '^h') as result; -- 返回 1
```
regexp_instr()：返回匹配子串的起始位置，语法 regexp_instr(字符串, '模式')。
```
select regexp_instr('abc123', '[0-9]+') as position; -- 返回 4（数字起始位置）
```

regexp_substr()：提取匹配的子串，语法 regexp_substr(字符串, '模式')。

select regexp_substr('email: test@example.com', '[a-za-z0-9._%+-]+@[a-za-z0-9.-]+\\.[a-za-z]{2,}') as email; -- 返回 "test@example.com"

regexp_replace()：替换匹配的子串，语法 regexp_replace(字符串, '模式', '替换文本')。

select regexp_replace('phone: 123-456', '[0-9]{3}-[0-9]{3}', '***-***') as masked; -- 返回 "phone: ***-***"

匹配数字序列：查询包含至少 3 位数字的字符串。
```
select * from data where content regexp '[0-9]{3,}';
```

验证邮箱格式：检查是否符合标准邮箱模式。

select * from users where email regexp '^[a-za-z0-9._%+-]+@[a-za-z0-9.-]+\\.[a-za-z]{2,}$';

提取和替换：在 mysql 8.0 中，提取日期部分。

select regexp_substr('2023-10-05 event', '[0-9]{4}-[0-9]{2}-[0-9]{2}') as date; -- 返回 "2023-10-05"

区分大小写：mysql 默认不区分大小写。如需区分，使用 binary 关键字或 regexp_like 的选项，例如 regexp_like(str, 'pattern', 'c')（'c' 表示大小写敏感）。
性能影响：正则表达式比简单 like 查询更慢，避免在大表上频繁使用。优先考虑索引或全文搜索。
版本兼容性：
- regexp/rlike 支持所有 mysql 版本。
- 高级函数（如 regexp_replace）仅限 mysql 8.0+。
- 旧版本（如 mysql 5.7）可能不支持某些模式（如 unicode 字符类）。
转义规则：在 sql 字符串中，需双重转义特殊字符。例如，匹配点号使用 '\\.'，而非单反斜杠。
错误处理：无效模式会导致查询错误。测试模式前，可使用简单值验证。