MySQL正则表达式REGEXP使用详解

吾爱主题阅读：163 2022-11-14 16:22:00 评论：0

正则表达式用来匹配文本的特殊的串（字符集合）（匹配文本，将一个模式（正则表达式）与一个文本串进行比较）。

LIKE 和 REGEXP之间的重要差别

LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行也不会被返回（除非使用通配符）。而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回，并且 REGEXP 能匹配整个列值（与 LIKE 相同的作用）。

正则表达式匹配不区分大小写

MySQL 的正则表达式匹配（自3.23.4版本后）不区分大小写（即大写和小写都匹配）。为区分大小写，可以使用 BINARY 关键字，例如：WHHERE name REGEXP BINARY 'Hern .000'。

简单的正则表达式测试

可以在不使用数据库表的情况下用 SELECT 语句来测试正则表达式，REGEXP 检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的 REGEXP 来测试表达式，并试验它们。

例如：检测 'Hern' 中是否含有数字

1	`SELECT` `'Hern'` `REGEXP` `'[0-9]'` `;`

结果将返回0（因为 ‘Hern’ 中没有数字）

空白元字符（特殊含义的字符）

用双反斜杠（\\）来引用元字符（具有特殊含义的字符）。常用引用元字符：

字符	其它信息
[ 和 ]	左右方括号用于指定字符类。字符类是进行匹配时所要依据的一组字符。除连字符 (-) 和脱字符 (^) 外，在字符类中指定的元字符和量词（如 * 和 {m}，分别为元字符和量词）没有特殊意义，可当作实际字符进行运算。
*	星号可用于与字符匹配 0 次或多次。例如，`REGEXP '.*abc'` 匹配的字符串以 abc 结尾并以任何前缀开头。因此，aabc、xyzabc 和 abc 匹配，但 bc 和 abcc 则不匹配。
?	问号可用于与字符匹配 0 次或 1 次。例如，`'colou?r'` 匹配 color 和 colour。
+	加号可用于与字符匹配 1 次或多次。例如，`'bre+'` 匹配 bre 和 bree，但不匹配 br。
-	可以在字符类中使用一个连字符来表示一个范围。例如，`REGEXP '[a-e]'` 匹配 a、b、c、d 和 e。
%	百分号可与 SIMILAR TO 配合使用来匹配任意数目的字符。不将百分号视为 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。当指定时，它匹配百分号 (%)。
_（下划线字符）	可将下划线与 SIMILAR TO 配合使用来匹配单个字符。不将下划线视为 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。当指定时，它匹配下划线 (_)。
\|	管道符号用于指定匹配字符串时要使用的替代模式。在由竖线分隔的一行模式中，竖线被解释为 OR，匹配过程从最左侧的模式开始，在找到第一个匹配项时停止。因此，您应按优先级的降序顺序列出模式。您可以指定任意数量的替代模式。
( 和 )	当左括号和右括号用于正则表达式的各个组合部分时，它们为元字符。例如，`(ab)*` 匹配零个或多个 ab 的重复项。与使用数学表达式一样，您使用组合来控制正则表达式各部分的计算顺序。
{ 和 }	当左大括号和右大括号用于指定量词时，它们为元字符。量词指定一个模式要构成某个匹配所必须重复的次数。例如： { m} 匹配某个字符正好 m 次。例如，`'519-[0-9]{3}-[0-9]{4}'` 匹配 519 地区号中的一个电话号码（假定数据按语法中定义的方式进行格式设置）。 { m,} 匹配某个字符至少 m 次。例如，`'[0-9]{5,}'` 匹配任何含有五个或更多数字的字符串。 { m,n} 匹配某个字符至少 m 次，但不超过 n 次。例如，`SIMILAR TO '_{5,10}'` 匹配任何含有 5 到 10（含 5 和 10）个字符的字符串。
\	反斜线被用作元字符的转义字符。它也可被用于转义非元字符。
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
:	在字符集中使用冒号来指定子字符类。例如，`'[[:alnum:]]'`。

注意：

1、多数正则表达式实现使用单个反斜杠转义特殊字符，一遍能使用这些字符本身，但是MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。

2、^ 符号的双重用途： ^在集合中（用 [ ] 定义）时用它来否定该集合，否则用来指定串的开始处。

