MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL正则表达式搜索入门与实例

2024-09-173.7k 阅读

MySQL 正则表达式基础

正则表达式的概念

正则表达式是一种用于匹配文本模式的强大工具。在 MySQL 中，正则表达式可用于在字符串中查找特定模式的数据。它提供了比普通的 LIKE 操作符更灵活、更强大的匹配方式。LIKE 操作符虽然可以进行简单的模式匹配，但正则表达式能够处理更复杂的字符组合、重复模式以及位置相关的匹配。

例如，假设我们有一个存储用户邮箱地址的表，要查找所有以 gmail.com 结尾的邮箱。使用 LIKE 可以写成：WHERE email LIKE '%gmail.com'。但如果我们想查找所有以字母开头，中间包含任意字符，然后是 @，接着是 gmail.com 或者 yahoo.com 结尾的邮箱，LIKE 就会显得力不从心，而正则表达式则可以轻松应对。

MySQL 中支持的正则表达式语法

  1. 字符匹配
    • 单个字符匹配：
      • 点号（.）匹配任意单个字符。例如，'a.c' 可以匹配 'abc'、'aec' 等，只要中间是任意一个字符即可。
      • 方括号（[]）用于指定一个字符集合。例如，'a[xyz]c' 可以匹配 'axc'、'ayc'、'azc'，只能匹配方括号内列出的字符。
    • 字符范围匹配：在方括号内，可以使用连字符（-）表示字符范围。例如，'a[b - e]c' 可以匹配 'abc'、'adc'、'aec'，即匹配 b 到 e 之间的字符。
  2. 重复匹配
    • 星号（*）表示前面的字符可以出现 0 次或多次。例如，'ab*c' 可以匹配 'ac'（此时 b 出现 0 次）、'abc'、'abbc' 等。
    • 加号（+）表示前面的字符可以出现 1 次或多次。例如，'ab + c' 只能匹配 'abc'、'abbc' 等，不能匹配 'ac'。
    • 花括号（{n}、{n,}、{n,m}）：{n} 表示前面的字符恰好出现 n 次；{n,} 表示前面的字符至少出现 n 次；{n,m} 表示前面的字符出现 n 到 m 次。例如，'ab{2}c' 只能匹配 'abbc'；'ab{2,}c' 可以匹配 'abbc'、'abbbc' 等；'ab{2,4}c' 可以匹配 'abbc'、'abbbc'、'abbbbc'。
  3. 边界匹配
    • 脱字符（^）在正则表达式开始处表示匹配字符串的开始位置。例如，'^abc' 只会匹配以 abc 开头的字符串。
    • 美元符号（$）在正则表达式末尾表示匹配字符串的结束位置。例如，'abc$' 只会匹配以 abc 结尾的字符串。
  4. 特殊字符匹配
    • 反斜杠（\）用于转义特殊字符，使其表示字符本身。例如，要匹配点号字符（.），需写成 '\.'，因为点号本身在正则表达式中有特殊含义。

正则表达式在 MySQL 中的应用场景

数据验证

在数据库中存储数据时，经常需要对数据进行验证，确保其符合特定格式。例如，验证电话号码、邮箱地址、身份证号码等。

假设我们有一个 customers 表，其中有一个 phone_number 字段，存储用户的电话号码。电话号码格式为 11 位数字，且以 1 开头。可以使用以下查询来验证：

SELECT * FROM customers
WHERE phone_number REGEXP '^1[0 - 9]{10}$';

这个查询会返回所有符合 11 位数字且以 1 开头格式的电话号码。如果有不符合格式的电话号码，就不会出现在结果集中。

文本搜索与分析

在文本字段中查找特定模式的内容非常有用。比如在新闻文章、博客内容等文本数据中搜索关键词或特定格式的字符串。

假设有一个 news_articles 表，其中有一个 content 字段存储文章内容。我们想查找所有包含以 http:// 或 https:// 开头的链接的文章，可以使用如下查询：

SELECT * FROM news_articles
WHERE content REGEXP '^(http|https)://';

此查询使用了管道符（|），表示 http 或者 https 都可以匹配。

数据清洗

当导入数据或者从不同数据源获取数据时，数据可能存在格式不一致的情况。正则表达式可以用于将数据清洗为统一格式。

例如，我们有一个 product_names 表，其中的产品名称可能存在一些不规则的空格或特殊字符。假设我们想将所有产品名称中的多余空格去除，只保留一个空格。可以通过以下步骤实现：

  1. 首先，创建一个新的临时字段 cleaned_name：
ALTER TABLE product_names ADD COLUMN cleaned_name VARCHAR(255);
  1. 然后，使用正则表达式替换函数 REGEXP_REPLACE 来清洗数据：
UPDATE product_names
SET cleaned_name = REGEXP_REPLACE(product_name, ' +','');

这里 ' +' 表示一个或多个空格，REGEXP_REPLACE 函数将其替换为单个空格。

正则表达式在 MySQL 中的操作符

REGEXP 操作符

REGEXP 操作符用于判断一个字符串是否匹配指定的正则表达式。语法为：string REGEXP pattern，如果 string 匹配 pattern，则返回 1，否则返回 0。

例如，有一个 employees 表，其中 last_name 字段存储员工的姓氏。我们想查找姓氏以 S 开头的员工：

SELECT * FROM employees
WHERE last_name REGEXP '^S';

NOT REGEXP 操作符

