mysql 正则表达式

MySQL 是一种广泛使用的关系型数据库管理系统，正则表达式是一种能够描述字符串模式的嵌入式语言。MySQL 能够通过正则表达式实现对数据库中字符串的查询和操作。本篇文章将介绍 MySQL 正则表达式的基本语法、函数和示例。

正则表达式语法

MySQL 嵌入了 POSIX 正则表达式，支持的语法和常规正则表达式是一样的。以下是一些基本的语法：

• ^ 字符串的开头
• $ 字符串的结尾
• . 任何一个字符
• * 零个或多个匹配前面的字符或子表达式
• + 至少一个匹配前面的字符或子表达式
• ? 零个或一个匹配前面的字符或子表达式
• [] 匹配方括号中的任何一个字符
• [^] 不匹配方括号中的任何一个字符
• () 捕获匹配的字串

字符组

字符组表示在方括号中包含的任意字符之一。比如 [abc] 表示匹配单个字符 a，b 或 c。

SELECT * FROM mytable WHERE name REGEXP '^[abc]';

在上面的语句中，将返回名字以 a，b 或 c 开头的记录。

表达式组

表达式组使用括号 () 包括，并被视为单个操作对象。可用括号捕获单个子串，可在之后通过 $n 引用该子串。例如：

SELECT * FROM mytable WHERE name REGEXP '^(ho|ri)sey?$';

在上面的语句中，将返回名字为 horse 或 horsy，或者名字以 ho 或 ri 开头的记录。

特殊字符

有些字符在正则表达式中有特殊的含义，需要转义后才能正确匹配。以下是一些常见的特殊字符及其转义方式：

• * 转义为 \*
• + 转义为 \+
• { 转义为 \{
• } 转义为 \}
• ( 转义为 \(
• ) 转义为 \)
• . 转义为 \.

常用函数

MySQL 提供了许多正则表达式相关的函数，可以用来进行查询、替换和提取。以下是一些常用的函数：

• REGEXP 匹配正则表达式
• REGEXP_LIKE 匹配正则表达式
• REGEXP_REPLACE 替换正则表达式
• REGEXP_SUBSTR 提取正则表达式

REGEXP

REGEXP 函数用于对字段进行正则表达式匹配：

SELECT * FROM mytable WHERE name REGEXP '^h.*y$';

这将返回名字以 h 开头，以 y 结尾的记录。

REGEXP_LIKE

REGEXP_LIKE 函数也用于正则表达式匹配，但语义更接近于 SQL 的 LIKE 操作符：

SELECT * FROM mytable WHERE REGEXP_LIKE(name, '^h.*y$');

这将返回名字以 h 开头，以 y 结尾的记录。

REGEXP_REPLACE

REGEXP_REPLACE 函数用于正则表达式替换：

SELECT REGEXP_REPLACE(name, '(o|a)', '*') as newname FROM mytable;

这将返回一个新的结果集，其中所有名字中的 o 或 a 都被替换为 *。

REGEXP_SUBSTR

REGEXP_SUBSTR 函数用于提取正则表达式匹配的字串：

SELECT REGEXP_SUBSTR(name, '^h.*y$') as newname FROM mytable;

这将返回一个新的结果集，其中所有名字中符合正则表达式 ^h.*y$ 的字串被提取出来。

示例

下面是一些具体的示例，展示了如何使用正则表达式查询、替换和提取数据。

查询

假设有一个表 mytable，其中保存了名字和邮箱，现在我们想查询所有名字包含 son 或 sun，且邮箱为 gmail.com 的记录：

SELECT * FROM mytable WHERE (name REGEXP 's[ou]n') AND (email REGEXP '@gmail\\.com$');

在上面的语句中，s[ou]n 表示匹配名字中包含 son 或 sun 的记录，@gmail\\.com$ 表示匹配邮箱以 @gmail.com 结尾的记录。

替换

假设要将所有以 a 开头，以 b 结尾的单词替换为 abc，可以使用 REGEXP_REPLACE 函数：

SELECT REGEXP_REPLACE(word, '^a.*b$', 'abc') as newword FROM mytable;

在上面的语句中，^a.*b$ 表示匹配以 a 开头，以 b 结尾的单词，被替换为 abc。

提取

假设有一个表 t，其中保存了一些文本，现在我们想提取其中包含 http:// 或 https:// 的 URL：

SELECT REGEXP_SUBSTR(text, 'https?://[a-zA-Z0-9./]+') as url FROM t;

在上面的语句中，https?://[a-zA-Z0-9./]+ 表示匹配以 http:// 或 https:// 开头，后面接一些字母、数字、.、/ 的 URL。

结论

MySQL 正则表达式是一个非常强大和灵活的工具，能够帮助我们查询、替换和提取数据，同时还能提高代码的可读性和可维护性。本篇文章介绍了 MySQL 正则表达式的基本语法、函数和示例，希望能够帮助大家更好地使用这个工具。