MySQL 正则表达式

正则表达式概述

MySQL 支持正则表达式（Regular Expression），允许你进行复杂的模式匹配。正则表达式比 LIKE 操作符更强大，可以实现更精确和灵活的文本搜索。

MySQL 正则表达式操作符：REGEXP或RLIKE：匹配正则表达式NOT REGEXP或NOT RLIKE：不匹配正则表达式REGEXP_LIKE()：函数形式的正则匹配REGEXP_REPLACE()：正则替换REGEXP_SUBSTR()：正则提取

基本语法

-- 基本正则表达式语法
SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';
SELECT column_name FROM table_name WHERE column_name RLIKE 'pattern';
SELECT column_name FROM table_name WHERE column_name NOT REGEXP 'pattern';

注意：MySQL 的正则表达式默认是大小写不敏感的，可以使用 BINARY 关键字使其大小写敏感。

示例数据准备

-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    code VARCHAR(20),
    description TEXT,
    email VARCHAR(100)
);

-- 插入测试数据
INSERT INTO products VALUES
(1, 'iPhone 14 Pro', 'IP14P-001', 'Apple iPhone 14 Pro 128GB', '[email protected]'),
(2, 'Samsung Galaxy S23', 'SG23-002', 'Samsung Galaxy S23 256GB', '[email protected]'),
(3, 'MacBook Air M2', 'MBA-M2-003', 'Apple MacBook Air with M2 chip', '[email protected]'),
(4, 'Dell XPS 13', 'DX13-004', 'Dell XPS 13 Laptop Intel i7', '[email protected]'),
(5, 'iPad Pro 11', 'IPD11-005', 'Apple iPad Pro 11-inch 2022', '[email protected]'),
(6, 'Surface Pro 9', 'SP9-006', 'Microsoft Surface Pro 9 Tablet', '[email protected]'),
(7, 'AirPods Pro 2', 'APP2-007', 'Apple AirPods Pro 2nd Generation', '[email protected]'),
(8, 'Galaxy Buds2 Pro', 'GBP2-008', 'Samsung Galaxy Buds2 Pro Wireless', '[email protected]');

基本正则表达式模式

字符匹配

-- 匹配包含 'Pro' 的产品
SELECT name FROM products WHERE name REGEXP 'Pro';

-- 匹配以 'Apple' 开头的描述
SELECT name, description FROM products WHERE description REGEXP '^Apple';

-- 匹配以 '.com' 结尾的邮箱
SELECT name, email FROM products WHERE email REGEXP '\.com$';

模式	说明	示例
^	行的开始	^Apple 匹配以 Apple 开头
$	行的结束	.com$ 匹配以 .com 结尾
.	任意单个字符	a.c 匹配 abc, adc 等
\	转义字符	. 匹配字面意思的点

字符类

-- 匹配包含数字的产品代码
SELECT name, code FROM products WHERE code REGEXP '[0-9]';

-- 匹配包含字母 A-F 的代码
SELECT name, code FROM products WHERE code REGEXP '[A-F]';

-- 匹配不包含数字的产品名称
SELECT name FROM products WHERE name REGEXP '[^0-9]';

模式	说明	示例
[abc]	匹配 a、b 或 c	[aeiou] 匹配元音字母
[a-z]	匹配 a 到 z 的任意字母	[0-9] 匹配任意数字
[^abc]	不匹配 a、b 或 c	[^0-9] 匹配非数字
[:alnum:]	字母和数字	[[:alnum:]] 匹配字母数字
[:alpha:]	字母	[[:alpha:]] 匹配字母
[:digit:]	数字	[[:digit:]] 匹配数字

量词

-- 匹配包含一个或多个数字的代码
SELECT name, code FROM products WHERE code REGEXP '[0-9]+';

-- 匹配包含 0 个或多个字母 P 的产品
SELECT name FROM products WHERE name REGEXP 'P*';

-- 匹配恰好包含 2 个数字的代码
SELECT name, code FROM products WHERE code REGEXP '[0-9]{2}';

-- 匹配包含 2-4 个连续字母的产品名
SELECT name FROM products WHERE name REGEXP '[A-Za-z]{2,4}';

