MySQL零散拾遗（八）--- MySQL正则表达式

MySQL 支持使用正则表达式进行模式匹配，这对于复杂的字符串处理非常有用。MySQL 中的正则表达式可以通过 REGEXP 或 RLIKE 运算符来实现。下面详细介绍 MySQL 中正则表达式的语法和一些常用的正则表达式模式。

正则表达式基础

1. 锚点

   • ^: 匹配字符串开头
   • $: 匹配字符串结尾

2. 量词:

   • .(点)：匹配除换行符以外的任意单个字符。
   • *: 匹配前一个字符（前面的表达式）零次或多次。
   • +: 匹配前一个字符（前面的表达式）一次或多次。
   • ?: 匹配前一个字符（前面的表达式） 0 次或 1 次
   • {n}: 匹配前一个字符（前面的表达式）恰好 n 次。
   • {n,}: 匹配前一个字符（前面的表达式）至少 n 次。
   • {n,m}: 匹配前一个字符（前面的表达式）至少 n 次但不超过 m 次。

3. 字符类:

   • [abc]: 匹配方括号内的任意一个字符
   • [^abc]: 匹配不在方括号内的任意字符
   • [a-z]: 匹配 a 到 z 之间的任何小写字母。
   • [A-Z]: 匹配 A 到 Z 之间的任何大写字母。
   • [0-9]: 匹配任何数字。

4. 预定义类:

   • \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]。

5. 特殊字符:

   • |：匹配左右任意一个表达式。
   • ()：用于分组。
   • []：用于字符类。
   • {}：用于量词。
   • \：转义字符。

6. 分组和引用

   • (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 的正则表达式中,分组和引用是非常有用的功能。它们可以帮助我们更灵活地匹配和处理字符串。

分组

在正则表达式中,使用圆括号 () 可以创建一个分组。分组可以用于以下目的:

1. 将一部分正则表达式作为一个整体进行匹配。
2. 对分组进行某些操作,如替换、捕获等。

示例:

-- 匹配以 "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 的官方文档或使用在线资源来获得更详细的指导。