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

MySQL正则表达式中的字符匹配与OR操作

2021-12-091.4k 阅读

MySQL正则表达式基础概述

MySQL 中的正则表达式是一种强大的字符串匹配工具，它允许用户在字符串中搜索特定模式。正则表达式使用一种专门的语法来定义模式，这种语法包含了各种特殊字符和元字符，用于描述字符集合、重复次数、位置等。在 MySQL 中，正则表达式主要通过 REGEXP 操作符来使用。例如，要查找名字中包含“son”的记录，可以使用以下查询：

SELECT * FROM users WHERE name REGEXP 'son';

这里的 REGEXP 操作符会在 name 字段的每个值中搜索“son”这个模式。

字符匹配

基本字符匹配

  1. 普通字符匹配：在正则表达式中，普通字符（非元字符）会匹配自身。例如，正则表达式 abc 会匹配字符串“abc”。在 MySQL 中，假设我们有一个 products 表，其中有一个 description 字段，我们要查找描述中包含“book”的产品，可以这样写查询：
SELECT * FROM products WHERE description REGEXP 'book';
  1. 字符类匹配：字符类是用方括号 [] 括起来的一组字符。它匹配方括号内的任意一个字符。例如，[abc] 会匹配“a”、“b”或“c”中的任意一个字符。如果我们要查找名字中包含“a”、“e”或“i”的用户，可以使用：
SELECT * FROM users WHERE name REGEXP '[aei]';
  1. 范围字符类匹配：在字符类中，可以使用连字符 - 来指定字符范围。例如，[a - z] 匹配任意小写字母，[0 - 9] 匹配任意数字。假设我们有一个 orders 表，其中 order_number 字段包含数字和字母的混合值，我们要查找订单号中包含小写字母的记录，可以这样查询：
SELECT * FROM orders WHERE order_number REGEXP '[a - z]';

转义字符匹配

元字符在正则表达式中有特殊含义，如果要匹配元字符本身，就需要使用反斜杠 \ 进行转义。例如，点号 . 在正则表达式中是元字符，表示匹配任意单个字符，但如果要匹配实际的点号，就需要写成 \.。假设我们有一个 files 表，其中 file_name 字段包含文件名，我们要查找文件名中包含点号的记录，可以这样写：

SELECT * FROM files WHERE file_name REGEXP '\\.';

注意在 MySQL 中，需要使用双反斜杠 \\，因为在 SQL 字符串中，反斜杠本身也是转义字符。

特殊字符匹配

  1. 点号 .：点号是一个元字符，它匹配任意单个字符（除了换行符，在某些正则表达式实现中可以通过特定选项包含换行符）。例如，正则表达式 a.c 会匹配“abc”、“a1c”、“a c”等。假设我们要查找描述中包含三个字符，中间字符任意，且首尾字符分别为“b”和“k”的产品：
SELECT * FROM products WHERE description REGEXP 'b.k';
  1. 脱字符 ^：在正则表达式的开头使用脱字符 ^，表示匹配字符串的开头。例如，^abc 会匹配以“abc”开头的字符串。如果我们要查找名字以“John”开头的用户：
SELECT * FROM users WHERE name REGEXP '^John';
  1. 美元符号 $：美元符号 $ 在正则表达式的结尾使用，表示匹配字符串的结尾。例如，abc$ 会匹配以“abc”结尾的字符串。假设我们有一个 urls 表，其中 url 字段包含网址，我们要查找以“.com”结尾的网址：
SELECT * FROM urls WHERE url REGEXP '\.com$';

OR 操作

竖线 | 表示 OR 操作

在正则表达式中，竖线 | 用于表示逻辑或操作。它允许我们在模式中指定多个可能的匹配。例如，abc|def 会匹配“abc”或者“def”。假设我们有一个 categories 表，其中 category_name 字段包含产品类别名称，我们要查找类别名称为“electronics”或者“clothing”的记录，可以这样查询：

SELECT * FROM categories WHERE category_name REGEXP 'electronics|clothing';

