MySQL 正则表达式（REGEXP）指南：语法、MySQL 8 函数、示例与最佳实践

1. 介绍

在 MySQL 中使用正则表达式

MySQL 是一个功能强大的数据库管理系统，可用于搜索和操作数据，并且它还支持 正则表达式 (REGEXP)，以实现更灵活的搜索。借助正则表达式，您可以高效地提取匹配特定模式的数据，以及对数据进行格式化或验证。

在 MySQL 中使用正则表达式的好处

使用 MySQL 正则表达式可带来以下好处：

• 高级搜索：轻松提取包含特定字符串或模式的数据。
• 检查数据完整性：对输入进行验证（例如，验证电子邮件地址格式）。
• 比 LIKE 操作符更强大：相较于使用通配符（ % 和 _ ）的 LIKE，REGEXP 允许您更灵活地定义复杂的搜索条件。

本文的目的与结构

本文将从 MySQL 正则表达式的基础语法讲起，逐步深入到高级用法、性能优化以及 MySQL 8.0 及以后版本新增的正则表达式函数。通过实用示例，内容力求即使是初学者也能轻松理解。

文章结构：

1. MySQL 正则表达式基础与语法
2. MySQL 8.0 及以后版本新增的正则表达式函数
3. 实用示例（验证、数据提取、格式转换）
4. 使用正则表达式时的关键注意事项（性能、安全）
5. FAQ（常见问题解答）
6. 总结

2. MySQL 中的正则表达式基础与语法

如何在 MySQL 中使用正则表达式

在 MySQL 中，您使用 REGEXP 运算符（或 RLIKE）来处理正则表达式。它的用法类似于 LIKE 运算符，用于判断列值是否匹配指定的正则模式。

基本语法

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

或

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE 是 REGEXP 的别名，两者行为相同。

示例
例如，搜索名称中包含字符 “山” 的记录：

SELECT * FROM users WHERE name REGEXP '山';

基本正则表达式模式

在 MySQL 正则表达式中，您可以使用如下模式：

与 LIKE 操作符的区别

MySQL 也提供 LIKE 操作符，常用于简单的模式匹配。然而，REGEXP 比 LIKE 更强大，支持更复杂的条件搜索。

示例 1：使用 LIKE 操作符

SELECT * FROM users WHERE name LIKE '%山%';

• 使用 LIKE 时，您可以搜索包含 山 的数据，但只能使用简单通配符（ % 和 _ ）。

示例 2：使用 REGEXP 运算符

SELECT * FROM users WHERE name REGEXP '^山';

• 使用 REGEXP 时，您只能提取名称 以 山 开头 的数据。

如何指定多个模式

您可以使用 | 在正则表达式中指定多个模式，实现 OR 条件。

示例：获取姓氏为 “佐藤” 或 “田中” 的用户

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

否定匹配

如果在方括号内使用 ^，可以检索“不包含指定字符”的数据。

示例：名称以除 “山” 之外的字符开头的数据

SELECT * FROM users WHERE name REGEXP '^[^山]';

在此示例中，它返回名称不以 山 开头的记录。

总结

• 在 MySQL 中，REGEXP 提供比 LIKE 更强大的模式搜索功能。
• 了解基本语法和模式（.、^、$、[]、*、+ 等）可以实现灵活的搜索。
• 了解它与 LIKE 的区别并适当地使用两者非常重要。
• 使用 | 可以进行多模式搜索。
• 使用否定字符类 [^ ] 可帮助搜索不以特定字符开头的数据。

3. MySQL 8.0 及以后新增的正则表达式函数

在 MySQL 8.0 中，除了传统的 REGEXP 运算符外，新增了四个函数，以实现更灵活的正则表达式搜索。这些函数可以获取匹配位置、提取子串以及进行替换，极大地扩展了在 MySQL 中使用正则表达式的可能性。

本节将详细说明每个 新正则表达式函数，并提供具体示例。

3.1 REGEXP_LIKE()

