正则表达式函数和运算符
表正则表达式函数和运算符
名称 | 描述 |
---|---|
NOT REGEXP | 否REGEXP |
REGEXP | 字符串是否匹配正则表达式 |
REGEXP_INSTR() | 子串匹配正则表达式的起始索引 |
REGEXP_LIKE() | 字符串是否匹配正则表达式 |
REGEXP_REPLACE() | 替换匹配正则表达式的子字符串 |
REGEXP_SUBSTR() | 返回匹配正则表达式的子字符串 |
RLIKE | 字符串是否匹配正则表达式 |
正则表达式是为复杂搜索指定模式的有效方法。本节讨论可用于正则表达式匹配的函数和运算符,并举例说明一些可用于正则表达式操作的特殊字符和构造。另请参见“模式匹配(like)”。
MySQL使用Unicode国际组件(ICU)实现了正则表达式支持,该组件提供了完整的Unicode支持并且是多字节安全的。(在MySQL 8.0.4之前,MySQL使用Henry Spencer的正则表达式实现,该实现以字节方式运行,并且不是多字节安全的。有关使用正则表达式的应用程序可能受到实现更改影响的方式的信息,请参见正则表达式兼容性注意事项。)
- 正则表达式函数和运算符
- 正则表达式语法
- 正则表达式资源控制
- 正则表达式兼容性注意事项
正则表达式函数和运算符
expr NOT REGEXP pat
,expr NOT RLIKE pat
这与相同。
NOT(expr REGEXP pat)
expr REGEXP pat
,expr RLIKE pat
如果字符串
expr
与模式指定的正则表达式匹配,则返回1,否则返回pat
0。如果expr
或者pat
是NULL
,返回值是NULL
。REGEXP
并且RLIKE
是同义词REGEXP_LIKE()
。有关如何进行匹配的其他信息,请参见的描述
REGEXP_LIKE()
。mysql>
SELECT 'Michael!' REGEXP '.*'; +------------------------ + | 'Michael!' REGEXP '.*' | +------------------------ + | 1 | +------------------------ + mysql>SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; +--------------------------------------- + | 'new*\n*line' REGEXP 'new\\*.\\*line' | +--------------------------------------- + | 0 | +--------------------------------------- + mysql>SELECT 'a' REGEXP '^[a-d]'; +--------------------- + | 'a' REGEXP '^[a -d]' | +--------------------- + | 1 | +--------------------- + mysql>SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A'; +---------------- +----------------------- + | 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' | +---------------- +----------------------- + | 1 | 0 | +---------------- +----------------------- +REGEXP_INSTR(
expr
,pat
[,pos
[,occurrence
[,return_option
[,match_type
]]]])返回与
expr
模式指定的正则表达式匹配的字符串的子字符串的起始索引pat
,如果不匹配,则返回0。如果expr
或者pat
是NULL
,返回值是NULL
。字符索引从1开始。REGEXP_INSTR()
采用以下可选参数:pos
:expr
开始搜索的位置。如果省略,则默认值为1。occurrence
:要搜索的匹配项。如果省略,则默认值为1。return_option
:要返回的头寸类型。如果此值为0,则REGEXP_INSTR()
返回匹配的子字符串的第一个字符的位置。如果此值为1,则REGEXP_INSTR()
返回匹配的子字符串之后的位置。如果省略,则默认值为0。match_type
:一个字符串,指定如何执行匹配。含义如所描述REGEXP_LIKE()
。
有关如何进行匹配的其他信息,请参见的描述
REGEXP_LIKE()
。mysql>
SELECT REGEXP_INSTR('dog cat dog', 'dog'); +------------------------------------ + | REGEXP_INSTR('dog cat dog', 'dog') | +------------------------------------ + | 1 | +------------------------------------ + mysql>SELECT REGEXP_INSTR('dog cat dog', 'dog', 2); +--------------------------------------- + | REGEXP_INSTR('dog cat dog', 'dog', 2) | +--------------------------------------- + | 9 | +--------------------------------------- + mysql>SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}'); +------------------------------------- + | REGEXP_INSTR('aa aaa aaaa', 'a{2}') | +------------------------------------- + | 1 | +------------------------------------- + mysql>SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}'); +------------------------------------- + | REGEXP_INSTR('aa aaa aaaa', 'a{4}') | +------------------------------------- + | 8 | +------------------------------------- +REGEXP_LIKE(expr,pat[,match_type])
如果字符串
expr
与模式指定的正则表达式匹配,则返回1,否则返回pat
0。如果expr
或者pat
是NULL
,返回值是NULL
。模式可以是扩展的正则表达式,其语法在正则表达式语法中进行了讨论。该模式不必是文字字符串。例如,可以将其指定为字符串表达式或表列。
可选
match_type
参数是一个字符串,可以包含以下任何或所有以下字符,这些字符指定如何执行匹配:c
:区分大小写的匹配。i
:不区分大小写的匹配。m
:多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。n
:.
字符与行终止符匹配。默认值是使.
匹配在行尾停止。u
:仅限Unix的行结尾。只有换行字符识别为结束一条线.
,^
以及$
匹配运算符。
如果在中指定了指定矛盾选项的字符
match_type
,则最右边的字符优先。默认情况下,在确定字符类型并执行比较时,正则表达式操作使用字符集以及
expr
和pat
参数的校验规则。如果参数具有不同的字符集或校验规则,则按照“表达式中的校验规则强制性”中所述应用强制性规则。可以使用显式排序指示符指定参数以更改比较行为。mysql>
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE'); +--------------------------------------- + | REGEXP_LIKE('CamelCase', 'CAMELCASE') | +--------------------------------------- + | 1 | +--------------------------------------- + mysql>SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE'COLLATE utf8mb4_0900_as_cs); +------------------------------------------------------------------ + | REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) | +------------------------------------------------------------------ + | 0 | +------------------------------------------------------------------ +match_type
可以用c
或i
字符指定,以覆盖默认的区分大小写。例外:如果任何一个参数都是二进制字符串,则即使它们match_type
包含i
字符,也会以区分大小写的方式将其作为二进制字符串处理。注意
因为MySQL在字符串中使用C转义语法(例如,
\n
表示换行符),所以您必须\
对expr
和pat
参数中使用的任何值加倍。mysql>
SELECT REGEXP_LIKE('Michael!', '.*'); +------------------------------- + | REGEXP_LIKE('Michael!', '.*') | +------------------------------- + | 1 | +------------------------------- + mysql>SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line'); +---------------------------------------------- + | REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') | +---------------------------------------------- + | 0 | +---------------------------------------------- + mysql>SELECT REGEXP_LIKE('a', '^[a-d]'); +---------------------------- + | REGEXP_LIKE('a', '^[a -d]') | +---------------------------- + | 1 | +---------------------------- + mysql>SELECT REGEXP_LIKE('a', 'A'), REGEXP_LIKE('a', BINARY 'A'); +----------------------- +------------------------------ + | REGEXP_LIKE('a', 'A') | REGEXP_LIKE('a', BINARY 'A') | +----------------------- +------------------------------ + | 1 | 0 | +----------------------- +------------------------------ +mysql>
SELECT REGEXP_LIKE('abc', 'ABC'); +--------------------------- + | REGEXP_LIKE('abc', 'ABC') | +--------------------------- + | 1 | +--------------------------- + mysql>SELECT REGEXP_LIKE('abc', 'ABC', 'c'); +-------------------------------- + | REGEXP_LIKE('abc', 'ABC', 'c') | +-------------------------------- + | 0 | +-------------------------------- +REGEXP_REPLACE(
expr
,pat
,repl
[,pos
[,occurrence
[,match_type
]]])替代对象出现在字符串中的
expr
匹配由模式指定正则表达式的pat
与所述替换字符串repl
,并返回生成的字符串。如果expr
,pat
或repl
时NULL
,返回值是NULL
。REGEXP_REPLACE()
采用以下可选参数:pos
:expr
开始搜索的位置。如果省略,则默认值为1。occurrence
:替换哪个匹配项。如果省略,则默认值为0(表示“替换所有出现的内容”)。match_type
:一个字符串,指定如何执行匹配。含义如所描述REGEXP_LIKE()
。
在MySQL 8.0.17之前,此函数返回的结果使用
UTF-16
字符集。在MySQL 8.0.17及更高版本中,使用字符集和搜索匹配项的表达式的校验规则。错误#94203,错误#29308212)有关如何进行匹配的其他信息,请参见的描述
REGEXP_LIKE()
。mysql>
SELECT REGEXP_REPLACE('a b c', 'b', 'X'); +----------------------------------- + | REGEXP_REPLACE('a b c', 'b', 'X') | +----------------------------------- + | a X c | +----------------------------------- + mysql>SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3); +---------------------------------------------------- + | REGEXP_REPLACE('abc def ghi', '[a -z] +', 'X', 1, 3) | +---------------------------------------------------- + | abc def X | +---------------------------------------------------- +REGEXP_SUBSTR(
expr
,pat
[,pos
[,occurrence
[,match_type
]]])如果不匹配,则返回与
expr
模式指定的正则表达式匹配的字符串的子字符串。如果或者是,返回值是。pat
NULL
expr
pat
NULL
NULL
REGEXP_SUBSTR()
采用以下可选参数:pos
:expr
开始搜索的位置。如果省略,则默认值为1。occurrence
:要搜索的匹配项。如果省略,则默认值为1。match_type
:一个字符串,指定如何执行匹配。含义如所描述REGEXP_LIKE()
。
在MySQL 8.0.17之前,此函数返回的结果使用
UTF-16
字符集。在MySQL 8.0.17及更高版本中,使用字符集和搜索匹配项的表达式的校验规则。错误#94203,错误#29308212)有关如何进行匹配的其他信息,请参见的描述
REGEXP_LIKE()
。mysql>
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+'); +---------------------------------------- + | REGEXP_SUBSTR('abc def ghi', '[a -z] +') | +---------------------------------------- + | abc | +---------------------------------------- + mysql>SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3); +---------------------------------------------- + | REGEXP_SUBSTR('abc def ghi', '[a -z] +', 1, 3) | +---------------------------------------------- + | ghi | +---------------------------------------------- +
正则表达式语法
正则表达式描述了一组字符串。最简单的正则表达式是其中没有特殊字符的表达式。例如,正则表达式hello
匹配hello
,仅此而已。
非平凡的正则表达式使用某些特殊的构造,以便它们可以匹配多个字符串。例如,正则表达式hello|world
包含|
交替运算符并匹配hello
或world
。
作为一个更复杂的实例中,正则表达式B[an]*s
匹配的任何串的Bananas
,Baaaaas
,Bs
,和开始用任何其他字符串B
,与结束s
,和含有任何数目的a
或n
在字符之间。
下表列出了可在正则表达式中使用的一些基本特殊字符和构造。有关用于实现正则表达式支持的ICU库所支持的完整正则表达式语法的信息,请访问 Unicode国际组件网站。
^
匹配字符串的开头。
mysql>
SELECT REGEXP_LIKE('fo\nfo', '^fo$'); -> 0 mysql>SELECT REGEXP_LIKE('fofo', '^fo'); -> 1$
匹配字符串的结尾。
mysql>
SELECT REGEXP_LIKE('fo\no', '^fo\no$'); -> 1 mysql>SELECT REGEXP_LIKE('fo\no', '^fo$'); -> 0.
匹配任何字符(包括回车符和换行符,尽管要在字符串中间匹配它们,必须提供
m
(多行)匹配控制字符或(?m)
内部模式修饰符)。mysql>
SELECT REGEXP_LIKE('fofo', '^f.*$'); -> 1 mysql>SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$'); -> 0 mysql>SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm'); -> 1 mysql>SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$'); -> 1a*
匹配任何零个或多个
a
字符的序列。mysql>
SELECT REGEXP_LIKE('Ban', '^Ba*n'); -> 1 mysql>SELECT REGEXP_LIKE('Baaan', '^Ba*n'); -> 1 mysql>SELECT REGEXP_LIKE('Bn', '^Ba*n'); -> 1a+
匹配任何一个或多个
a
字符的序列。mysql>
SELECT REGEXP_LIKE('Ban', '^Ba+n'); -> 1 mysql>SELECT REGEXP_LIKE('Bn', '^Ba+n'); -> 0a?
匹配零个或一个
a
字符。mysql>
SELECT REGEXP_LIKE('Bn', '^Ba?n'); -> 1 mysql>SELECT REGEXP_LIKE('Ban', '^Ba?n'); -> 1 mysql>SELECT REGEXP_LIKE('Baan', '^Ba?n'); -> 0de|abc
交替;与序列
de
或匹配abc
。mysql>
SELECT REGEXP_LIKE('pi', 'pi|apa'); -> 1 mysql>SELECT REGEXP_LIKE('axe', 'pi|apa'); -> 0 mysql>SELECT REGEXP_LIKE('apa', 'pi|apa'); -> 1 mysql>SELECT REGEXP_LIKE('apa', '^(pi|apa)$'); -> 1 mysql>SELECT REGEXP_LIKE('pi', '^(pi|apa)$'); -> 1 mysql>SELECT REGEXP_LIKE('pix', '^(pi|apa)$'); -> 0(abc)*
匹配序列的零个或多个实例
abc
。mysql>
SELECT REGEXP_LIKE('pi', '^(pi)*$'); -> 1 mysql>SELECT REGEXP_LIKE('pip', '^(pi)*$'); -> 0 mysql>SELECT REGEXP_LIKE('pipi', '^(pi)*$'); -> 1{1}
,{2,3}
重复;和符号提供了一种更通用的方式来编写与该模式的前一个原子(或“ piece ”)匹配的正则表达式。和是整数。
{n}
{m,n}
m
n
a*
可以写成
a{0,}
。a+
可以写成
a{1,}
。a?
可以写成
a{0,1}
。
更精确地说,完全匹配的实例。匹配或更多个实例。通过,包括个实例进行匹配。如果同时给出和,则必须小于或等于。
a{n}
n
a
a{n,}
n
a
a{m,n}
m
n
a
m
n
m
n
mysql>
SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e'); -> 0 mysql>SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e'); -> 1 mysql>SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e'); -> 1[a-dX]
,[^a-dX]
匹配是(或不是,如果任何字符
^
使用的)任一a
,b
,c
,d
或X
。甲-
其它两个字符之间的字符形成一个所有字符从所述第一字符向所述第二匹配范围。例如,[0-9]
匹配任何十进制数字。要包含文字]
字符,它必须紧跟在左括号之后[
。要包含文字-
字符,必须先写后写。字符内没有定义特殊含义的任何字符[]
对仅匹配自身。mysql>
SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]'); -> 1 mysql>SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$'); -> 0 mysql>SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$'); -> 1 mysql>SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$'); -> 0 mysql>SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$'); -> 1 mysql>SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$'); -> 0[=character_class=]
在方括号表达式(使用
[
和编写]
)中,[=character_class=]
表示一个等效类。它匹配所有具有相同归类值的字符,包括自身。例如,如果o
和(+)
是一个等价类的成员,[[=o=]]
,[[=(+)=]]
,和[o(+)]
都是同义。等效类不能用作范围的终点。[:character_class:]
在方括号表达式(使用
[
和编写]
)中,[:character_class:]
表示与所有属于该类的字符匹配的字符类。下表列出了标准的类名称。这些名称代表ctype(3)
手册页中定义的字符类。特定的语言环境可以提供其他类名称。字符类不能用作范围的端点。角色类别名称 含义 alnum
字母数字字符 alpha
字母字符 blank
空格字符 cntrl
控制字符 digit
数字字符 graph
图形字符 lower
小写字母字符 print
图形或空格字符 punct
标点符号 space
空格,制表符,换行符和回车符 upper
大写字母字符 xdigit
十六进制数字字符 mysql>
SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+'); -> 1 mysql>SELECT REGEXP_LIKE('!!', '[[:alnum:]]+'); -> 0
要在正则表达式中使用特殊字符的文字实例,请在其前面加上两个反斜杠(\)字符。MySQL解析器解释一个反斜杠,而正则表达式库解释另一个反斜杠。例如,为了匹配1+2
包含特殊+
字符的字符串,只有以下正则表达式中的最后一个才是正确的:
mysql>SELECT REGEXP_LIKE('1+2', '1+2'); -> 0 mysql>SELECT REGEXP_LIKE('1+2', '1\+2'); -> 0 mysql>SELECT REGEXP_LIKE('1+2', '1\\+2'); -> 1
正则表达式资源控制
REGEXP_LIKE()
类似的功能使用可以通过设置系统变量来控制的资源:
- 匹配引擎将内存用于其内部堆栈。要控制堆栈的最大可用内存(以字节为单位),请设置
regexp_stack_limit
系统变量。 - 比赛引擎会逐步操作。要控制引擎执行的最大步数(从而间接控制执行时间),请设置
regexp_time_limit
系统变量。因为此限制表示为步骤数,所以它仅间接影响执行时间。通常,它是毫秒级的。
正则表达式兼容性注意事项
在MySQL 8.0.4之前,MySQL使用Henry Spencer正则表达式库来支持正则表达式操作,而不是用于Unicode的国际组件(ICU)。以下讨论描述了可能影响应用程序的Spencer库和ICU库之间的差异:
使用Spencer库,
REGEXP
andRLIKE
运算符以字节方式工作,因此它们不是多字节安全的,并且使用多字节字符集可能会产生意外的结果。此外,这些运算符按字节值比较字符,即使给定的归类将它们视为相等,重音字符也可能不相等。ICU具有完全的Unicode支持,并且是多字节安全的。它的正则表达式函数将所有字符串视为
UTF-16
。您应该记住,位置索引基于16位块,而不基于代码点。这意味着,当传递给此类函数时,使用多个块的字符可能会产生意外的结果,例如以下所示:mysql>
SELECT REGEXP_INSTR('🍣🍣b', 'b'); +-------------------------- + | REGEXP_INSTR('??b', 'b') | +-------------------------- + | 5 | +-------------------------- + 1 row in set (0.00 sec) mysql>SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4); +-------------------------------- + | REGEXP_INSTR('??bxxx', 'b', 4) | +-------------------------------- + | 5 | +-------------------------------- + 1 row in set (0.00 sec)在这方面,Unicode基本多语言平面中的字符(包括大多数现代语言使用的字符)是安全的:
mysql>
SELECT REGEXP_INSTR('бжb', 'b'); +---------------------------- + | REGEXP_INSTR('бжb', 'b') | +---------------------------- + | 3 | +---------------------------- + 1 row in set (0.00 sec) mysql>SELECT REGEXP_INSTR('עבb', 'b'); +---------------------------- + | REGEXP_INSTR('עבb', 'b') | +---------------------------- + | 3 | +---------------------------- + 1 row in set (0.00 sec) mysql>SELECT REGEXP_INSTR('µå周çб', '周'); +------------------------------------ + | REGEXP_INSTR('µå周çб', '周') | +------------------------------------ + | 3 | +------------------------------------ + 1 row in set (0.00 sec)表情符号,例如前两个示例中使用的“寿司”字符
🍣
(U + 1F363),不包含在基本多语言平面中,而是包含在Unicode的补充多语言平面中。当表情符号和其他4字节字符出现时,可能会出现另一个问题REGEXP_SUBSTR()
或类似的功能开始在字符中间进行搜索。以下示例中的两个语句均从第一个参数的第二个2字节位置开始。第一条语句处理仅由2字节(BMP)字符组成的字符串。第二条语句包含4个字节的字符,由于前两个字节被剥离,因此字符数据的其余部分未对齐,因此结果中的解释不正确。mysql>
SELECT REGEXP_SUBSTR('周周周周', '.*', 2); +---------------------------------------- + | REGEXP_SUBSTR('周周周周', '.*', 2) | +---------------------------------------- + | 周周周 | +---------------------------------------- + 1 row in set (0.00 sec) mysql>SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2); +-------------------------------- + | REGEXP_SUBSTR('????', '.*', 2) | +-------------------------------- + | ?㳟揘㳟揘㳟揘 | +-------------------------------- + 1 row in set (0.00 sec)- 对于
.
操作员,Spencer库在字符串表达式中的任何位置(包括中间)匹配行终止符(回车符,换行符)。要使用ICUm
匹配字符串中间的行终止符,请指定匹配控制字符。 - Spencer库支持单词开头和单词结尾边界标记(
[[:<:]]
和[[:>:]]
表示法)。ICU没有。对于ICU,您可以\b
用来匹配单词边界;将反斜杠加倍,因为MySQL会将其解释为字符串中的转义字符。 - Spencer库支持整理元素括号表达式(
[.characters.]
表示法)。ICU没有。 - 对于重复计数(
{n}
和{m,n}
计数),Spencer库的最大值为255。ICU没有这样的限制,尽管可以通过设置regexp_time_limit
系统变量来限制匹配引擎步骤的最大数量。 ICU将括号解释为元字符。要
(
在正则表达式中指定文字打开或关闭括号,必须将其转义:mysql>
SELECT REGEXP_LIKE('(', '('); ERROR 3692 (HY000): Mismatched parenthesis in regular expression. mysql>SELECT REGEXP_LIKE('(', '\\('); +------------------------- + | REGEXP_LIKE('(', '\\(') | +------------------------- + | 1 | +------------------------- + mysql>SELECT REGEXP_LIKE(')', ')'); ERROR 3692 (HY000): Mismatched parenthesis in regular expression. mysql>SELECT REGEXP_LIKE(')', '\\)'); +------------------------- + | REGEXP_LIKE(')', '\\)') | +------------------------- + | 1 | +------------------------- +ICU还将方括号解释为元字符,但是只有转义方括号才可以用作文字字符:
mysql>
SELECT REGEXP_LIKE('[', '['); ERROR 3696 (HY000): The regular expression contains an unclosed bracket expression. mysql>SELECT REGEXP_LIKE('[', '\\['); +------------------------- + | REGEXP_LIKE('[', '\\[') | +------------------------- + | 1 | +------------------------- + mysql>SELECT REGEXP_LIKE(']', ']'); +----------------------- + | REGEXP_LIKE(']', ']') | +----------------------- + | 1 | +----------------------- +