下表概括了 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 错误。
一、正则表达式函数和运算符描述
1. NOT REGEXP
语法：expr NOT REGEXP pat 或 expr NOT RLIKE pat 结果同 NOT(expr REGEXP pat)。
2. REGEXP
语法：expr REGEXP pat 或 expr RLIKE pat 如果字符串 expr 与模式 pat 指定的正则表达式匹配，则返回1，否则返回0。如果 expr 或 pat 为 NULL，则返回值为 NULL。REGEXP 和 RLIKE 是 REGEXP_LIKE() 的同义词。
有关如何进行匹配的其他信息，参阅REGEXP_LIKE() 部分的描述。
代码语言：javascript复制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 |
--------------------- 3. REGEXP_INSTR
语法：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() 部分的描述。
代码语言：javascript复制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 |
------------------------------------- 4. REGEXP_LIKE
语法：REGEXP_LIKE(expr, pat[, match_type]) 如果字符串 expr 与模式 pat 指定的正则表达式匹配，则返回1，否则返回0。如果 expr 或 pat 为 NULL，则返回值为 NULL。模式可以是扩展的正则表达式，其语法在正则表达式语法中进行了讨论。模式不需要是文字字符串，它也可以指定为字符串表达式或表列。
可选的 match_type 参数是一个字符串，它可以包含指定如何执行匹配的以下任何字符的组合：
- c：区分大小写。
- i：不区分大小写。
- m：多行模式。识别字符串中的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符（不匹配换行符）。
- n：dotall 模式，字符 . 匹配换行符。默认行为是 . 匹配在换行符处停止。
- u：仅 Unix 的行尾。只有换行符被 .、^ 和 $ 匹配运算符识别为行尾。
如果在 match_type 中指定了指定矛盾选项的字符，则最右边的字符优先。
默认情况下，正则表达式操作在决定字符类型和执行比较时使用 expr 和 pat 参数的字符集和排序规则。如果参数具有不同的字符集或排序规则，则使用强制性转换，参见https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html。可以使用显式指定排序规则参数，以更改比较行为。
代码语言：javascript复制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 模式，在这种情况下不使用转义符。
代码语言：javascript复制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 |
-------------------------------- 5. REGEXP_REPLACE
语法：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 及更高版本中，使用了搜索匹配的表达式的字符集和排序规则。参见 bug#94203，bug#29308212。
有关如何进行匹配的其他信息，参阅REGEXP_LIKE() 部分的描述。
代码语言：javascript复制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 |
---------------------------------------------------- 6. REGEXP_SUBSTR
语法：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 及更高版本中，使用了搜索匹配的表达式的字符集和排序规则。参见 bug#94203，bug#29308212。
有关如何进行匹配的其他信息，参阅REGEXP_LIKE() 部分的描述。
代码语言：javascript复制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('fonfo', '^fo$'); -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^fo'); -> 1
mysql> SELECT REGEXP_LIKE('fonfo', '^fo$', 'm'); -> 1- $：匹配字符串的末尾。
mysql> SELECT REGEXP_LIKE('fono', '^fono$'); -> 1
mysql> SELECT REGEXP_LIKE('fono', '^fo$'); -> 0- .：匹配任何单个字符。要匹配字符串中间的回车符和换行符，必须指定 n（dotall）匹配控制字符，或在匹配模式中指定 (?m) 修饰符。
mysql> SELECT REGEXP_LIKE('fofo', '^f.*$'); -> 1
mysql> SELECT REGEXP_LIKE('fornfo', '^f.*$'); -> 0
mysql> SELECT REGEXP_LIKE('fornfo', '^f.*$', 'n'); -> 1
mysql> SELECT REGEXP_LIKE('fornfo', '(?m)^f.*$'); -> 1- a*：匹配零个或多个 a 字符。
mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n'); -> 1
mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n'); -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n'); -> 1- a ：匹配一个或多个 a 字符。
mysql> SELECT REGEXP_LIKE('Ban', '^Ba n'); -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba n'); -> 0- a?：匹配零个或一个 a 字符。
mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n'); -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n'); -> 1
mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n'); -> 0- de|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}：重复{n}和{m，n}表示法提供了一种更通用的编写正则表达式的方法，这些正则表达式与模式的前一个原子（或“片段”）的多次出现相匹配。m和n是整数。a* 可以写为 a{0,}；a 可以写为 a{1,}；a? 可以写为 a{0,1}。更准确地说，a{n} 正好匹配 a 的 n 个实例。a{n,} 匹配 a 的 n 个或更多个实例。如果同时给定 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 | 小写字母字符 |
图形或空格、制表符、换行符和回车符 | |
punct | 标点符号 |
space | 空格、制表符、换行符和回车符 |
upper | 大写字母字符 |
xdigit | 十六进制数字字符 |
mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]] '); -> 1
mysql> SELECT REGEXP_LIKE('!!', '[[:alnum:]] '); -> 0要在正则表达式中使用特殊字符的文字实例，要在其前面加两个反斜杠 字符。MySQL 解析器解释其中一个反斜杠，正则表达式库解释另一个。例如，要匹配包含特殊 字符的字符串 1 2，只有以下正则表达式中的最后一个是正确的：
代码语言：javascript复制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 国际组件（International Components for Unicode，ICU）。以下讨论描述了 Spencer 和 ICU 库之间可能影响应用程序的差异。
1. 多字节安全问题
对于 Spencer 库，REGEXP 和 RLIKE 运算符以字节方式工作，因此它们不是多字节安全的，并且可能会对多字节字符集产生意外结果。此外，这些运算符通过字节值比较字符，重音字符可能不会被比较为相等，即使给定的排序规则将它们视为相等。
ICU 完全支持 Unicode，并且是多字节安全的。它的正则表达式函数将所有字符串视为 UTF-16。应该记住，位置索引是基于16位块，而不是基于代码点。这意味着，当传递给此类函数时，使用多个块的字符可能会产生意想不到的结果，例如此处所示：
代码语言：javascript复制mysql> SELECT REGEXP_INSTR('