复杂 OR 操作示例

  1. 结合字符类与 OR 操作：我们可以将字符类和 OR 操作结合起来，实现更复杂的匹配。例如，假设我们要查找名字中包含“a”或者“e”，并且以“son”结尾的用户。可以这样写正则表达式：
SELECT * FROM users WHERE name REGEXP '[ae].*son';

这里的 .* 表示匹配任意数量（包括 0 个）的任意字符。[ae] 匹配“a”或“e”，然后 .* 匹配中间的任意字符，最后“son”匹配以“son”结尾。 2. 多层 OR 操作：我们还可以进行多层的 OR 操作。例如，假设我们要查找描述中包含“red”或者“blue”，并且包含“car”或者“bike”的产品。可以这样构建正则表达式：

SELECT * FROM products WHERE description REGEXP '(red|blue).*(car|bike)';

这里外层括号中的 (red|blue) 表示匹配“red”或者“blue”，内层括号中的 (car|bike) 表示匹配“car”或者“bike”，中间的 .* 匹配它们之间的任意字符。

边界匹配与 OR 操作结合

单词边界与 OR 操作

在正则表达式中，单词边界可以用 \b 表示。单词边界匹配单词的开始或结束位置，这里的单词是由字母、数字和下划线组成的序列。当与 OR 操作结合时，可以更精确地匹配特定单词。例如，假设我们有一个 texts 表，其中 content 字段包含文本内容，我们要查找包含“apple”或者“banana”作为独立单词的记录：

SELECT * FROM texts WHERE content REGEXP '\b(apple|banana)\b';

这样可以避免匹配像“applet”或“bananana”这样的字符串，因为它们不是完整的单词。

行边界与 OR 操作

行边界与单词边界类似，不过它是针对整行的。^ 匹配行的开头，$ 匹配行的结尾。当与 OR 操作结合时，可以在整行的层面进行匹配。例如，假设我们有一个 logs 表，其中 log_message 字段包含日志信息，我们要查找以“ERROR”或者“WARNING”开头的日志记录：

SELECT * FROM logs WHERE log_message REGEXP '^(ERROR|WARNING)';

这在处理日志文件等按行存储的文本数据时非常有用，可以快速筛选出特定类型的日志。

重复匹配与 OR 操作

重复字符与 OR 操作

  1. 星号 *：星号 * 表示前面的字符或字符类可以出现 0 次或多次。当与 OR 操作结合时，可以灵活地匹配不同数量的字符。例如，假设我们要查找名字中包含“a”0 次或多次，然后跟着“b”，或者包含“c”0 次或多次，然后跟着“d”的用户。可以这样写正则表达式：
SELECT * FROM users WHERE name REGEXP 'a*b|c*d';
  1. 加号 +：加号 + 表示前面的字符或字符类可以出现 1 次或多次。例如，假设我们要查找描述中包含“e”1 次或多次，然后跟着“f”，或者包含“g”1 次或多次，然后跟着“h”的产品：
SELECT * FROM products WHERE description REGEXP 'e+f|g+h';
  1. 问号 ?：问号 ? 表示前面的字符或字符类可以出现 0 次或 1 次。例如，假设我们要查找名字中包含“m”0 次或 1 次，然后跟着“n”，或者包含“o”0 次或 1 次，然后跟着“p”的用户：
SELECT * FROM users WHERE name REGEXP'm?n|o?p';

限定重复次数与 OR 操作

我们还可以使用花括号 {} 来指定重复的次数。例如，{n} 表示前面的字符或字符类恰好出现 n 次，{n,} 表示至少出现 n 次，{n,m} 表示出现 n 到 m 次。当与 OR 操作结合时，可以实现更精确的匹配。假设我们有一个 codes 表，其中 code 字段包含编码，我们要查找编码中包含“1”恰好 3 次，然后跟着“2”，或者包含“3”至少 2 次，然后跟着“4”的记录：

SELECT * FROM codes WHERE code REGEXP '1{3}2|3{2,}4';

分组与 OR 操作

分组的概念

