MySQL正则表达式REGEXP使用详解

更新时间:2022-09-20 10:11:31

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

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' 中是否含有数字

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 以查找任何名称为两个词的城市:

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匹配反斜线。

\a

匹配报警字符。

\A

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

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

\b

匹配退格字符。

\B

匹配反斜线字符 (\)。

\cX

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

\d

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

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

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

\D

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

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

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

\e

匹配转义字符。

\E

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

\f

匹配换页符。

\n

匹配换行符。

\Q

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

\r

匹配回车符。

\s

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

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

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

\S

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

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

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

\t

匹配水平制表符。

\v

匹配垂直制表符。

\w

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

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

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

\W

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

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

\xhh

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

\x{hhh}

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

\z\Z

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

正则表达式断言

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

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

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

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

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 升序排序。

SELECT *
FROM city
WHERE Population REGEXP '1000'
ORDER BY ID;

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

SELECT *
FROM city
WHERE Population REGEXP '.000'
ORDER BY ID;

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

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

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

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升序排序。

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升序排序。

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

注意:使用 ^ 符号匹配除中括号([ ])之外的任意值,并返回。

4、匹配范围(使用 - 符号)

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

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 列中包含 ' .' 字符值的所有信息。

SELECT *
FROM city
WHERE name REGEXP '\\.';

6、匹配多个实例

(1)、例如搜索city表中name列含有()、数字0~9、&lsquo;Hern&rsquo;的所有信息

SELECT *
FROM city
WHERE name REGEXP '\\([0-9] Hern?\\)';

注意:问号(?)匹配&lsquo;Hern&rsquo;后面出现任何字符的0次或1次。

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

SELECT *
FROM city
WHERE name REGEXP '[[:digit:]]{4}';
或者
SELECT *
FROM city
WHERE name REGEXP '[0-9][0-9][0-9][0-9]';

7、定位符

例如搜索city表中name列开始位置含有连在一起的4位数字的所有信息

SELECT *
FROM city
WHERE name REGEXP '^[0-9]{4}';

MySQL