mysql_正则表达式

作者：YXN-sql 阅读量：211 发布日期：2021-03-08

匹配规则

在MySQL中，可以使用 REGEXP 或 RLIKE 操作符来执行正则表达式匹配

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'
[^...]	负值字符集合。匹配未包含的任意字符。例如， 'abc' 可以匹配 "plain" 中的'p'
p1 \| p2 \| p3	匹配 p1 或 p2 或 p3。例如，'z\\|food' 能匹配 "z" 或 "food"。'(z\\|f)ood' 则匹配 "zood" 或 "food"
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次

举例：

-- 查询包含特定字符串的记录
SELECT * FROM table WHERE column REGEXP 'contained_string';
-- 匹配以特定字符串开头的值
SELECT * FROM table WHERE Name RLIKE '^[A-G]';
-- 匹配以特定字符串结尾的值
SELECT * FROM table WHERE Name RLIKE 'e$';
-- 匹配包含特定长度字符串的值
SELECT * FROM table WHERE Name RLIKE '^.{6}$';

常用用法

校验数字的表达式

检查是否为n位数字：'^\d{n}$'
检查是否为非零的正整数：'^[1-9]\d*$'
检查是否为浮点数：'^(-?\d+)(.\d+)?$'
检查是否为负整数：'^-\d+$'
检查是否为正整数或浮点数：'^\d+(.\d+)?$'
检查是否为正数：'^[1-9]\d*|0$'
检查是否为非负整数：'^\d+$'
检查是否为非负浮点数：'^\d+(.\d+)?$'

校验字符的表达式

检查是否为汉字：'^[\u4e00-\u9fa5]+$'
检查是否为英文和数字：'^[A-Za-z0-9]+$'
检查是否为纯英文字母：'^[a-zA-Z]+$'
检查是否为大写英文字母：'^[A-Z]+$'
检查是否为小写英文字母：'^[a-z]+$'

特殊需求表达式

邮箱地址：'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$'
手机号码：'^\d{11}$'
IP地址：'^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$'
URL地址：'^(http|https)://[\w-]+(.[\w-]+)+([\w.,@?^=%&:/+#-]*[\w@?^=%&/+#-])?$'

高级用法

除了基本的匹配操作，SQL还支持更复杂的正则表达式功能，如分组、反向引用和断言等。这些功能允许你构建更加精确和灵活的匹配模式。例如，你可以使用括号来创建子表达式组，然后在正则表达式的其他部分引用它们。

BINARY关键字：用于区分大小写的匹配。
NOT REGEXP：用于匹配不符合正则表达式的记录。
REGEXP_INSTR()：返回与正则表达式模式匹配的子字符串的起始索引。
REGEXP_SUBSTR()：从字符串中提取与正则表达式模式匹配的子字符串。
REGEXP_REPLACE()：替换字符串中与正则表达式模式匹配的子字符串。

举例：

分组与捕获

分组与捕获是正则表达式中的一个重要概念，它允许你将匹配的文本分成几个部分，并分别处理这些部分。

例如，假设你想要匹配一个电话号码，其中包含国家代码、地区代码和本地号码，你可以这样做：

SELECT * FROM users WHERE phone_number REGEXP '^(\\+?\d{1,3})?[- ]?(\d{3})[- ]?(\d{3}[- ]?\d{4})$';

(\d{1,3}) 是第一个分组，用于匹配国家代码，
(\d{3}) 是第二个分组，用于匹配地区代码，
(\d{3}[- ]?\d{4}) 是第三个分组，用于匹配本地号码。
每个分组都被圆括号包围，形成一个捕获组，可以在正则表达式的其他部分通过 \1、\2 和 \3 来引用。

前瞻断言和后顾断言

前瞻断言和后顾断言允许你在不消耗字符的情况下检查文本的前面或后面是否存在某种模式。

例如，你可以使用前瞻断言来确保一个单词后面跟着另一个特定的单词：

SELECT * FROM articles WHERE title REGEXP '(?<=the )word';

(?<=the ) 是一个后顾断言，它确保 word 之前有 the（注意空格）。

条件表达式

条件表达式允许你根据前一个匹配的成功与否来决定是否进行后续的匹配。

例如，你可以使用条件表达式来匹配一个单词，除非它是另一个单词的一部分：

SELECT * FROM text WHERE word REGEXP '(?(^|[^a-z])apple)';

(?(^|[^a-z])apple) 是一个条件表达式，它只有在 apple 前面不是字母时才会匹配 apple。

替换操作

许多数据库管理系统允许你使用正则表达式进行文本替换操作。

例如，你可以使用正则表达式来替换所有的HTML标签为相应的实体：

UPDATE posts SET content = REPLACE(content, '<([a-z][a-z0-9]*[^>])>', '&lt;\1&gt;') WHERE content LIKE '%<%';

REPLACE 函数使用正则表达式 <([a-z][a-z0-9]*[^>])> 来匹配所有的HTML标签，并将其替换为 < 和 >。

限制

虽然SQL的正则表达式功能很强大，但它们也有一些限制。

例如，某些数据库系统可能不支持所有的正则表达式语法，或者对性能有一定影响，特别是在处理大量数据时，复杂的正则表达式可能会导致查询变慢。。

此外，正则表达式可能难以阅读和维护，特别是对于复杂的模式，需要确保理解正则表达式的语法和含义，以免造成不必要的错误。

小实例

# 去除MySQL中的HTML标签和行内样式
# regexp_replace函数用于替换HTML标签和行内样式为空字符串，从而实现去除的效果。
SELECT regexp_replace(regexp_replace(column_name, '<[^>]+>', ''), 'style="[^"]*"', '') AS cleaned_column
FROM your_table;

YXN-sql

2021-03-08