量词	说明	示例
*	0 次或多次	ab* 匹配 a, ab, abb 等
+	1 次或多次	ab+ 匹配 ab, abb 等
?	0 次或 1 次	ab? 匹配 a 或 ab
{n}	恰好 n 次	a{3} 匹配 aaa
{n,}	至少 n 次	a{2,} 匹配 aa, aaa 等
{n,m}	n 到 m 次	a{2,4} 匹配 aa, aaa, aaaa

高级正则表达式模式

分组和选择

-- 匹配 iPhone 或 iPad
SELECT name FROM products WHERE name REGEXP '(iPhone|iPad)';

-- 匹配 Apple 或 Samsung 品牌
SELECT name, description FROM products 
WHERE description REGEXP '(Apple|Samsung)';

-- 匹配产品代码格式：字母-数字-数字
SELECT name, code FROM products 
WHERE code REGEXP '^[A-Z]+-[0-9]+-[0-9]+$';

预定义字符类

-- 使用预定义字符类
SELECT name FROM products WHERE name REGEXP '[[.space.]]';  -- 匹配空格
SELECT name FROM products WHERE name REGEXP '[[:digit:]]';  -- 匹配数字
SELECT name FROM products WHERE name REGEXP '[[:alpha:]]';  -- 匹配字母
SELECT name FROM products WHERE name REGEXP '[[:alnum:]]';  -- 匹配字母数字

复杂模式示例

-- 匹配邮箱格式
SELECT name, email FROM products 
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- 匹配产品代码格式（字母开头，包含数字和连字符）
SELECT name, code FROM products 
WHERE code REGEXP '^[A-Z]+[0-9]*-[0-9]+$';

-- 匹配包含版本号的产品（数字后跟可选的小数点和数字）
SELECT name FROM products 
WHERE name REGEXP '[0-9]+\.?[0-9]*';

正则表达式函数

REGEXP_LIKE() 函数

-- REGEXP_LIKE 函数提供更多选项
SELECT name FROM products 
WHERE REGEXP_LIKE(name, 'pro', 'i');  -- 'i' 表示忽略大小写

-- 多行模式
SELECT name, description FROM products 
WHERE REGEXP_LIKE(description, '^Apple.*Pro$', 'm');  -- 'm' 表示多行模式

REGEXP_REPLACE() 函数

-- 替换文本中的模式
SELECT 
    name,
    REGEXP_REPLACE(name, '[0-9]+', 'X') as name_no_numbers,
    REGEXP_REPLACE(email, '@.*', '@company.com') as masked_email
FROM products;

-- 格式化产品代码
SELECT 
    code,
    REGEXP_REPLACE(code, '([A-Z]+)([0-9]+)-([0-9]+)', '\\1_\\2_\\3') as formatted_code
FROM products;

REGEXP_SUBSTR() 函数

-- 提取匹配的子字符串
SELECT 
    name,
    REGEXP_SUBSTR(name, '[0-9]+') as extracted_number,
    REGEXP_SUBSTR(email, '[^@]+') as username
FROM products;

-- 提取产品代码中的数字部分
SELECT 
    code,
    REGEXP_SUBSTR(code, '[0-9]+') as first_number,
    REGEXP_SUBSTR(code, '[0-9]+', 1, 2) as second_number  -- 第2个匹配
FROM products;

REGEXP_INSTR() 函数

-- 查找模式在字符串中的位置
SELECT 
    name,
    REGEXP_INSTR(name, '[0-9]+') as number_position,
    REGEXP_INSTR(email, '@') as at_position
FROM products;

实际应用场景

1. 数据验证

-- 验证邮箱格式
SELECT name, email,
    CASE 
        WHEN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' 
        THEN '有效邮箱' 
        ELSE '无效邮箱' 
    END as email_status
FROM products;

-- 验证产品代码格式
SELECT name, code,
    CASE 
        WHEN code REGEXP '^[A-Z]{2,4}[0-9]*-[0-9]{3}$' 
        THEN '标准格式' 
        ELSE '非标准格式' 
    END as code_format
FROM products;

2. 数据清理

-- 查找包含特殊字符的数据
SELECT name FROM products 
WHERE name REGEXP '[^A-Za-z0-9 ]';  -- 包含非字母数字空格的字符

-- 查找格式不一致的数据
SELECT name, code FROM products 
WHERE code NOT REGEXP '^[A-Z]+-[0-9]+-[0-9]+$';  -- 不符合标准格式

