一、选择操作
简单地说，选择操作可在多个可选模式中匹配一个。例如想找出 the 出现过多少次，包括THE、The 和 the 等形式。为此就可以使用选择操作 (the|The|THE) ：
代码语言：javascript复制mysql> select regexp_like('the','(the|The|THE)');
------------------------------------
| regexp_like('the','(the|The|THE)') |
------------------------------------
| 1 |
------------------------------------
1 row in set (0.00 sec)1. 选项和修饰符
可以使用一个选项来使分组更简短。借助选项，可以指定查找模式的方式。例如 (?i) 选项让模式不再区分大小写，因此原来带选择操作的模式可以简写成 (?i)the ：
代码语言：javascript复制mysql> select regexp_like('THE','(?i)the');
------------------------------
| regexp_like('THE','(?i)the') |
------------------------------
| 1 |
------------------------------
1 row in set (0.00 sec)此时即便使用 regexp_like 函数的选项 c 也不会起到区分大小写的效果，因为正则表达式中的 (?i) 会匹配所有大小写组合。
代码语言：javascript复制mysql> select regexp_like('THE','the'),regexp_like('THE','the','c'),regexp_like('THE','(?i)the','c')G
*************************** 1. row ***************************
regexp_like('THE','the'): 1
regexp_like('THE','the','c'): 0
regexp_like('THE','(?i)the','c'): 1
1 row in set (0.00 sec)下表中列出了各种选项和修饰符，MySQL支持除 (?J) 和 (?U) 以外的其它选项。
选项 | 描述 | 支持平台 |
|---|---|---|
(?d) | Unix中的行 | Java |
(?i) | 不区分大小写 | PCRE、Perl、Jave |
(?J) | 允许重复的名字 | PCRE* |
(?m) | 多行 | PCRE、Perl、Java |
(?s) | 单行（dotall） | PCRE、Perl、Java |
(?u) | Unicode | Java |
(?U) | 默认最短匹配 | PCRE |
(?x) | 忽略空格和注释 | PCRE、Perl、Java |
(?-…) | 复原或关闭选项 | PCRE |
*参见http://www.pcre.org/pcre.txt中的“Named Subpatterns”（命名子模式）。
2. 统计单词出现的行数
要对单词 the 出现一次或多次的行的数目进行统计，且不区分大小写。为了更切合数据库的实际应用场景，先给原表增加一个ID主键列。
代码语言：javascript复制alter table t_regexp add id int auto_increment primary key first;统计单词出现的行数的需求实现如下。
代码语言：javascript复制select id,sum(regexp_like(b,'\bthe\b(?i)'))
from (
select id,substring_index(substring_index(a,char(10),lv),char(10),-1) b, lv
from t_regexp,
(with recursive tab1(lv) as (
select 1 lv union all select t1.lv 1 from tab1 t1 where lv < 20 )
select lv from tab1) t
where t.lv <= (length(a) - length(replace(a,char(10),''))) 1) t
group by id;MySQL正则表达式虽然提供了“多行模式”，但只能用于匹配时的比较，并未提供按行统计的接口。对于数据库来说，n 只是一个普通字符，整个字符串还是一行。因此要实现 n 分割的多行统计，需要将单行拆分为多行，再进行后续的匹配和汇总求和。按固定分隔符将一行分成多行的经典方法，就是先使用笛卡尔积连接制造出行，再用两个嵌套的substring_index函数取子串。MySQL 8提供了递归查询，可以轻松构造序列表，以进行笛卡尔积连接。
最内层查询构造出1-20的数字序列表，其中的20是变量，为预估的最大 n 个数：
代码语言：javascript复制mysql> with recursive tab1(lv) as (
-> select 1 lv union all select t1.lv 1 from tab1 t1 where lv < 20 )
-> select lv from tab1;
------
| lv |
------
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
------
20 rows in set (0.00 sec)中间层的查询，将一行按换行符 n 分割成多行；
代码语言：javascript复制mysql> select id,substring_index(substring_index(a,char(10),lv),char(10),-1) b, lv
-> from t_regexp,
-> (with recursive tab1(lv) as (
-> select 1 lv union all select t1.lv 1 from tab1 t1 where lv < 20 )
-> select lv from tab1) t
-> where t.lv <= (length(a) - length(replace(a,char(10),''))) 1;
---- --------------------------------------------------------------------------------- ------
| id | b | lv |
---- --------------------------------------------------------------------------------- ------
| 1 | THE RIME OF THE ANCYENT MARINERE, IN SEVEN PARTS. | 1 |
| 1 | ARGUMENT. | 2 |
| 1 | How a Ship having passed the Line was driven by Storms to the cold Country | 3 |
| 1 | towards the South Pole; and how from thence she made her course to the tropical | 4 |
| 1 | Latitude of the Great Pacific Ocean; and of the strange things that befell; | 5 |
| 1 | and in what manner the Ancyent Marinere came back to his own Country. | 6 |
| 1 | I. | 7 |
| 1 | 1 It is an ancyent Marinere, | 8 |
| 1 | 2 And he stoppeth one of three: | 9 |
| 1 | 3 "By thy long grey beard and thy glittering eye | 10 |
| 1 | 4 "Now wherefore stoppest me? | 11 |
| 2 | THE RIME OF THE ANCYENT MARINERE, IN SEVEN PARTS. | 1 |
| 3 | ARGUMENT. | 1 |
| 4 | How a Ship having passed the Line was driven by Storms to the cold Country | 1 |
| 5 | towards the South Pole; and how from thence she made her course to the tropical | 1 |
| 6 | Latitude of the Great Pacific Ocean; and of the strange things that befell; | 1 |
| 7 | and in what manner the Ancyent Marinere came back to his own Country. | 1 |
| 8 | I. | 1 |
| 9 | 1 It is an ancyent Marinere, | 1 |
| 10 | 2 And he stoppeth one of three: | 1 |
| 11 | 3 "By thy long grey beard and thy glittering eye | 1 |
| 12 | 4 "Now wherefore stoppest me? | 1 |
---- --------------------------------------------------------------------------------- ------
22 rows in set (0.00 sec)最外层按行ID分组求和，统计每数据库行中，单词 the 出现的以 n 分割的行数：
代码语言：javascript复制mysql> select id,sum(regexp_like(b,'\bthe\b(?i)'))
-> from (
-> select id,substring_index(substring_index(a,char(10),lv),char(10),-1) b, lv
-> from t_regexp,
-> (with recursive tab1(lv) as (
-> select 1 lv union all select t1.lv 1 from tab1 t1 where lv < 20 )
-> select lv from tab1) t
-> where t.lv <= (length(a) - length(replace(a,char(10),''))) 1) t
-> group by id;
---- -------------------------------------
| id | sum(regexp_like(b,'\bthe\b(?i)')) |
---- -------------------------------------
| 1 | 5 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
---- -------------------------------------
12 rows in set (0.00 sec)二、子模式
多数情况下，提到正则表达式中的子模式（subpattern），就是指分组中的一个或多个分组。子模式就是模式中的模式。多数情况下，子模式中的条件能得到匹配的前提是前面的模式得到匹配，但也有例外。子模式的写法可以有很多种，这里我们主要关注括号中的子模式，如之前所见的模式 (the|The|THE) 有三个子模式：the 是第一个子模式，The 是第二个，而 THE 是第三个。但是这种情况下，匹配第二个子模式不依赖于是否匹配第一个。（最左边的模式会首先匹配。）而在以下的模式中，子模式依赖于前面的模式：
代码语言：javascript复制(t|T)h(e|eir)这个模式会匹配字面值 t 或 T，然后是一个 h，接下来就是一个 e 或者是 eir。相应地，这个模式会匹配以下四种情况：the、The、their、Their。
在以上情况中，第二个子模式 (e|eir) 依赖于第一个子模式 (t|T)。括号对于子模式不是必需的。下面是一个使用字符组的子模式示例：
代码语言：javascript复制b[tT]h[ceinry]*b这个模式会匹配 the 或 The 还有th、thee、thy 以及 thence 等单词。两个单词边界（b）表示该模式只匹配整个单词，而不会匹配单词中的某几个字母。
代码语言：javascript复制mysql> set @r:='\b[tT]h[ceinry]*\b';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_like('th',@r),regexp_like('thee',@r),regexp_like('thy',@r),regexp_like('thence',@r);
---------------------- ------------------------ ----------------------- --------------------------
| regexp_like('th',@r) | regexp_like('thee',@r) | regexp_like('thy',@r) | regexp_like('thence',@r) |
---------------------- ------------------------ ----------------------- --------------------------
| 1 | 1 | 1 | 1 |
---------------------- ------------------------ ----------------------- --------------------------
1 row in set (0.00 sec)以下是对这个模式的解析。
- b匹配单词起始边界。
- [tT] 是字符组，它匹配小写字母 t 或者大写字母 T。可以将其看做是第一个子模式。
- 然后匹配（或尝试匹配）小写字母 h。
- 第二个也就是最后一个子模式也表示为字符组 [ceinry]，其后用量词 * 表示零个或多个。
- 最后，该模式以另外一个 b 结束。
正则表达式的术语经常是含义相近但使用范围迥异，也有人认为字符组不是子模式。这里的观点是它们与子模式起到的作用一样，所以两者可以归为一类。
三、捕获分组和后向引用
当一个模式的全部或者部分内容由一对括号分组时，它就对内容进行捕获并临时存储于内存中。可以通过后向引用引用捕获的内容，形式为 1 或 1 。这里 1 或 1 引用的是第一个捕获的分组，而 2 或 2 引用第二个捕获的分组，以此类推。MySQL只接受 1 这种形式。
下面再展示一下后向引用的使用方法。我们将使用它来重新排序诗文中的一行词。
代码语言：javascript复制mysql> select regexp_replace('It is an ancyent Marinere','(It is) (an ancyent Marinere)','\2 \1') c1,
-> regexp_replace('It is an ancyent Marinere','(It is) (an ancyent Marinere)','$2 $1') c2;
------ ---------------------------
| c1 | c2 |
------ ---------------------------
| 2 1 | an ancyent Marinere It is |
------ ---------------------------
1 row in set (0.00 sec)使用 regexp_replace 正则替换函数，将原文分成两个捕获分组用作正则表达式，替换字符串引用两个捕获分组并颠倒顺序。要用sed得到相同结果，可以这样做
代码语言：javascript复制sed -En 's/(It is) (an ancyent Marinere)/2 1/p' rime.txt输出为：
代码语言：javascript复制1 an ancyent Marinere It is,下面我们来分析一下这个 sed 命令。
- -E选项仍然是调用ERE（扩展的正则表达式），因此，括号可以直接当成字面值来使用了。
- -n选项覆盖打印每一行的默认设置。
- 替换命令搜索与文本“It is an ancyent Marinere”匹配的内容，再将其捕获放入两个分组中。
- 替换命令还将捕获的文本重排为先是后向引用 2 的内容再是 1 的内容，再将匹配的文本替换为重排后的内容并输出。
- 替换命令结尾处的 p 表示要打印该行。
命名分组
命名分组（named group）就是有名字的分组。这样，就可以通过名字（而不是数字）来引用分组。下面展示一下MySQL中如何使用命名分组：
代码语言：javascript复制mysql> select regexp_replace('It is an ancyent Marinere','(?<one>It is) (?<two>an ancyent Marinere)','${two} ${one}') c2;
---------------------------
| c2 |
---------------------------
| an ancyent Marinere It is |
---------------------------
1 row in set (0.00 sec)在括号内添加 ?<one> 和 ?<two> 将分组分别命名为 one 和 two ；
代码语言：javascript复制mysql> select regexp_like('000000','(?<z>0{3})\k<z>');
------------------------------------------
| regexp_like('000000','(?<z>0{3})\k<z>') |
------------------------------------------
| 1 |
------------------------------------------
1 row in set (0.00 sec)用 (?<z>0{3}) 对连续三个0的分组命名为 z ，然后可以用 k<z> 再次使用该分组，MySQL只支持 k<分组名> 这一种重用捕获分组的语法。在一个正则表达式中不能使用 ${分组名} 进行引用。
代码语言：javascript复制mysql> select regexp_like('000000','(?<z>0{3})${z}');
ERROR 3692 (HY000): Incorrect description of a {min,max} interval.四、非捕获分组
还有一种分组是非捕获分组（Non-Capturing Group）。非捕获分组不会将其内容存储在内存中，即非捕获分组根本不保存匹配项的值。在并不想引用分组的时候，可以使用它。由于不存储内容，非捕获分组就会带来较高的性能。非捕获分组的语法是在分组中加上 ?: 前缀。
代码语言：javascript复制mysql> select regexp_like('the','(?:the|The|THE)'),
-> regexp_like('the','(?i)(?:the)'),
-> regexp_like('the','(?:(?i)the)'),
-> regexp_like('the','(?i:the)')G
*************************** 1. row ***************************
regexp_like('the','(?:the|The|THE)'): 1
regexp_like('the','(?i)(?:the)'): 1
regexp_like('the','(?:(?i)the)'): 1
regexp_like('the','(?i:the)'): 1
1 row in set (0.00 sec)上面语句中四个 regexp_like 中的正则表达式等价，都是匹配不区分大小写的单词 the 。注意最后一种写法，选项 i 可以放在问号和冒号之间。
1. 原子分组
另一种非捕获分组是原子分组（atomic group），原子分组禁用回溯。如果使用的正则表达式引擎进行回溯操作，这种分组就可以将回溯操作关闭，但它只针对原子分组内的部分，而不针对整个正则表达式。其语法为 (?>the)：
代码语言：javascript复制mysql> select regexp_like('THE','(?>the)');
------------------------------
| regexp_like('THE','(?>the)') |
------------------------------
| 1 |
------------------------------
1 row in set (0.00 sec)2. 回溯
正则表达式匹配目标字符串时，它从左到右逐个测试表达式的组成部分，看是否能找到匹配项。在遇到量词时，需要决定何时尝试匹配更多字符。在遇到分支时，必须从可选项中选择一个尝试匹配。每当正则做类似的决定时，如果有必要，都会记录其他选择，以便匹配不成功时进行回溯，到最后一个决策点，再重新进行匹配。
（1）量词导致回溯
考虑正则表达式 ab?c 匹配字符串 ac。
- 首先从 a 开始，匹配到了，跳到 a 后面的位置（即一个零宽断言的位置）。
- 然后字符 b 后面有一个量词修饰符 ?，代表0或1次，匹配 b，没有出现 b 的情况先记录下来，放到备用状态里面。如果后面的匹配不成功，那么就回溯，到备用状态里面选择一个重新匹配。现在我们可以暂时认为这个量词要匹配，那么便是出现 1 次，此时的正则其实相当于 abc，b和c不一样，匹配失败。
- 注意，现在就要回溯了，到备用状态里面拿出来，此时正则表达式相当于ac，匹配ac，c和c一样，匹配成功。
mysql> select regexp_like('ac','ab?c');
--------------------------
| regexp_like('ac','ab?c') |
--------------------------
| 1 |
--------------------------
1 row in set (0.00 sec)使用原子分组时，如果完整的正则表达式与给定的字符串不匹配，则正则表达式引擎不会回退进一步的排列。还以本例来分析。
- 首先从 a 开始，匹配到了，跳到 a 后面的位置（即一个零宽断言的位置）。
- 然后字符 b 后面有一个量词修饰符 ?，代表0或1次，匹配 b，如果后面的匹配不成功，则匹配失败。现在我们可以认为这个量词要匹配，那么便是出现 1 次，此时的正则其实相当于 abc，b和c不一样，匹配失败。
mysql> select regexp_like('ac','ab?>c');
---------------------------
| regexp_like('ac','ab?>c') |
---------------------------
| 0 |
---------------------------
1 row in set (0.00 sec)（2）分支导致回溯
考虑正则表达式 a(bc|b)c 匹配字符串 abc。
- 首先从 a 开始，匹配到了，跳到 a 后面的位置（即一个零宽断言的位置）。
- 继续走遇到了分支，先用左边的（分支选择是从左到右），abbc 与 abc 匹配失败。
- 要回溯，从开始的那个 a 后面的位置，abc 与 abc 匹配成功。
如果使用原子分组：
- 首先从 a 开始，匹配到了，跳到 a 后面的位置（即一个零宽断言的位置）。
- 继续走遇到了分支，先用左边的（分支选择是从左到右），abbc 与 abc 匹配失败，则整个匹配失败，不再回溯其他分支。
mysql> select regexp_like('abc','a(bc|b)c'),
-> regexp_like('abc','a(?:bc|b)c'),
-> regexp_like('abc','a(?:b|bc)c'),
-> regexp_like('abc','a(?>bc|b)c'),
-> regexp_like('abc','a(?>b|bc)c')G
*************************** 1. row ***************************
regexp_like('abc','a(bc|b)c'): 1
regexp_like('abc','a(?:bc|b)c'): 1
regexp_like('abc','a(?:b|bc)c'): 1
regexp_like('abc','a(?>bc|b)c'): 0
regexp_like('abc','a(?>b|bc)c'): 1
1 row in set (0.00 sec)当使用分支（也叫替换）时，如果匹配成功，则正则表达式将立即尝试匹配表达式的其余部分，但会跟踪可能进行其他替换的位置。如果表达式的其余部分不匹配，则正则表达式将返回到先前记录的位置并尝试其他组合。如果使用了原子分组，则正则表达式引擎将不会跟踪先前的位置，而只会放弃匹配。
（3）回溯与性能
上面的示例清楚地表明原子分组消除了回溯，并可能改变匹配的结果，但并没有真正说明使用原子分组的目的。什么时候会想使用原子分组呢？正则表达式处理过程缓慢的一个因素就是回溯操作。其原因就是回溯操作会尝试每一种可能性，这会消耗时间和计算资源，有时它会占用大量时间。回溯有可能产生巨大的负面效应，这被称为灾难性回溯。来看下面的例子。
代码语言：javascript复制mysql> select regexp_like('11ab11111111111111111111111','(a(. ) b)');
ERROR 3699 (HY000): Timeout exceeded in regular expression match.查询直接报错退出。MySQL 8用两个系统变量控制匹配引擎的资源消耗。regexp_stack_limit 控制匹配使用的最大内存，缺省为 8000000 字节。regexp_time_limit 控制引擎执行的最大步骤数，缺省为 32。因为这个限制是用步骤数表示的，所以它只会间接影响执行时间。通常匹配引擎执行时间的数量级为毫秒。
代码语言：javascript复制mysql> show variables like 'regexp%';
-------------------- ---------
| Variable_name | Value |
-------------------- ---------
| regexp_stack_limit | 8000000 |
| regexp_time_limit | 32 |
-------------------- ---------
2 rows in set (0.00 sec)在贪婪量词模式下，正则表达式会尽可能长地去匹配符合规则的字符串，且会回溯。这种情况下原子分组会显著提高查询性能。
代码语言：javascript复制mysql> select regexp_like('11ab11111111111111111111111','(a(. ) b)');
----------------------------------------------------------
| regexp_like('11ab11111111111111111111111','(a(?>. ) b)') |
----------------------------------------------------------
| 0 |
----------------------------------------------------------
1 row in set (0.00 sec)
