MySQL - 正则表达式

MySQL：使用正则表达式进行高级模式匹配

正则表达式 (regex) 提供了一种强大而灵活的方式，用于搜索文本数据中的复杂模式，远远超出了 LIKE 运算符的简单通配符功能。MySQL 8+ 使用 Unicode 国际组件 (ICU) 库，该库提供了一个现代化、高性能且支持 Unicode 的正则表达式引擎。

REGEXP_LIKE() 函数

用于正则表达式匹配的主要函数是 REGEXP_LIKE()。如果输入字符串与模式匹配，则它返回 1（真），否则返回 0（假）。旧版运算符 REGEXP 和 RLIKE 现在是 REGEXP_LIKE() 的同义词。

语法

REGEXP_LIKE(expression, pattern [, match_type])

-- 或使用运算符语法:
expression REGEXP pattern

可选的 match_type 字符串允许您修改匹配行为（例如，'i' 表示不区分大小写的匹配）。

常见正则表达式模式

以下是一些在正则表达式模式中最常用的元字符和结构：

模式	描述
^	将匹配锚定在字符串的开头。
$	将匹配锚定在字符串的末尾。
.	匹配任何单个字符（换行符除外）。
[abc]	匹配集合中的任意一个字符 (a, b 或 c)。
[^abc]	匹配不在集合中的任意一个字符。
p1|p2	选择：匹配模式 p1 或模式 p2。
*	匹配前一个元素零次或多次。
+	匹配前一个元素一次或多次。
?	匹配前一个元素零次或一次。
{n}	匹配前一个元素恰好 n 次。
{n,m}	匹配前一个元素 n 到 m 次。
\d	匹配任意数字。等同于 [0-9]。
\s	匹配任意空白字符。
\w	匹配任意单词字符（字母数字 + 下划线）。

示例

让我们使用 products 表来演示这些模式。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL
);

INSERT INTO products (product_code, product_name) VALUES
('SKU-001A', 'Wireless Mouse'),
('SKU-002B', '104-Key Keyboard'),
('HW-003C', 'Webcam Pro'),
('HW-004D', 'Monitor 24 inch'),
('ACC-005', 'Mouse Pad');

查找产品代码以 ‘SKU’ 开头的产品：

SELECT * FROM products WHERE REGEXP_LIKE(product_code, '^SKU');

查找产品名称包含数字的产品：

SELECT * FROM products WHERE product_name REGEXP '\\d'; -- 在 SQL 字符串中需要双反斜杠进行转义

查找产品名称严格为 ‘Webcam Pro’ 或 ‘Wireless Mouse’ 的产品（区分大小写）：

SELECT * FROM products WHERE REGEXP_LIKE(product_name, '^(Webcam Pro|Wireless Mouse)$');

查找产品代码以字母结尾的产品（不区分大小写匹配）：

SELECT * FROM products WHERE REGEXP_LIKE(product_code, '[a-z]$', 'i');

高级正则表达式函数

除了简单的匹配之外，MySQL 还提供了使用正则表达式提取、替换和定位子字符串的函数。

REGEXP_SUBSTR() - 提取子字符串

提取字符串中与正则表达式模式匹配的部分。

-- 提取产品名称中的数字部分
SELECT product_name, REGEXP_SUBSTR(product_name, '\\d+') AS number_in_name
FROM products;

REGEXP_REPLACE() - 替换子字符串

用另一个字符串替换正则表达式模式的出现。

-- 通过将 '-' 替换为空字符串来标准化产品代码
SELECT product_code, REGEXP_REPLACE(product_code, '-', '') AS standardized_code
FROM products;

REGEXP_INSTR() - 查找子字符串位置

返回与正则表达式模式匹配的子字符串的起始位置（基于 1 的索引）。

-- 查找产品名称中第一个数字的位置
SELECT product_name, REGEXP_INSTR(product_name, '\\d') AS first_digit_position
FROM products
WHERE REGEXP_LIKE(product_name, '\\d');

性能和最佳实践

• 索引： 标准数据库索引（如 B-树）通常对正则表达式搜索无效，特别是对于未锚定在字符串开头的模式（^）。这可能导致全表扫描，并在大表上导致性能缓慢。
• LIKE vs. REGEXP： 对于简单的前缀搜索（'word%'），LIKE 显著更快，因为它可以使用索引。仅当模式复杂性要求时才使用 REGEXP。
• 全文搜索： 对于在大段文本中搜索自然语言词汇和短语，请考虑使用 MySQL 的全文搜索功能，这些功能是专门为此目的设计和优化的。
• 复杂性： 注意编写过于复杂或低效的正则表达式模式（例如，过度回溯），因为它们可能会消耗大量的 CPU 资源。

正则表达式函数和运算符表

序号	函数或运算符	描述
1	REGEXP_LIKE()	检查字符串是否匹配正则表达式的主要函数。返回 1 或 0。
2	REGEXP / RLIKE	REGEXP_LIKE() 函数的同义运算符。常用于 WHERE 子句中。
3	NOT REGEXP	REGEXP_LIKE() 的否定。如果模式不匹配则返回 1，否则返回 0。
4	REGEXP_INSTR()	返回与正则表达式匹配的子字符串的起始索引（基于 1）。
5	REGEXP_REPLACE()	将匹配正则表达式的子字符串替换为新字符串。
6	REGEXP_SUBSTR()	返回与正则表达式匹配的子字符串。