-- 标准化数据格式
UPDATE products 
SET code = REGEXP_REPLACE(code, '([A-Z]+)([0-9]+)([0-9]+)', '\\1-\\2-\\3')
WHERE code REGEXP '^[A-Z]+[0-9]+[0-9]+$';

3. 搜索和过滤

-- 搜索特定型号的产品
SELECT name FROM products 
WHERE name REGEXP '(iPhone|iPad|MacBook).*[0-9]+';

-- 查找专业版产品
SELECT name FROM products 
WHERE name REGEXP 'Pro[^a-z]';

-- 按品牌分类
SELECT 
    name,
    CASE 
        WHEN description REGEXP '^Apple' THEN 'Apple'
        WHEN description REGEXP '^Samsung' THEN 'Samsung'
        WHEN description REGEXP '^Dell' THEN 'Dell'
        WHEN description REGEXP '^Microsoft' THEN 'Microsoft'
        ELSE 'Other'
    END as brand
FROM products;

4. 文本分析

-- 提取产品型号
SELECT 
    name,
    REGEXP_SUBSTR(name, '[0-9]+') as model_number,
    REGEXP_SUBSTR(name, '(Pro|Air|Max)') as product_line
FROM products;

-- 分析邮箱域名
SELECT 
    REGEXP_SUBSTR(email, '@(.+)', 1, 1, '', 1) as domain,
    COUNT(*) as count
FROM products 
GROUP BY REGEXP_SUBSTR(email, '@(.+)', 1, 1, '', 1);

性能优化

正则表达式性能优化建议：尽量使用简单的模式，避免复杂的回溯在可能的情况下，使用 LIKE 而不是正则表达式将正则表达式与其他条件结合使用考虑使用全文索引进行文本搜索避免在大数据集上使用复杂的正则表达式

-- 性能优化示例
-- 不好的做法：直接使用复杂正则表达式
SELECT * FROM large_table 
WHERE description REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- 更好的做法：先用简单条件过滤，再用正则表达式
SELECT * FROM large_table 
WHERE description LIKE '%@%'  -- 先用索引友好的条件
  AND description REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

常见错误和注意事项

常见错误：忘记转义特殊字符（如点号 .）不理解贪婪匹配和非贪婪匹配在大数据集上使用复杂正则表达式导致性能问题混淆 LIKE 和 REGEXP 的语法

转义字符

-- 错误：没有转义点号
SELECT * FROM products WHERE email REGEXP '.com$';  -- 匹配任意字符 + com

-- 正确：转义点号
SELECT * FROM products WHERE email REGEXP '\.com$';  -- 匹配字面意思的 .com

-- 其他需要转义的字符
SELECT * FROM products WHERE code REGEXP '\\+';     -- 匹配加号
SELECT * FROM products WHERE code REGEXP '\\?';     -- 匹配问号
SELECT * FROM products WHERE code REGEXP '\\*';     -- 匹配星号

大小写敏感性

-- 默认情况下，MySQL 正则表达式不区分大小写
SELECT * FROM products WHERE name REGEXP 'pro';     -- 匹配 Pro, PRO, pro

-- 使用 BINARY 使其区分大小写
SELECT * FROM products WHERE name REGEXP BINARY 'pro';  -- 只匹配 pro

-- 使用 REGEXP_LIKE 函数控制大小写
SELECT * FROM products WHERE REGEXP_LIKE(name, 'pro', 'c');  -- 区分大小写
SELECT * FROM products WHERE REGEXP_LIKE(name, 'pro', 'i');  -- 不区分大小写

正则表达式参考

常用模式

邮箱：^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$手机号：^1[3-9][0-9]{9}$身份证：^[1-9][0-9]{5}(19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01])[0-9]{3}[0-9Xx]$IP地址：^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$URL：^https?://[A-Za-z0-9.-]+.[A-Za-z]{2,}(/.*)?$

字符类速查

字符类	等价表达式	说明
[[:alnum:]]	[A-Za-z0-9]	字母和数字
[[:alpha:]]	[A-Za-z]	字母
[[:digit:]]	[0-9]	数字
[[:lower:]]	[a-z]	小写字母
[[:upper:]]	[A-Z]	大写字母
[[:space:]]	[ \t\n\r\f\v]	空白字符
[[:punct:]]		标点符号