MySQL 正则表达式

   一、MySQL 正则表达式的作用：

• 数据筛选：从大量的数据中精确地筛选出符合特定模式的行或值。

• 数据验证：验证输入的数据是否符合特定的格式要求，例如用户名、邮箱地址等。

• 文本处理：对文本字段进行复杂的操作，如查找、替换特定模式的内容。




二、MySQL 中的常见正则表达式类型


1. 匹配字符：

• .
   ：匹配任意单个字符。

• ^
   ：匹配字符串的开始位置。

• $
   ：匹配字符串的结束位置。

• *
   ：匹配前面的字符或字符集出现零次或多次。

• +
   ：匹配前面的字符或字符集出现一次或多次。

• ?
   ：匹配前面的字符或字符集出现零次或一次。

• [ ]
   ：匹配方括号内的任意一个字符。

• [^ ]
   ：匹配除方括号内的字符之外的任意一个字符。

• ( )
   ：分组和捕获匹配的子表达式。

2. 字符类：

• \d
   ：匹配一个数字字符。

• \w
   ：匹配一个字母数字字符（包括下划线）。

• \s
   ：匹配一个空白字符（包括空格、制表符和换行符）。

3. 重复匹配：

• {n}
   ：匹配前面的字符或字符集恰好出现 n 次。

• {n,}
   ：匹配前面的字符或字符集出现 n 次或更多次。

• {n,m}
   ：匹配前面的字符或字符集出现至少 n 次且最多 m 次。

4. 逻辑操作符：

• |
   ：表示或操作，用于匹配多个模式中的任意一个。

  三、常见的正则表达式实例演示：

• [abc]
   ：匹配字符 a
  、b
   或 c
   。假设表 users
   中有列 name
   ，值有 alice
  、bob
  、charlie
   ，以下查询会返回 alice
  、bob
  、charlie
   ：

SELECT * FROM users WHERE name REGEXP '[abc]';

• [a-z]
   ：匹配小写字母 a
   到 z
   之间的任意字符。
  假设表 words
   中有列 word
   ，值有 apple
  、banana
  、cherry
   ，以下查询会返回这三行：

SELECT * FROM words WHERE word REGEXP '[a-z]';

• [A-Z]
   ：匹配大写字母 A
   到 Z
   之间的任意字符。
  假设表 cities
   中有列 city_name
   ，值有 New York
  、London
  、PARIS
   ，以下查询会返回 New York
  、London
  、PARIS
   ：

SELECT * FROM cities WHERE city_name REGEXP '[A-Z]';

• [0-9]
   ：匹配数字 0
   到 9
   之间的任意字符。
  假设表 numbers
   中有列 num
   ，值有 123
  、456
  、789
   ，以下查询会返回这三行：

SELECT * FROM numbers WHERE num REGEXP '[0-9]';

• [^abc]
   ：匹配除了 a
  、b
  、c
   之外的任意字符。
  假设表 chars
   中有列 char_value
   ，值有 x
  、y
  、z
   ，以下查询会返回这三行：

SELECT * FROM chars WHERE char_value REGEXP '[^abc]';

• \d
   ：匹配一个数字。
  假设表 codes
   中有列 code
   ，值有 A1B2
  、C3D4
   ，以下查询会返回这两行：

SELECT * FROM codes WHERE code REGEXP '\d';

• \D
   ：匹配一个非数字。
  假设表 texts
   中有列 text
   ，值有 Hello 123
  、World 456
   ，以下查询会返回这两行：

SELECT * FROM texts WHERE text REGEXP '\D';

• \w
   ：匹配字母、数字或下划线。
  假设表 identifiers
   中有列 identifier
   ，值有 user_1
  、order_2
   ，以下查询会返回这两行：

SELECT * FROM identifiers WHERE identifier REGEXP '\w';

• \W
   ：匹配非字母、数字或下划线。
  假设表 symbols
   中有列 symbol
   ，值有 @
  、#
  、$
   ，以下查询会返回这三行：

SELECT * FROM symbols WHERE symbol REGEXP '\W';

• \s
   ：匹配空白字符（包括空格、制表符、换行符等）。
  假设表 sentences
   中有列 sentence
   ，值有 Hello World
   、 This is a test
   ，以下查询会返回这两行：

SELECT * FROM sentences WHERE sentence REGEXP '\s';

• .*
   ：匹配任意字符零次或多次。
  假设表 descriptions
   中有列 description
   ，值有 This is a long description
   ，以下查询会返回这一行：

SELECT * FROM descriptions WHERE description REGEXP '.*';

• .+
   ：匹配任意字符一次或多次。
  假设表 contents
   中有列 content
   ，值有 Empty
   、 Some text
   ，以下查询会返回 Some text
   这一行：