匹配字符类

[:alpha:]

匹配当前归类中的大写和小写字母字符。例如，'[0-9]{3}[[:alpha:]]{2}' 匹配三个数字，后跟两个字母。

[:alnum:]

匹配当前归类中的数字、大写和小写字母字符。例如，'[[:alnum:]]+' 匹配含有一个或多个字母和数字的字符串。

[:digit:]

匹配当前归类中的数字。例如，'[[:digit:]-]+' 匹配含有一个或多个数字或横线的字符串。同样，'[^[:digit:]-]+' 匹配含有一个或多个不是数字或横线的字符的字符串。

[:lower:]

匹配当前归类中的小写字母字符。例如，'[[:lower:]]' 不匹配 A，因为 A 为大写。

[:space:]

匹配单个空格 (' ')。例如，搜索 Contacts.City 以查找任何名称为两个词的城市：

1 2 3 SELECT City FROM Contacts WHERE City REGEXP '.*[[:space:]].*' ;

[:upper:]

匹配当前归类中的大写字母字符。例如，'[[:upper:]ab]' 与以下其中一项匹配：任何大写字母、a 或 b。

[:whitespace:]

匹配一个空白字符，例如，空格、制表符、换页符和回车符。

[:ascii:]

匹配任何七位的 ASCII 字符（0 到 127 之间的顺序值）。

[:blank:]

匹配一个空白区或水平制表符。

[[:blank:]] 等效于 [ \t]。

[:cntrl:]

匹配顺序值小于 32 或字符值为 127 的 ASCII 字符（控制字符）。控制字符包括换行符、换页符、退格符，等等。

[:graph:]

匹配打印字符。

[[:graph:]] 等效于 [[:alnum:][:punct:]]。

[:print:]

匹配打印字符和空格。

[[:print:]] 等效于 [[:graph:][:whitespace:]]。

[:punct:]

匹配其中一个字符： !"#$%&'()*+,-./:;<=>?@[\]^_`{|}~.

[:punct:] 子字符类不能包括当前归类中可用的非 ASCII 标点字符。

[:word:]

匹配当前归类中的字母、数字或下划线字符。

[[:word:]] 等效于 [[:alnum:]_]。

[:xdigit:]

匹配字符类 [0-9A-Fa-f] 中的字符。

子字符类其它信息

正则表达式支持的其它语法约定

\0xxx

匹配值为 \0xxx 的字符，其中 xxx 是任何八进制数字序列，0 是零。例如，\0134 匹配反斜线。

匹配报警字符。

用在字符集外部以便匹配字符串的开头。

等效于在字符集外部使用的 ^。

匹配退格字符。

匹配反斜线字符 (\)。

\cX

匹配已命名的控制字符。例如，\cZ 代表 ctrl-Z。

匹配当前归类中的一个数字。例如，以下语句搜索 Contacts.Phone 以查找以 00 结尾的所有电话号码：

1 2 3 SELECT Surname, Surname, City, Phone FROM Contacts WHERE Phone REGEXP '\\d{8}00' ;

\d 既可用在字符类的内部也可用在字符类的外部，等效于 [[:digit:]]。

匹配数字以外的任何字符。它的作用与 \d 正好相反。

\D 既可用在字符类的内部也可用在字符类的外部，等效于 [^[:digit:]]。

在方括号内使用取非速记时请务必谨慎。[\D\S] 与 [^\d\s] 并不相同。后者匹配数字或空格以外的任何字符。所以它匹配 x，但不匹配 8。而前者匹配不是数字或不是空格（满足两个条件之一）的任何字符。因为数字不是空格，空格也不是数字，所以 [\D\S] 可以匹配任何字符、数字、空格或其它字符。

匹配转义字符。

将由 \Q 启动的将元字符视为非元字符这一功能停止。

匹配换页符。

匹配换行符。