概述

REGEXP_LIKE() 与 REGEXP 运算符类似，用于检查列值是否 匹配指定的正则表达式模式。

语法

REGEXP_LIKE(column_name, 'regex_pattern' [, flags])

• column_name : 要搜索的列
• 'regex_pattern' : 用作条件的正则表达式模式
• flags (optional) : 大小写敏感性（使用 i 进行不区分大小写的匹配）

示例

“获取电子邮件以 gmail.com 或 yahoo.co.jp 结尾的用户”

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail\.com|@yahoo\.co\.jp)$');

“搜索包含 ‘admin’（不区分大小写）的用户名”

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

✅ REGEXP_LIKE() 与 REGEXP 运算符的作用相同，但它提供了更符合标准 SQL 的写法。

3.2 REGEXP_INSTR()

概述

REGEXP_INSTR() 返回正则匹配在字符串中出现的 位置（起始索引）。

语法

REGEXP_INSTR(column_name, 'regex_pattern' [, start_position, occurrence, return_option, flags])

• start_position（可选）：开始搜索的位置（默认：1）
• occurrence（可选）：返回第几次匹配（默认：1）
• return_option（可选）：0（返回起始位置）或 1（返回匹配的结束位置）
• flags（可选）：使用 i 进行不区分大小写的匹配

示例

“如果电话号码以 090 或 080 开头，获取起始位置”

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

“获取电子邮件地址中域名部分的起始位置”

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• 它返回 @ 所在的位置（例如，user@example.com 返回 5）。

✅ 使用 REGEXP_INSTR()，可以获取 位置信息，从而扩展字符串处理的可能性。

3.3 REGEXP_SUBSTR()

概述

REGEXP_SUBSTR() 提取 与正则表达式模式匹配的子串。

语法

REGEXP_SUBSTR(column_name, 'regex_pattern' [, start_position, occurrence, flags])

• occurrence（可选）：获取第 n 次匹配（默认：1）
• flags（可选）：大小写敏感性（使用 i 忽略大小写）

示例

“仅提取电子邮件地址的域名部分”

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

“获取消息中的第一个数字序列”

SELECT message, REGEXP_SUBSTR(message, '[0-9]+') AS first_number FROM logs;

✅ 使用 REGEXP_SUBSTR()，可以提取特定模式并用于数据的组织和转换。

3.4 REGEXP_REPLACE()

概述

REGEXP_REPLACE() 将匹配正则表达式模式的子串 替换为另一个字符串。

语法

REGEXP_REPLACE(column_name, 'regex_pattern', 'replacement' [, occurrence, flags])

• replacement : 用于替换匹配部分的字符串
• occurrence（可选）：仅替换第 n 次匹配（如果省略，则替换所有匹配）
• flags（可选）：使用 i 进行不区分大小写的匹配

Examples

“去除电话号码中的连字符（-）”

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

“去除 HTML 标签”

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

✅ 使用 REGEXP_REPLACE()，格式转换和数据清洗变得轻而易举。

3.5 小结

4. MySQL 正则表达式的实用示例

通过利用 MySQL 正则表达式，您可以简化实际数据处理任务，例如 数据验证、提取特定数据 和 格式转换。本节将介绍带有 SQL 代码和说明的实用案例。

4.1 电子邮件地址验证

概述

使用正则表达式检查数据库中存储的电子邮件地址是否符合有效格式。

使用的 SQL

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

说明

• ^[a-zA-Z0-9._%+-]+ → @ 前的一个或多个字母、数字、点、下划线或加号
• @[a-zA-Z0-9.-]+ → @ 后的域名
• \.[a-zA-Z]{2,}$ → 以至少 2 个字母的 顶级域名 (TLD) 结尾（例如 .com、.jp、.net）

使用此查询，您可以 排除无效的电子邮件地址（例如 user@@example.com、user@.com）。

4.2 检查日本电话号码格式

概述

确定常见的日本电话号码（例如 090-1234-5678、03-1234-5678）是否符合正确格式。