SELECT * FROM contents WHERE content REGEXP '.+';

• ^
   ：匹配字符串的开头。
  假设表 urls
   中有列 url
   ，值有 https://example.com
   、 www.example.org
   ，以下查询会返回这两行：

SELECT * FROM urls WHERE url REGEXP '^https';

• $
   ：匹配字符串的结尾。
  假设表 extensions
   中有列 extension
   ，值有 .jpg
   、 .png
   ，以下查询会返回这两行：

SELECT * FROM extensions WHERE extension REGEXP '.jpg$';

    四、使用 REGEXP 进行模式匹配的示例及结果：

假设我们有一个表 emails
 ，其中有列 email
 ，包含以下值： example@example.com
 、 test123@yahoo.com
 、 invalid_email
 。

SELECT * FROM emails WHERE email REGEXP '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';

上述查询用于筛选出有效的电子邮件格式的行，输出结果将是 example@example.com
 、 test123@yahoo.com
 。

    五、使用 RLIKE 进行模式匹配的示例及结果：

同样对于 emails
 表：

SELECT * FROM emails WHERE email RLIKE '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';

输出结果与使用 REGEXP
 时相同，即 example@example.com
 、 test123@yahoo.com
 。

    六、在 MySQL 中使用正则表达式注意的事项：

1. 性能影响：复杂的正则表达式可能会导致查询性能下降，特别是在处理大型数据集时。因此，在可能的情况下，尽量使用简单和高效的正则表达式模式。

2. 字符集和排序规则：正则表达式的匹配结果可能会受到表和列的字符集以及排序规则的影响。确保您了解并考虑到这些因素，以获得预期的匹配结果。

3. 版本差异：不同版本的 MySQL 可能在正则表达式的支持和行为上存在细微的差异。在使用特定的正则表达式功能时，最好参考对应版本的文档。

4. 数据类型兼容性：确保要应用正则表达式的列的数据类型与预期的操作兼容。例如，对整数列使用正则表达式可能会导致意外的结果或错误。

5. 转义特殊字符：如果您的正则表达式模式中包含 MySQL 本身的特殊字符（如 %
   、_
    等），需要正确地进行转义，以避免意外的匹配行为。

6. 错误处理：如果正则表达式模式存在语法错误，可能会导致查询失败。在实际应用中，要对可能的错误进行适当的处理和提示。

7. 测试和验证：在将正则表达式应用于实际的生产环境之前，务必在测试环境中进行充分的测试和验证，以确保其能够正确地筛选出所需的数据。

    七、MySQL 正则表达式常见错误及解决方法：

1. 正则表达式语法错误：

• 错误示例：SELECT * FROM table WHERE column REGEXP '[(]'
   （缺少反斜线转义括号）

• 解决方法：正确的应该是 SELECT * FROM table WHERE column REGEXP '\\('
   ，对于特殊字符，如 (
  , )
  , [
  , ]
  , {
  , }
  , ^
  , $
  , |
  , *
  , +
  , ?
  , .
   等，需要使用反斜线 \
   进行转义。

2. 忘记转义 MySQL 通配符：

• 错误示例：SELECT * FROM table WHERE column REGEXP '%value%'
   （%
   是 MySQL 的通配符）

• 解决方法：使用反斜线转义 %
   ，即 SELECT * FROM table WHERE column REGEXP '\%value\%'
   。

3. 字符集和排序规则导致的不匹配：

• 错误示例：当表的字符集为 utf8mb4 ，但正则表达式模式没有考虑多字节字符。

• 解决方法：确保了解表的字符集和排序规则，并根据需要调整正则表达式模式。

4. 模式过于复杂影响性能：

• 错误示例：使用了非常复杂且冗长的正则表达式模式，导致查询速度极慢。

• 解决方法：尽量简化模式，或者考虑先对数据进行预处理或筛选，减少需要应用正则表达式的数据量。

5. 对不适当的数据类型应用正则表达式：

• 错误示例：对整数列使用正则表达式进行匹配。

• 解决方法：确保只对适合的字符串类型列应用正则表达式。

6. 忽略版本差异：

• 错误示例：使用了较新的 MySQL 版本支持但当前版本不支持的正则表达式特性。

• 解决方法：查阅当前使用的 MySQL 版本的文档，确保使用的正则表达式特性是被支持的。

7. 错误理解匹配逻辑：

• 错误示例：期望匹配所有包含字母和数字的字符串，但编写的模式不准确。

• 解决方法：仔细检查和测试正则表达式模式，确保其符合预期的匹配逻辑。可以使用一些在线正则表达式测试工具来验证模式的正确性。

点 击 下 方  关注+星标  公众号