MySQL 正则表达式 - 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', 'sales@apple.com'),
(2, 'Samsung Galaxy S23', 'SG23-002', 'Samsung Galaxy S23 256GB', 'info@samsung.com'),
(3, 'MacBook Air M2', 'MBA-M2-003', 'Apple MacBook Air with M2 chip', 'support@apple.com'),
(4, 'Dell XPS 13', 'DX13-004', 'Dell XPS 13 Laptop Intel i7', 'contact@dell.com'),
(5, 'iPad Pro 11', 'IPD11-005', 'Apple iPad Pro 11-inch 2022', 'service@apple.com'),
(6, 'Surface Pro 9', 'SP9-006', 'Microsoft Surface Pro 9 Tablet', 'help@microsoft.com'),
(7, 'AirPods Pro 2', 'APP2-007', 'Apple AirPods Pro 2nd Generation', 'store@apple.com'),
(8, 'Galaxy Buds2 Pro', 'GBP2-008', 'Samsung Galaxy Buds2 Pro Wireless', 'sales@samsung.co.kr');
                

基本正则表达式模式

字符匹配

-- 匹配包含 '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:]]		标点符号

🔍 MySQL 正则表达式