在正则表达式中，使用括号 () 可以对字符进行分组。分组有两个主要作用：一是可以将一组字符视为一个整体进行重复操作；二是在与 OR 操作结合时，可以更清晰地定义逻辑关系。例如，(abc)+ 表示“abc”这个整体可以出现 1 次或多次。

分组与 OR 操作的结合使用

  1. 简单分组与 OR 操作：假设我们要查找名字中包含“john”或者“jane”，并且后面跟着“smith”的用户。可以这样写正则表达式：
SELECT * FROM users WHERE name REGEXP '(john|jane)smith';

这里通过括号将“john”和“jane”分组，然后整个分组与“smith”进行匹配。 2. 嵌套分组与 OR 操作：我们还可以进行嵌套分组。例如，假设我们要查找描述中包含“red car”或者“blue bike”，或者“green truck”的产品。可以这样构建正则表达式：

SELECT * FROM products WHERE description REGEXP '((red car)|(blue bike)|(green truck))';

这里外层括号将三个不同的组合进行分组，每个内层括号又将颜色和交通工具组合在一起，通过 OR 操作实现多个模式的匹配。

MySQL 正则表达式性能考虑

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

使用正则表达式在 MySQL 中进行查询时，性能是一个需要考虑的重要因素。一般来说，正则表达式的匹配操作比简单的字符串比较操作要复杂得多，因此可能会导致查询执行时间变长。特别是当数据量较大时，复杂的正则表达式可能会显著影响查询性能。例如，使用包含大量字符类、重复字符和 OR 操作的复杂正则表达式，数据库需要对每个记录进行详细的模式匹配，这会消耗大量的 CPU 和内存资源。

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

  1. 尽量简化正则表达式：避免使用不必要的复杂模式。例如，如果可以通过简单的字符串比较来完成任务，就不要使用正则表达式。如果必须使用正则表达式，尽量减少字符类、重复字符和 OR 操作的嵌套深度。例如，将 (a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z)+ 简化为 [a - z]+。
  2. 利用索引：如果可能，尽量对要匹配的字段建立索引。虽然 MySQL 对正则表达式查询不能完全利用索引，但在某些简单情况下，索引可以提高查询性能。例如，如果查询是基于以某个字符串开头的匹配（如 ^abc），并且字段上有索引，MySQL 可能会部分利用索引来加速查询。
  3. 批量处理：如果需要处理大量数据，可以考虑将数据分成多个批次进行处理，而不是一次性处理所有数据。这样可以减少内存的使用，提高查询的整体性能。

实际应用场景

数据清洗中的应用

在数据清洗过程中，经常需要使用正则表达式来识别和纠正不符合格式要求的数据。例如，在一个包含电话号码的表中，可能存在多种格式的电话号码，如“(123) 456 - 7890”、“123 - 456 - 7890”、“1234567890”等。我们可以使用正则表达式结合 OR 操作来识别这些不同格式的电话号码，并进行统一格式的转换。假设我们有一个 customers 表，其中 phone_number 字段包含电话号码：

-- 识别不同格式的电话号码
SELECT * FROM customers WHERE phone_number REGEXP '^\(\d{3}\)\s\d{3}-\d{4}$|^\d{3}-\d{3}-\d{4}$|^\d{10}$';

然后可以通过更新语句将这些电话号码转换为统一格式。

文本搜索中的应用

在文本搜索应用中，正则表达式的 OR 操作可以帮助我们查找包含多个关键词的文本。例如，在一个文章数据库中，我们要查找包含“database”或者“algorithm”，并且包含“performance”或者“optimization”的文章。假设我们有一个 articles 表，其中 content 字段包含文章内容：

SELECT * FROM articles WHERE content REGEXP '(database|algorithm).*(performance|optimization)';

这样可以快速筛选出与数据库性能优化相关的文章。

验证数据格式中的应用

在数据录入过程中，需要验证输入的数据是否符合特定格式。例如，验证电子邮件地址是否合法。电子邮件地址通常有多种格式，但基本结构是用户名@域名。我们可以使用正则表达式结合 OR 操作来验证不同类型的电子邮件地址。假设我们有一个 users 表，其中 email 字段包含用户的电子邮件地址：