使用的 SQL

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

说明

• 0[789]0-[0-9]{4}-[0-9]{4} → 移动电话（如 090-xxxx-xxxx、080-xxxx-xxxx 等）
• 0[1-9]-[0-9]{4}-[0-9]{4} → 固定电话（如 03-xxxx-xxxx、06-xxxx-xxxx 等）

通过这种方式，正则表达式在 需要格式一致性 时非常有用。

4.3 信用卡号格式验证

概述

验证信用卡号（Visa、Mastercard、American Express 等）的格式。

使用的 SQL

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

说明

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa（16 位或 13 位）
• ^5[1-5][0-9]{14}$ → Mastercard（16 位）
• ^3[47][0-9]{13}$ → American Express（15 位）

使用此查询，您可以 过滤掉数据库中无效的卡号（例如长度不对或起始数字不正确）。

4.4 去除 HTML 标签

概述

如果用户提交的数据包含 HTML 标签，您可以将其移除并 转换为纯文本。

使用的 SQL

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

说明

• '<[^>]+>' → 删除 < 与 > 之间的所有 HTML 标签

示例

这对于 从用户评论和博客文章中剥离 HTML 标签 非常有用。

4.5 日本邮政编码格式验证

概述

验证日本邮政编码（例如 123-4567）是否符合正确格式。

使用的 SQL

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

说明

• ^[0-9]{3}-[0-9]{4}$ → “3 位数字-4 位数字” 格式的邮政编码

使用此正则表达式有助于 保持数据一致性并防止格式错误。

4.6 用户名过滤（检测禁用词）

概述

在注册时检查用户名是否包含系统保留的禁用词（例如 admin、root、system）。

使用的 SQL

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

说明

• admin|root|system → 检测包含这些词的用户名

此查询有助于防止普通用户使用系统管理员专用的名称。

摘要

• 正则表达式可用于 验证电子邮件地址、电话号码和信用卡号码。
• 也可用于 去除 HTML 标签和转换数据格式。
• 通过诸如 过滤禁用词和验证邮政编码 等任务，它们有助于提升数据库的安全性和一致性。

5. 使用正则表达式时的重要注意事项（性能与安全）

在 MySQL 中使用正则表达式可以实现强大的搜索和数据清洗。然而，不当使用可能导致性能下降并带来安全风险。本节阐述了安全高效使用 MySQL 正则表达式的关键注意事项。

5.1 正则表达式的性能优化

MySQL 的正则搜索虽然方便，但有一个缺点：索引通常不适用，这可能导致查询变慢。

提升性能的措施

1. 结合 LIKE 或 FULLTEXT 索引

   SELECT * FROM users WHERE email LIKE '%gmail.com';
   

2. 使用更简洁的正则表达式

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';
   

3. 先过滤目标数据

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

5.2 安全风险与对策

1. 正则表达式拒绝服务（ReDoS）

• 避免使用如 .* 之类的无限匹配。
• 使用 ^ 和 $ 限定搜索范围。
• 不要直接将用户输入传入 REGEXP。

2. 正则表达式与 SQL 注入

❌ 不安全的查询

SELECT * FROM users WHERE username REGEXP '$input';

✅ 安全的查询（使用占位符）

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 版本兼容性

摘要

• 因为 REGEXP 不使用索引，性能措施非常重要。
• 为防止 ReDoS（正则表达式拒绝服务），避免过度使用通配符。
• 为防止 SQL 注入，不要直接将用户输入用于 REGEXP。

6. 常见问题（FAQ）

以下是关于使用 MySQL 正则表达式（REGEXP）的常见问题。解决这些问题将帮助您更有效地使用正则。

6.1 在 MySQL 中可以使用哪些正则模式？

6.2 LIKE 与 REGEXP 有何区别？

✅ 因为 LIKE 更快，建议在简单搜索时使用 LIKE，在需要复杂匹配时使用 REGEXP。