NOT REGEXP 操作符与 REGEXP 相反，用于判断一个字符串是否不匹配指定的正则表达式。语法为：string NOT REGEXP pattern，如果 string 不匹配 pattern，则返回 1，否则返回 0。

例如，继续上面的 employees 表，我们想查找姓氏不以 S 开头的员工：

SELECT * FROM employees
WHERE last_name NOT REGEXP '^S';

正则表达式函数

REGEXP_REPLACE 函数

REGEXP_REPLACE 函数用于在字符串中查找匹配正则表达式的部分，并将其替换为指定的字符串。语法为：REGEXP_REPLACE(str, pattern, replacement)，其中 str 是要操作的字符串，pattern 是正则表达式，replacement 是替换字符串。

假设我们有一个 addresses 表，其中 address 字段存储地址信息，可能存在一些不规范的缩写，比如 St. 应该统一为 Street。可以使用以下查询：

UPDATE addresses
SET address = REGEXP_REPLACE(address, 'St\.', 'Street');

这里 St\. 表示匹配 St.，将其替换为 Street。

REGEXP_SUBSTR 函数

REGEXP_SUBSTR 函数用于从字符串中提取匹配正则表达式的子字符串。语法为：REGEXP_SUBSTR(str, pattern [, start_position [, occurrence [, match_type]]])。

例如，在一个 emails 表中，email 字段存储邮箱地址。我们想提取每个邮箱地址中的用户名部分（即 @ 符号之前的部分）：

SELECT REGEXP_SUBSTR(email, '^[^@]+') AS username
FROM emails;

这里 '^[^@]+' 表示从字符串开头到 @ 符号之前的一个或多个字符。

正则表达式搜索实例

实例一：查找特定格式的订单编号

假设我们有一个 orders 表，其中 order_number 字段存储订单编号，订单编号格式为 ORD - 20230101 - 001，即 ORD - 年 + 月 + 日 - 三位数字序号。我们想查找 2023 年 10 月的所有订单编号。

SELECT order_number
FROM orders
WHERE order_number REGEXP '^ORD - 202310[0 - 9]{2}-[0 - 9]{3}$';

这里 ^ORD - 202310 匹配开头为 ORD - 202310，[0 - 9]{2} 匹配日期的两位数字，-[0 - 9]{3}$ 匹配 - 加上三位数字序号并以该模式结尾。

实例二：查找包含特定关键词的产品描述

有一个 products 表，description 字段存储产品描述。我们想查找所有描述中包含 waterproof 或者 water - resistant 关键词的产品。

SELECT * FROM products
WHERE description REGEXP 'waterproof|water - resistant';

通过 | 操作符，只要描述中包含其中一个关键词，该产品就会出现在结果集中。

实例三：清洗与规范化数据

在 customers 表中，phone_number 字段存储的电话号码格式不一，有的可能包含括号、空格或短横线，如 (123) 456 - 7890、123 - 456 - 7890 等。我们要将其统一格式化为 1234567890。

  1. 首先，去除所有非数字字符：
UPDATE customers
SET phone_number = REGEXP_REPLACE(phone_number, '[^0 - 9]', '');

这里 [^0 - 9] 表示匹配所有非数字字符，并将其替换为空字符串。 2. 然后，检查电话号码长度是否为 10 位，如果不是则进行处理（假设不符合长度的记录需要特殊标记）：

UPDATE customers
SET phone_number = CASE
                   WHEN CHAR_LENGTH(phone_number) = 10 THEN phone_number
                   ELSE 'INVALID_PHONE'
                   END;

通过这两步操作，实现了电话号码数据的清洗与规范化。

正则表达式性能考量

正则表达式对查询性能的影响

正则表达式虽然强大，但在使用时可能会对查询性能产生较大影响。与简单的 LIKE 操作符相比，正则表达式的匹配过程更为复杂，因为它需要对字符串中的每个字符进行模式匹配。

例如，在一个包含大量文本数据的表中，如果使用复杂的正则表达式进行全表扫描，查询可能会非常耗时。这是因为正则表达式引擎需要在每一行数据上执行复杂的匹配逻辑，而不像 LIKE 操作符在某些情况下可以利用索引提高查询效率。

优化正则表达式查询的方法

  1. 避免在开头使用通配符：尽量避免在正则表达式开头使用 .* 或类似的通配符组合，因为这会导致全表扫描。例如，'.*keyword' 这种模式会迫使数据库检查每一个字符，而 'keyword.*' 则可以利用索引（如果有的话）。
  2. 使用索引：如果可能，对要进行正则表达式匹配的字段创建索引。虽然 MySQL 对正则表达式的索引支持有限，但对于一些简单的模式，索引仍然可以提高查询性能。例如，对于以固定字符串开头的正则表达式，索引可能会起作用。
  3. 限制匹配范围：尽量缩小要匹配的字符串范围。如果只需要在字符串的一部分进行匹配，不要对整个字符串进行正则表达式操作。例如，在处理长文本时，可以先通过其他条件过滤出可能包含目标模式的部分，再进行正则表达式匹配。

在实际应用中，需要根据具体的数据量、数据特点以及查询需求来权衡是否使用正则表达式，以及如何优化正则表达式查询，以确保数据库的性能不受太大影响。

总之，MySQL 中的正则表达式是一个非常强大的工具，在数据验证、文本搜索、数据清洗等方面都有广泛的应用。但在使用时，需要深入理解其语法和性能特点，以充分发挥其优势，同时避免性能问题。通过不断实践和优化，能够更好地利用正则表达式来处理和管理数据库中的数据。