将所有元字符视为非元字符，直到遇到 \E。例如，\Q[$\E 等效于 \[\$。

匹配回车符。

匹配一个被视为白空格的空格或字符。例如，从 Products.ProductName 中返回名称中至少有一个空格的所有产品名：

1 2 3 SELECT Name FROM Products WHERE Name REGEXP '.*\\s.*' ;

\s 既可用在字符类的内部也可用在字符类的外部，等效于 [[:whitespace:]]。

匹配非白空格字符。它的作用与 \d 正好相反，而等效于 [^[:whitespace:]]。

\S 既可用在字符类的内部也可用在字符类的外部。

匹配水平制表符。

匹配垂直制表符。

匹配当前归类中的字母字符、数字或下划线。例如，从 Contacts.Surname 返回长度正好为七个字母数字字符的所有姓：

1 2 3 SELECT Surname FROM Contacts WHERE Surname REGEXP '\\w{7}' ;

\w 既可用在字符类的内部也可用在字符类的外部。等效于 [[:alnum:]_].。

匹配当前归类中字母字符、数字或下划线以外的任何字符。它的作用与 \w 正好相反，而等效于 [^[:alnum:]_]。

在字符类的内部和外部都可使用此正则表达式。

\xhh

匹配值为 0xhh 的字符，其中 hh 最多为两个十六进制数字。例如，\x2D 等效于一个连字符。等效于 \x{hh}。

\x{ hhh}

匹配值为 0xhhh 的字符，其中 hhh 最多为三个十六进制数字。

\z 和 \Z

匹配字符串结尾处的位置（而非字符）。等效于 $。

正则表达式语法名称和含义

正则表达式断言

断言测试条件是否为真，并影响字符串中开始匹配的位置。断言不返回字符；最终匹配中不包括断言模式。REGEXP 搜索条件和REGEXP_SUBSTR 函数支持断言模式。

在尝试拆分字符串时，lookahead 和 lookbehind 断言对于 REGEXP_SUBSTR 将非常有用。例如，可以通过执行以下语句返回 Customers 表的 Address 列中街道名称（不带街道编号）的列表：

1 2	`SELECT` `REGEXP_SUBSTR( Street,` `'(?<=^\\S+\\s+).*$'` `)` `FROM` `Customers;`

另一个示例：假定您想要使用正则表达式来验证口令是否符合某些规则。可以使用类似于下面内容的零宽度断言：

1 2 3 4 5 IF password REGEXP '(?=.*[[:digit:]])(?=.*[[:alpha:]].*[[:alpha:]])[[:word:]]{4,12}' MESSAGE 'Password conforms' TO CLIENT; ELSE MESSAGE 'Password does not conform' TO CLIENT; END IF

password 至少有一位数（零宽度肯定断言 [[:digit:]]）当满足以下条件时，口令有效：

password 至少有两个字母字符（零宽度肯定断言 [[:alpha:]].*[[:alpha:]]）
password 只含有字母数字字符或下划线字符 ([[:word:]])
password 最少含有 4 个字符，最多含有 12 个字符 ({4,12})

下表包含 SQL Anywhere 支持的断言：

语法	含义
(?=pattern)	肯定的 lookahead 零宽度断言查看字符串中的当前位置是否紧跟着出现了 pattern，而 pattern 不会成为匹配字符串的一部分。`'A(?=B)'` 匹配后面跟有 B 的 A，但不使 B 成为匹配的一部分。例如，`SELECT REGEXP_SUBSTR( 'in new york city', 'new(?=\\syork)');` 会返回子串 new，因为它后面紧跟着 ' york'（请注意 york 前面的空格）。
(?!pattern)	否定的 lookahead 零宽度断言查看字符串中的当前位置是否没有紧跟着出现 pattern，而 pattern 不会成为匹配字符串的一部分。所以，`'A(?!B)'` 匹配后面未跟着 B 的 A。例如，`SELECT REGEXP_SUBSTR('new jersey', 'new(?!\\syork)');` 会返回子串 new。
(?<=pattern)	肯定的 lookbehind 零宽度断言查看字符串中的当前位置是否前面紧挨着出现了 pattern，而 pattern 不会成为匹配字符串的一部分。所以，`'(?<=A)B'` 匹配前面紧挨着 A 的 B，但不使 A 成为匹配的一部分。例如，`SELECT REGEXP_SUBSTR('new york', '(?<=new\\s)york');` 会返回子串 york。
(?<!pattern)	否定的 lookbehind 零宽度断言查看字符串中的当前位置的前面是否没有紧挨着出现 pattern，而 pattern 不会成为匹配字符串的一部分。例如，`SELECT REGEXP_SUBSTR('about york', '(?<!new\\s)york');` 会返回子串 york。
(?>pattern)	所属关系局部子表达式仅匹配与 pattern 匹配的剩余字符串的最大前缀。例如，在 `'aa' REGEXP '(?>a)a'` 中，`(?>a)` 匹配（并消耗）aa，而决不仅仅是前导 a。因此，`'aa' REGEXP '(?>a*)a'` 的计算结果为 false。
(?:pattern)	非捕获块该语法在功能上就等效于 pattern，是为实现兼容性而提供。例如，在 `'bb' REGEXP '(?:b)b'` 中，`(?:b)` 匹配（并消耗）bb。但是，与所属关系局部子表达式不同，bb 中的最后一个 b 会被放弃，以允许整个匹配成功（即，允许与在非捕获块的外部找到的 b 匹配）。同样，`'a(?:bc\|b)c'` 匹配 abcc 和 abc。在匹配 abc 时，bc 中最后面的 c 会发生回溯，以便可以使用组外的 c 来使匹配成功。
(?#text)	用于注释。text 的内容会被忽略。

语法实例

1、基本字符匹配

例如搜索city表中 Population 列包含'1000'的所有信息，并按照 ID 升序排序。

1 2 3 4 SELECT * FROM city WHERE Population REGEXP '1000' ORDER BY ID;

搜索city表中 Population 列包含'000'的所有信息，并按照 ID 升序排序。

1 2 3 4 SELECT * FROM city WHERE Population REGEXP '.000' ORDER BY ID;

注意：这里的点（.）表示匹配任意一个字符，例如，1000,2000,3000等都匹配且返回。

2、进行 OR （使用竖线（|）符号表示）匹配（为搜索多个串之一）

例如搜索city表中 Population 列包含 '1000' 或 '2000' 的所有信息，并按照ID升序排序。

1 2 3 4 SELECT * FROM city WHERE Population REGEXP '1000|2000' ORDER BY ID;

注意：多个 OR 条件可以并入单个正则表达式，例如 '1000|2000|3000' 将匹配1000或2000或3000.

3、匹配几个字符之一（匹配任何单一字符，使用符号中括号（[ ]））

例如搜索city表中 Population 列包含 '1000' 或 '2000' 或 '3000'的所有信息，并按照ID升序排序。

1 2 3 4 SELECT * FROM city WHERE Population REGEXP '[123]000' ORDER BY ID;

注意：[ ] 是另一种形式的 OR 语句，事实上，[123]000 为 [1|2|3]000 的缩写（与 '1000|2000|3000'要一个意思）。

例如搜索city表中 Population 列除 '1000' 或 '2000' 或 '3000'以外的所有信息，并按照ID升序排序。

1 2 3 4 SELECT * FROM city WHERE Population REGEXP '[^123]000' ORDER BY ID;

注意：使用 ^ 符号匹配除中括号（[ ]）之外的任意值，并返回。

4、匹配范围（使用 - 符号）

例如搜索city表中 Population 列 '1000' - '5000'的所有信息，并按照ID升序排序。

1 2 3 4 5 6 7 8 9 SELECT * FROM city WHERE Population REGEXP '[1-5]000' ORDER BY ID; 或 SELECT * FROM city WHERE Population REGEXP '[12345]000' ORDER BY ID;

5、匹配特殊字符（使用 \\ 符号）

例如搜索city表中 name 列中包含 ' . ' 字符值的所有信息。

1 2 3 SELECT * FROM city WHERE name REGEXP '\\.' ;

6、匹配多个实例

（1）、例如搜索city表中name列含有（）、数字0~9、‘Hern’的所有信息

1 2 3 SELECT * FROM city WHERE name REGEXP '\$[0-9] Hern?\$' ;

注意：问号（？）匹配‘Hern’后面出现任何字符的0次或1次。

（2）、例如搜索city表中name列含有连在一起的4位数字的所有信息

1 2 3 4 5 6 7 SELECT * FROM city WHERE name REGEXP '[[:digit:]]{4}' ; 或者 SELECT * FROM city WHERE name REGEXP '[0-9][0-9][0-9][0-9]' ;