下表概括了 MySQL 正则表达式函数和运算符。
名称 | 描述 |
---|---|
NOT REGEXP | REGEXP的逆运算 |
REGEXP | 字符串是否与正则表达式匹配 |
REGEXP_INSTR() | 匹配正则表达式的子字符串的起始位置 |
REGEXP_LIKE() | 字符串是否与正则表达式匹配 |
REGEXP_REPLACE() | 替换与正则表达式匹配的子字符串 |
REGEXP_SUBSTR() | 返回与正则表达式匹配的子字符串 |
RLIKE | 字符串是否与正则表达式匹配 |
MySQL 使用 International Components for Unicode(ICU)实现正则表达式,该组件提供了完整的 Unicode 支持,并且是多字节安全的。在 MySQL 8.0.4 之前,MySQL 使用 Henry Spencer 的正则表达式实现,该实现以字节方式运行,不安全。
在 MySQL 8.0.22 之前,可以在这些函数中使用二进制字符串参数,但会产生不确定的结果。在 MySQL 8.0.22 及更高版本中,二进制字符串与任何 MySQL 正则表达式函数一起使用时会返回 ER_CHARACTER_SET_MISMATCH 错误。
语法:expr NOT REGEXP pat 或 expr NOT RLIKE pat 结果同 NOT(expr REGEXP pat)。
语法:expr REGEXP pat 或 expr RLIKE pat 如果字符串 expr 与模式 pat 指定的正则表达式匹配,则返回1,否则返回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'; -- 非 dotall 模式,点好 . 不匹配换行符。
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
| 0 |
+---------------------------------------+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
| 1 |
+---------------------+
语法:REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]]) 返回字符串 expr 中与模式 pat 指定的正则表达式匹配的子字符串的起始位置,如果不匹配,则返回0。如果 expr 或 pat 为 NULL,则返回值为 NULL。字符位置从 1 开始。
REGEXP_INSTR() 接受以下可选参数:
有关如何进行匹配的其他信息,参阅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', 1, 1, 0);
+---------------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 0) |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 1);
+---------------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 1) |
+---------------------------------------------+
| 4 |
+---------------------------------------------+
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 与模式 pat 指定的正则表达式匹配,则返回1,否则返回0。如果 expr 或 pat 为 NULL,则返回值为 NULL。模式可以是扩展的正则表达式,其语法在正则表达式语法中进行了讨论。模式不需要是文字字符串,它也可以指定为字符串表达式或表列。
可选的 match_type 参数是一个字符串,它可以包含指定如何执行匹配的以下任何字符的组合:
如果在 match_type 中指定了指定矛盾选项的字符,则最右边的字符优先。
默认情况下,正则表达式操作在决定字符类型和执行比较时使用 expr 和 pat 参数的字符集和排序规则。如果参数具有不同的字符集或排序规则,则使用强制性转换,参见https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html。可以使用显式指定排序规则参数,以更改比较行为。
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 参数包含文本 一个斜杠 \,则必须写两个斜杠 \\。除非启用了 NO_BACKSLASH_ESCAPES SQL 模式,在这种情况下不使用转义符。
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('new*\n*line', 'new\\*.\\*line','n');
+--------------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line','n') |
+--------------------------------------------------+
| 1 |
+--------------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
| 1 |
+----------------------------+
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() 接受以下可选参数:
在 MySQL 8.0.17 之前,此函数返回的结果使用了 UTF-16 字符集;在 MySQL 8.0.17 及更高版本中,使用了搜索匹配的表达式的字符集和排序规则。参见 bug#94203,bug#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() 接受以下可选参数:
在 MySQL 8.0.17 之前,此函数返回的结果使用了 UTF-16 字符集;在 MySQL 8.0.17 及更高版本中,使用了搜索匹配的表达式的字符集和排序规则。参见 bug#94203,bug#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、Baaaas、Bs 中的任何一个,以及以 B 开头、以 s 结尾并包含介于两者之间的任意数量的 a 或 n 字符的任何字符串。
下面的列表涵盖了一些可以在正则表达式中使用的基本特殊字符和构造。有关用于实现正则表达式的 ICU 库所支持的完整正则表达式语法信息,参见International Components for Unicode web site。
mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$'); -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^fo'); -> 1
mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$', 'm'); -> 1
mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$'); -> 1
mysql> SELECT REGEXP_LIKE('fo\no', '^fo$'); -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^f.*$'); -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$'); -> 0
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'n'); -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$'); -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n'); -> 1
mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n'); -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n'); -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n'); -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n'); -> 0
mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n'); -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n'); -> 1
mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n'); -> 0
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
mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$'); -> 1
mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$'); -> 0
mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$'); -> 1
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
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
字符类名称 | 含义 |
---|---|
alnum | 字母数字字符 |
alpha | 字母字符 |
blank | 空白字符 |
cntrl | 控制字符 |
digit | 数字字符 |
graph | 图形字符 |
lower | 小写字母字符 |
图形或空格、制表符、换行符和回车符 | |
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() 和类似函数使用的资源可以通过设置系统变量来控制:
在 MySQL 8.0.4 之前,MySQL 使用 Henry Spencer 库来支持正则表达式操作,而不是 Unicode 国际组件(International Components for Unicode,ICU)。以下讨论描述了 Spencer 和 ICU 库之间可能影响应用程序的差异。
对于 Spencer 库,REGEXP 和 RLIKE 运算符以字节方式工作,因此它们不是多字节安全的,并且可能会对多字节字符集产生意外结果。此外,这些运算符通过字节值比较字符,重音字符可能不会被比较为相等,即使给定的排序规则将它们视为相等。
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)
但对于 Emoji 表情符号,如前两个例子中使用的(U+1F363)不包括在基本多语言平台中,而是包括在 Unicode 的补充多语言平台中。当 REGEXP_SUBSTR() 或类似函数开始在字符中间搜索时,表情符号和其他4字节字符可能会出现另一个问题。以下示例中的两个语句中的每一个都从第一个参数中的第二个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 库匹配字符串表达式中任何位置的行尾字符(回车符、换行符),包括在中间。而要将字符串中间的行结束符字符与 ICU 匹配,要指定 n 匹配控制字符。
Spencer 库支持单词开始和单词结束边界标记 [[:<:]] and [[:>:]] 。对于 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 |
+-----------------------+