6.3 MySQL 5.x 与 MySQL 8.x 在正则功能上有哪些差异？

6.4 正则表达式未如预期工作时该怎么办？

✅ 检查清单

1. 检查转义是否正确

   SELECT * FROM users WHERE email REGEXP '\.com$';
   

2. 尝试在 REGEXP_LIKE() 中使用 i 标志

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');
   

3. 检查数据编码

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 如何提升 REGEXP 查询的性能？

1. 先使用 LIKE 进行预过滤

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

2. 在适当的情况下使用索引

   ALTER TABLE users ADD FULLTEXT(email);
   

摘要

• REGEXP 比 LIKE 更强大，但必须注意性能。
• 在 MySQL 8.0 中，新增了正则函数（如 REGEXP_LIKE()），实现了更灵活的处理。
• 提升性能的方法：结合 LIKE 使用、利用索引、并设计简洁的正则模式。

7. 总结

MySQL 正则表达式（REGEXP）在数据搜索、验证和格式转换方面非常有用。本文介绍了 MySQL 正则基础、MySQL 8.0 及以后新增的功能、实用示例、重要注意事项以及常见问答。

7.1 关键要点

以下是最重要要点的快速回顾。

1. MySQL 正则表达式基础

• 使用 REGEXP 运算符 能够实现 比 LIKE 更灵活的模式匹配。
• 常见正则模式
• ^（开头），$（结尾），.（任意单字符），[]（字符类），+（一次或多次重复）等。

2. MySQL 8.0+ 中的新正则函数

在 MySQL 8.0 中，新增了以下四个函数，实现了更灵活的处理：

• REGEXP_LIKE()：REGEXP 运算符的替代方案
• REGEXP_INSTR()：获取匹配子串的起始位置
• REGEXP_SUBSTR()：提取匹配的子串
• REGEXP_REPLACE()：使用正则替换子串

3. 实用示例

• 电子邮件地址验证
• 电话号码和信用卡号检查
• 去除 HTML 标签
• 验证邮政编码格式
• 检测禁用词
• 数据清洗（去除空白、删除逗号等）

4. 使用正则表达式时的重要注意事项

• 性能优化
• 由于 REGEXP 不使用索引，可能导致 全表扫描。
• 可以通过结合 LIKE 或 FULLTEXT 索引来提升性能。
• 安全措施
• 为防止 ReDoS（正则表达式拒绝服务），避免过度使用 .*。
• 为防止 SQL 注入，使用 placeholders 。

5. 常见问答（FAQ）

• MySQL 支持的正则模式
• LIKE 与 REGEXP 的区别
• MySQL 5.x 与 8.x 的功能差异
• 正则未按预期工作时的排查
• 提升性能的方法

7.2 正则表达式使用最佳实践

1. 保持正则模式简洁

• 示例：避免过度使用 .*，并利用 ^（开头）和 $（结尾）。

2. 在适当情况下结合 LIKE 或 FULLTEXT

• 先使用 LIKE 进行预过滤，再使用 REGEXP 降低查询成本。

3. 使用 REGEXP_REPLACE() 进行数据清洗

• 示例：去除 HTML 标签并规范多余空格。

4. 充分利用 MySQL 8.0 及以上版本

• 使用 REGEXP_LIKE() 等新函数可以让 SQL 更易读。

5. 全面落实安全措施

• 不要直接将用户输入用于 REGEXP（防止 SQL 注入）。
• 对动态查询使用 placeholders 。

7.3 进一步学习资源

以下是帮助您深入学习 MySQL 正则表达式的参考资料。

官方文档

• MySQL 8.0 Regular Expression Reference
• MySQL 5.x Regular Expression Basics

7.4 最后说明

MySQL 正则表达式可用于多种场景，包括数据搜索、清洗和验证。但在使用时需了解其性能和安全方面的注意事项。

希望本文能帮助您在使用 MySQL 正则表达式时更加得心应手。尝试在项目中运用这些技巧吧！