-- 验证电子邮件地址
SELECT * FROM users WHERE email REGEXP '^[a-zA - Z0 - 9_.+-]+@[a-zA - Z0 - 9 -]+\.[a-zA - Z0 - 9-.]+$|^[a-zA - Z0 - 9_.+-]+@([a-zA - Z0 - 9 -]+\.)+[a-zA - Z]{2,}$';

这个正则表达式可以匹配常见的电子邮件地址格式，通过这种方式可以确保录入的电子邮件地址是有效的。

与其他字符串操作函数的对比

与 LIKE 操作符的对比

  1. 匹配能力：LIKE 操作符主要用于简单的字符串匹配，它支持通配符 %（匹配任意字符序列，包括空字符序列）和 _（匹配任意单个字符）。而正则表达式提供了更强大的匹配能力，如字符类、重复匹配、OR 操作等。例如，LIKE 无法直接实现字符类的匹配，而正则表达式可以轻松做到。假设我们要查找名字中包含“a”、“e”或“i”的用户，使用 LIKE 可能需要多个条件的组合：
SELECT * FROM users WHERE name LIKE '%a%' OR name LIKE '%e%' OR name LIKE '%i%';

而使用正则表达式则可以简洁地写成：

SELECT * FROM users WHERE name REGEXP '[aei]';
  1. 性能：在简单匹配场景下，LIKE 操作符的性能通常优于正则表达式，因为 LIKE 的匹配逻辑相对简单。但在复杂匹配场景下，正则表达式虽然功能强大，但可能会导致性能下降。例如，当需要匹配复杂的模式，如匹配特定格式的电话号码或电子邮件地址时，LIKE 可能无法实现，而正则表达式虽然能实现，但性能可能不如人意。

与其他字符串函数的对比

  1. 与 CONCAT、SUBSTRING 等函数对比：CONCAT 函数用于连接字符串，SUBSTRING 函数用于提取字符串的子串，这些函数主要用于字符串的拼接和截取操作，与正则表达式的匹配功能完全不同。例如，CONCAT('Hello', ', World') 会返回“Hello, World”，而正则表达式是用于在字符串中查找特定模式。不过，在实际应用中，这些函数可以与正则表达式结合使用。例如，我们可以先用正则表达式匹配出符合特定模式的字符串，然后使用 SUBSTRING 函数提取其中的部分内容。假设我们有一个 addresses 表，其中 address 字段包含完整地址，我们先用正则表达式匹配出包含邮政编码的部分：
SELECT address FROM addresses WHERE address REGEXP '\d{6}';

然后可以使用 SUBSTRING 函数提取出邮政编码：

SELECT SUBSTRING(address, REGEXP_INSTR(address, '\d{6}'), 6) FROM addresses WHERE address REGEXP '\d{6}';

这里 REGEXP_INSTR 函数用于返回正则表达式在字符串中第一次出现的位置。

总结与注意事项

  1. 总结：MySQL 正则表达式中的字符匹配和 OR 操作是非常强大的工具，它们可以帮助我们在数据库中进行复杂的字符串模式匹配。字符匹配提供了基本字符、字符类、转义字符和特殊字符等多种匹配方式，而 OR 操作则允许我们在模式中指定多个可能的匹配。通过合理组合这些功能，我们可以实现各种复杂的字符串搜索和验证需求。在实际应用中，它们在数据清洗、文本搜索、数据格式验证等场景中都发挥着重要作用。
  2. 注意事项：在使用正则表达式时，需要注意性能问题。复杂的正则表达式可能会导致查询执行时间变长，因此要尽量简化正则表达式，并根据情况利用索引。另外，不同数据库系统对正则表达式的支持可能存在细微差异，在跨数据库平台开发时需要注意兼容性。同时，在编写正则表达式时要仔细测试，确保模式能够准确匹配预期的字符串，避免出现误匹配或漏匹配的情况。