MySQL 支持使用正则表达式进行模式匹配，这对于复杂的字符串处理非常有用。MySQL 中的正则表达式可以通过 REGEXP 或 RLIKE 运算符来实现。下面详细介绍 MySQL 中正则表达式的语法和一些常用的正则表达式模式。
正则表达式基础
-
锚点
^: 匹配字符串开头$: 匹配字符串结尾
-
量词:
.(点)：匹配除换行符以外的任意单个字符。*: 匹配前一个字符（前面的表达式）零次或多次。+: 匹配前一个字符（前面的表达式）一次或多次。?: 匹配前一个字符（前面的表达式） 0 次或 1 次{n}: 匹配前一个字符（前面的表达式）恰好 n 次。{n,}: 匹配前一个字符（前面的表达式）至少 n 次。{n,m}: 匹配前一个字符（前面的表达式）至少 n 次但不超过 m 次。
-
字符类:
[abc]: 匹配方括号内的任意一个字符[^abc]: 匹配不在方括号内的任意字符[a-z]: 匹配 a 到 z 之间的任何小写字母。[A-Z]: 匹配 A 到 Z 之间的任何大写字母。[0-9]: 匹配任何数字。
-
预定义类:
\d: 匹配任意数字字符,等价于[0-9]。\D: 匹配任意非数字字符,等价于[^0-9]。\w: 匹配任意字母数字字符(包括下划线),等价于[a-zA-Z0-9_]。\W: 匹配任意非字母数字字符,等价于[^a-zA-Z0-9_]。\s: 匹配任意空白字符,包括空格、制表符、换行符等,等价于[ \t\n\r\f\v]。\S: 匹配任意非空白字符,等价于[^ \t\n\r\f\v]。
-
特殊字符:
|：匹配左右任意一个表达式。()：用于分组。[]：用于字符类。{}：用于量词。\：转义字符。
-
分组和引用
(pattern): 创建一个分组\n: 引用第n个分组
使用 REGEXP 或 RLIKE
在 MySQL 中，你可以使用 REGEXP 或 RLIKE 运算符来应用正则表达式。这两个关键字在功能上是相同的，但在某些情况下，RLIKE 可能会被优化器优化得更好。
示例
-
匹配以 ‘John’ 开头的字符串
SELECT * FROM users WHERE name REGEXP '^John'; -
匹配包含 ‘smith’ 的字符串(不区分大小写)
SELECT * FROM users WHERE name REGEXP 'smith'; -
匹配只包含数字的字符串
SELECT * FROM users WHERE phone REGEXP '^[0-9]+$'; -
匹配包含 ‘www.’ 或 ‘https://’ 的 URL
SELECT * FROM urls WHERE url REGEXP '^(www\.|https://)' -
替换包含 ‘foo’ 的字符串为 ‘bar’
SELECT REPLACE(column, 'foo', 'bar') FROM table; -
匹配包含数字的字符串
SELECT * FROM users WHERE phone REGEXP '\\d'; -
匹配只包含字母的字符串
SELECT * FROM products WHERE name REGEXP '^\\w+$'; -
匹配包含空白字符的字符串
SELECT * FROM comments WHERE content REGEXP '\\s'; -
匹配不包含数字的字符串
SELECT * FROM addresses WHERE city REGEXP '^\\D+$';
匹配电子邮箱
假设我们有一个表 emails，包含一个字段 email，现在我们要找出所有有效的电子邮件地址：
SELECT user_id, name, mail
FROM Users
-- 请注意，我们还转义了`@`字符，因为它在某些正则表达式中具有特殊意义
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';
上面提到了分组和引用，那就详细的介绍介绍
在 MySQL 的正则表达式中,分组和引用是非常有用的功能。它们可以帮助我们更灵活地匹配和处理字符串。
分组
在正则表达式中,使用圆括号 () 可以创建一个分组。分组可以用于以下目的:
- 将一部分正则表达式作为一个整体进行匹配。
- 对分组进行某些操作,如替换、捕获等。
示例:
-- 匹配以 "www." 开头,后跟 1 个或多个字母数字字符,然后是 ".com" 的字符串
SELECT * FROM websites WHERE url REGEXP '^www\\.(\\w+)\\.com$';
在上述例子中,(\\w+) 是一个分组,它匹配 1 个或多个字母数字字符。
引用
在正则表达式中,可以使用 \1、\2 等来引用已经匹配的分组。这在进行字符串替换或重新排列时非常有用。
示例:
-- 提取 URL 中的域名部分
SELECT REGEXP_REPLACE(url, '^www\\.(\\w+)\\.com$', '\\1')
FROM websites;
在上述例子中,\\1 引用了第一个分组 (\\w+),即域名部分。REGEXP_REPLACE 函数使用这个引用进行字符串替换,提取出域名。
另一个示例:
-- 将邮件地址中的用户名和域名部分交换
SELECT CONCAT(REGEXP_SUBSTR(email, '\\@'), '.', REGEXP_SUBSTR(email, '^[^\\@]+'))
FROM users;
在这个例子中,我们使用 REGEXP_SUBSTR 函数提取邮件地址中的用户名和域名部分,然后使用 CONCAT 函数将它们交换位置。
分组和引用是正则表达式中非常强大的功能,可以帮助我们更灵活地处理字符串。在 MySQL 中使用这些特性可以大大提高我们处理数据的能力。
注意事项
- MySQL 的正则表达式支持多种模式匹配，但并不是所有的正则表达式功能都可用，特别是在某些版本的 MySQL 中。
- 在使用正则表达式时，确保对输入数据进行适当的测试和验证，以避免安全漏洞或性能问题。
- 对于复杂或特定的正则表达式需求，你可能需要查阅 MySQL 的官方文档或使用在线资源来获得更详细的指导。
