一、匹配连续行
希望匹配连续多行文本，常见的情况是，一个逻辑行（logical line）可以分为许多现实的行，每一行以反斜杠结尾。
代码语言：javascript复制mysql> set @s:=
-> 'SRC=array.c buildin.c eval.c field.c gawkmisc.c io.c main.c\
'> missing.c msg.c node.c re.c version.c';
Query OK, 0 rows affected (0.00 sec)
mysql> select @sG
*************************** 1. row ***************************
@s: SRC=array.c buildin.c eval.c field.c gawkmisc.c io.c main.c
missing.c msg.c node.c re.c version.c
1 row in set (0.00 sec)1. 使用 dotall 模式
很简单，因为 dotall 模式的点号可以匹配换行符。
代码语言：javascript复制mysql> set @r:='^\w =.*';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, 'n') c, regexp_extract(@s, @r, 'n') sG
*************************** 1. row ***************************
@r: ^w =.*
c: 1
s: SRC=array.c buildin.c eval.c field.c gawkmisc.c io.c main.c
missing.c msg.c node.c re.c version.c
1 row in set (0.00 sec)2. 使用非 dotall 模式
换个思路：集中关注在特定时刻真正容许匹配的字符。在匹配一行文本时，期望匹配的要么是普通（除反斜杠和换行符之外）字符，要么是反斜杠与其它字符的结合体，要么是反斜杠加换行符。注意在 MySQL 中，每个反斜杠要用两个连续的反斜杠进行转义。
代码语言：javascript复制mysql> set @r:='^\w =([^\n\\]|\\.|\\\n)*';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: ^w =([^n\]|\.|\n)*
c: 1
s: SRC=array.c buildin.c eval.c field.c gawkmisc.c io.c main.c
missing.c msg.c node.c re.c version.c
1 row in set (0.01 sec)二、匹配IP地址
分析IP地址规则：
- 用点号分开的四个数。
- 每个数都在 0-255（含）之间。
- 第一段数字不能是 0。
1. 匹配0-255的数字
代码语言：javascript复制([01]?dd?|2[0-4]d|25[0-5])第一个分支可以匹配一位数 0-9、两位数 01-99、0 或 1 开头的三位数 000-199；第二个分支匹配 200-249；第三个分支匹配 250-255。
2. 第一段要求非零
代码语言：javascript复制(?!0 .)([01]?dd?|2[0-4]d|25[0-5])使用顺序否定环视，指定不能出现 0.、00.、000. 等等。
3. 四段合并
代码语言：javascript复制mysql> set @r:='^(?!0 \.)([01]?\d\d?|2[0-4]\d|25[0-5])\.((([01]?\d\d?|2[0-4]\d|25[0-5]))\.){2}(([01]?\d\d?|2[0-4]\d|25[0-5]))$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='0.1.1.1';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: ^(?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))$
c: 0
s:
1 row in set (0.01 sec)
mysql> set @s:='255.255.255.255';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: ^(?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))$
c: 1
s: 255.255.255.255
1 row in set (0.00 sec)
mysql> set @s:='001.255.255.255';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: ^(?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))$
c: 1
s: 001.255.255.255
1 row in set (0.00 sec)
mysql> set @s:='001.255.255.256';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: ^(?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))$
c: 0
s:
1 row in set (0.00 sec)4. 确定应用场合
上面的正则表达式必须借助锚点 ^ 和 $ 才能正常工作，否则可能匹配错误。
代码语言：javascript复制mysql> set @r:='(?!0 \.)([01]?\d\d?|2[0-4]\d|25[0-5])\.((([01]?\d\d?|2[0-4]\d|25[0-5]))\.){2}(([01]?\d\d?|2[0-4]\d|25[0-5]))';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='ip=72123.3.21.993';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: (?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))
c: 1
s: 123.3.21.99
1 row in set (0.01 sec)
mysql> set @s:='ip=123.3.21.223';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: (?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5]))
c: 1
s: 123.3.21.22
1 row in set (0.00 sec)为了避免匹配这样内嵌的文本，必须确保匹配文本两侧至少没有数字或者点号，可以使用否定环视实现。
代码语言：javascript复制mysql> set @r:='(?<![\d.])((?!0 \.)([01]?\d\d?|2[0-4]\d|25[0-5])\.((([01]?\d\d?|2[0-4]\d|25[0-5]))\.){2}(([01]?\d\d?|2[0-4]\d|25[0-5])))(?![\d.])';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='ip=72123.3.21.993';
Query OK, 0 rows affected (0.01 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: (?<![d.])((?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5])))(?![d.])
c: 0
s:
1 row in set (0.00 sec)
mysql> set @s:='ip=123.3.21.223';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') sG
*************************** 1. row ***************************
@r: (?<![d.])((?!0 .)([01]?dd?|2[0-4]d|25[0-5]).((([01]?dd?|2[0-4]d|25[0-5])).){2}(([01]?dd?|2[0-4]d|25[0-5])))(?![d.])
c: 1
s: 123.3.21.223
1 row in set (0.00 sec)三、处理文件名
1. 去掉文件名开头的路径
例如把/usr/local/bin/gcc变成gcc。
代码语言：javascript复制mysql> set @s:='/usr/local/bin/gcc';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='^.*/';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s,@r,'');
--------------------------
| regexp_replace(@s,@r,'') |
--------------------------
| gcc |
--------------------------
1 row in set (0.00 sec)利用匹配优先的特性，.* 可以匹配一整行，然后回退（也就是回溯）到最后的斜线，以完成匹配。别忘了时常想想匹配失败的情形。在本例中，匹配失败意味着字符串中没有斜线，所以不会替换，字符串也不会变化，而这正是所需要的。
为了保证效率，需要记住 NFA 引擎的工作原理。设想如果忘记在正则表达式的开头添加 ^ 符号，用来匹配一个恰好没有斜线的字符串，NFA 的执行过程如下。
正则引擎会在字符串的起始位置开始搜索。.* 抵达字符串的末尾，但必须不断回退，以找到斜线或者反斜线。直到最后它交还了匹配的所有字符，仍然无法匹配。此刻，正则引擎知道，在字符串的起始位置不存在匹配，但这远远没有结束。接下来传动装置开始工作，从目标字符串的第二个字符开始，依次尝试匹配整个正则表达式。事实上，它需要在字符串的每个位置（从理论上说）进行扫描-回溯。
如果字符串很长，就可能存在大量的回溯。DFA 不存在这个问题。MySQL 8 的正则引擎采用传统 NFA。实践中，经过合理优化的传动装置能够认识到，对几乎所有以 .* 开头的正则表达式来说，如果在某个字符串的起始位置不能匹配，也就不能在其他任何位置匹配，所以它只会在字符串的起始位置尝试一次。不过在正则表达式中写明这一点更加明智，本例中正是这样做的。
2. 从路径中获取文件名
代码语言：javascript复制mysql> set @s:='/usr/local/bin/perl';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='([^/]*)$';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_substr(@s, @r);
-----------------------
| regexp_substr(@s, @r) |
-----------------------
| perl |
-----------------------
1 row in set (0.00 sec)这次锚点不仅仅是一种优化措施，确实需要在结尾设置一个锚点以保证匹配的正确。这个正则表达式总能匹配，它唯一的要求是，字符串有 $ 能够匹配的结束位置。
在 NFA 中，([^/]*) 的效率很低。即便是短短的 '/usr/local/bin/perl'，在获得匹配结果之前也要进行四十多次回溯。考虑从 local 开始的尝试。([^/]*) 一直匹配到第二个 l，之后匹配失败，然后对 l、o、c、a、l 的存储状态依次尝试
本例使用 MySQL 提供的函数实现更好：
代码语言：javascript复制mysql> select substring_index('/usr/local/bin/perl','/',-1);
-----------------------------------------------
| substring_index('/usr/local/bin/perl','/',-1) |
-----------------------------------------------
| perl |
-----------------------------------------------
1 row in set (0.00 sec)3. 所在路径和文件名
代码语言：javascript复制mysql> set @r:='^(.*)/([^/]*)$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='/usr/local/bin/perl';
Query OK, 0 rows affected (0.00 sec)
mysql> select if (t,regexp_replace(@s, @r, '$1'),'.') path,
-> if (t,regexp_replace(@s, @r, '$2'),@s) filename
-> from (select instr(@s,'/') t) t;
---------------- ----------
| path | filename |
---------------- ----------
| /usr/local/bin | perl |
---------------- ----------
1 row in set (0.00 sec)要把完整的路径分为所在路径和文件名两部分。.* 会首先捕获所有文本，而不给 / 和 2 留下任何字符。.* 能交还字符的唯一原因，就是在尝试匹配 /([^/]*) 时进行的回溯。这会把“交还的”部分留给后面的 [^/]*。因此 1 就是文件所在的路径，2 就是文件名。
这个表达式有个问题，它要求字符串中必须至少出现一个斜线，如果用它来匹配 file.txt，因为无法匹配，路径和文件名都会返回原字符串。因此用子查询中的 instr 函数先判断有无斜杠。
四、匹配对称的括号
为了匹配括号部分，可以尝试下面这些正则表达式：
- (.*) 括号及括号内部的任何字符。
- ([^)]*) 从一个开括号到最近的闭括号。
- ([^()]*) 从一个开括号到最近的闭括号，但是不允许其中包含开括号。
下面显示了对一行简单字符串应用这些表达式的结果。
代码语言：javascript复制mysql> set @s:='var = foo(bar(this), 3.7) 2 * (that - 1);';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r1:='\(.*\)';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r2:='\([^)]*\)';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r3:='\([^()]*\)';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_substr(@s,@r1) s1,regexp_substr(@s,@r2) s2,regexp_substr(@s,@r3) s3;
----------------------------------- ------------ --------
| s1 | s2 | s3 |
----------------------------------- ------------ --------
| (bar(this), 3.7) 2 * (that - 1) | (bar(this) | (this) |
----------------------------------- ------------ --------
1 row in set (0.00 sec)需要匹配的部分是 (bar(this), 3.7)。可以看到，第一个正则表达式匹配的内容太多。.* 很容易出问题，所以使用 .* 时必须格外谨慎，明确是否真的需要用一个星号来约束点号。通常 .* 不是合适的选择。第二正则表达式匹配的内容太少，第三个正则表达式能够匹配 (this)，但无法匹配所需的内容。
这三个表达式都不合适。真正的问题在于，大多数系统中，正则表达式无法匹配任意深度的嵌套结构。可以用正则表达式来匹配特定深度的嵌套括号，如处理单层嵌套的正则表达式是：
代码语言：javascript复制([^()]*(([^()]*)[^()]*)*)测试：
代码语言：javascript复制mysql> set @s:='var = foo(bar(this), 3.7) 2 * (that - 1);';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='\([^()]*(\([^()]*\)[^()]*)*\)';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_substr(@s,@r);
----------------------
| regexp_substr(@s,@r) |
----------------------
| (bar(this), 3.7) |
----------------------
1 row in set (0.00 sec)这样类推下去，更深层次的嵌套就复杂得可怕。
五、防备不期望的匹配
用正则表达式匹配一个数，或者是整数或者是浮点数，这个数可能以负数符号开头。'-?[0-9]*.?[0-9]*' 可以匹配 1、-272.37、129238843.、191919，甚至是 -.0 这样的数。但是，这个表达式也能匹配 'this has no number'、'nothing here' 或是空字符串。
代码语言：javascript复制mysql> set @r:='-?[0-9]*\.?[0-9]*';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='1';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:='-272.37';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:='129238843.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='191919';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:='-.0';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s6:='this has no number';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s7:='nothing here';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s8:='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s1, @r, '') c, regexp_extract(@s1, @r, '') s;
------------------- ------ ------
| @r | c | s |
------------------- ------ ------
| -?[0-9]*.?[0-9]* | 2 | 1, |
------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s2, @r, '') c, regexp_extract(@s2, @r, '') s;
------------------- ------ ----------
| @r | c | s |
------------------- ------ ----------
| -?[0-9]*.?[0-9]* | 2 | -272.37, |
------------------- ------ ----------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s3, @r, '') c, regexp_extract(@s3, @r, '') s;
------------------- ------ -------------
| @r | c | s |
------------------- ------ -------------
| -?[0-9]*.?[0-9]* | 2 | 129238843., |
------------------- ------ -------------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s4, @r, '') c, regexp_extract(@s4, @r, '') s;
------------------- ------ ---------
| @r | c | s |
------------------- ------ ---------
| -?[0-9]*.?[0-9]* | 2 | 191919, |
------------------- ------ ---------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s5, @r, '') c, regexp_extract(@s5, @r, '') s;
------------------- ------ ------
| @r | c | s |
------------------- ------ ------
| -?[0-9]*.?[0-9]* | 2 | -.0, |
------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s6, @r, '') c, regexp_extract(@s6, @r, '') s;
------------------- ------ --------------------
| @r | c | s |
------------------- ------ --------------------
| -?[0-9]*.?[0-9]* | 19 | ,,,,,,,,,,,,,,,,,, |
------------------- ------ --------------------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s7, @r, '') c, regexp_extract(@s7, @r, '') s;
------------------- ------ --------------
| @r | c | s |
------------------- ------ --------------
| -?[0-9]*.?[0-9]* | 13 | ,,,,,,,,,,,, |
------------------- ------ --------------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s8, @r, '') c, regexp_extract(@s8, @r, '') s;
------------------- ------ ------
| @r | c | s |
------------------- ------ ------
| -?[0-9]*.?[0-9]* | 1 | |
------------------- ------ ------
1 row in set (0.00 sec)仔细看看这个表达式——每个部分都不是匹配必须的，如果存在一个数在字符串的起始位置，正则表达式的确能够匹配。但是因为匹配没有任何必须元素，此正则表达式可以匹配每个例子中字符串开头的空字符。实际上它甚至可以匹配 'num 123'开头的空字符，因为这个空字符比数字出现得更早。
代码语言：javascript复制mysql> set @s9:='num 123';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s9, @r, '') c, regexp_extract(@s9, @r, '') s;
------------------- ------ ----------
| @r | c | s |
------------------- ------ ----------
| -?[0-9]*.?[0-9]* | 6 | ,,,,123, |
------------------- ------ ----------
1 row in set (0.00 sec)一个浮点数必须要有至少一位数字，否则就不是一个合法的值。首先假设在小数点之前至少有一位数字（之后会去掉这个条件），需要用加号来控制这些数字 '-?[0-9] '。
如果要用正则表达式来匹配可能存在的小数点和其后的数字，就必须认识到，小数部分必须紧接在小数点之后。如果简单地用 '.?[0-9]*'，那么无论小数点是否存在，'[0-9]*' 都能够匹配。
解决的办法是用问号限定小数点和后面的小数部分，而不再只是小数点：'(.[0-9]*)?'。在这个结构体内部，小数点是必须出现的，如果没有小数点，'[0-9]*' 根本谈不上匹配。
把它们结合起来，就得到 '-?[0-9] (.[0-9]*)?'。
代码语言：javascript复制mysql> set @r:='-?[0-9] (\.[0-9]*)?';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='1';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:='-272.37';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:='129238843.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='191919';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:='-.0';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s6:='this has no number';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s7:='nothing here';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s8:='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s1, @r, '') c, regexp_extract(@s1, @r, '') s;
--------------------- ------ ------
| @r | c | s |
--------------------- ------ ------
| -?[0-9] (.[0-9]*)? | 1 | 1 |
--------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s2, @r, '') c, regexp_extract(@s2, @r, '') s;
--------------------- ------ ---------
| @r | c | s |
--------------------- ------ ---------
| -?[0-9] (.[0-9]*)? | 1 | -272.37 |
--------------------- ------ ---------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s3, @r, '') c, regexp_extract(@s3, @r, '') s;
--------------------- ------ ------------
| @r | c | s |
--------------------- ------ ------------
| -?[0-9] (.[0-9]*)? | 1 | 129238843. |
--------------------- ------ ------------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s4, @r, '') c, regexp_extract(@s4, @r, '') s;
--------------------- ------ --------
| @r | c | s |
--------------------- ------ --------
| -?[0-9] (.[0-9]*)? | 1 | 191919 |
--------------------- ------ --------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s5, @r, '') c, regexp_extract(@s5, @r, '') s;
--------------------- ------ ------
| @r | c | s |
--------------------- ------ ------
| -?[0-9] (.[0-9]*)? | 1 | 0 |
--------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s6, @r, '') c, regexp_extract(@s6, @r, '') s;
--------------------- ------ ------
| @r | c | s |
--------------------- ------ ------
| -?[0-9] (.[0-9]*)? | 0 | |
--------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s7, @r, '') c, regexp_extract(@s7, @r, '') s;
--------------------- ------ ------
| @r | c | s |
--------------------- ------ ------
| -?[0-9] (.[0-9]*)? | 0 | |
--------------------- ------ ------
1 row in set (0.00 sec)
mysql> select @r, regexp_count(@s8, @r, '') c, regexp_extract(@s8, @r, '') s;
--------------------- ------ ------
| @r | c | s |
--------------------- ------ ------
| -?[0-9] (.[0-9]*)? | 0 | |
--------------------- ------ ------
1 row in set (0.00 sec)这个表达式不能匹配 '.007'，因为它要求整数部分必须有一位数字。如果允许整数部分为空，就必须同时修改小数部分，否则这个表达式就可以匹配空字符（就是一开始准备解决的问题）。
解决的办法是为无法覆盖的情况添加多选分支：'-?([0-9] (.[0-9]*)?|.[0-9] )'。
代码语言：javascript复制mysql> set @r:='-?([0-9] (\.[0-9]*)?|\.[0-9] )';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:='-.0';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s5, @r, '') c, regexp_extract(@s5, @r, '') s;
-------------------------------- ------ ------
| @r | c | s |
-------------------------------- ------ ------
| -?([0-9] (.[0-9]*)?|.[0-9] ) | 1 | -.0 |
-------------------------------- ------ ------
1 row in set (0.00 sec)虽然这个表达式比最开始的好得多，但它仍然会匹配 '2003.04.12' 这样的数字。要想真正匹配期望的文本，同时忽略不期望的文本，求得平衡，就必须了解实际的待匹配文本。用来提取浮点数的正则表达式必须包含在一个大的正则表达式内部，例如用 '^...' 或者 'nums*=s*...'。
代码语言：javascript复制mysql> set @s10:='2003.04.12';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s10, @r, '') c, regexp_extract(@s10, @r, '') s;
---------------------------------- ------ ------
| @r | c | s |
---------------------------------- ------ ------
| ^-?([0-9] (.[0-9]*)?|.[0-9] )$ | 0 | |
---------------------------------- ------ ------
1 row in set (0.00 sec)
mysql> set @r:='^-?([0-9] (\.[0-9]*)?|\.[0-9] )$';
Query OK, 0 rows affected (0.00 sec)
mysql> select @r, regexp_count(@s10, @r, '') c, regexp_extract(@s10, @r, '') s;
---------------------------------- ------ ------
| @r | c | s |
---------------------------------- ------ ------
| ^-?([0-9] (.[0-9]*)?|.[0-9] )$ | 0 | |
---------------------------------- ------ ------
1 row in set (0.00 sec)六、匹配分隔符之内的文本
匹配用分隔符（以某些字符表示）之类的文本是常见的任务，除了匹配双引号内的文本和IP地址两个典型例子，还包括：
匹配 '/*' 和 '*/' 之间的 C 语言注释。
匹配一个 HTML tag，也就是尖括号之内的文本，例如 <CODE>。
提取 HTML tag 标注的文本，例如在 HTML 代码 'a<I>super exciting</I>offer!' 中的‘super exciting’。
匹配 .mailrc 文件中的一行内容，这个文件的每一行都按下面的数据格式组织：
代码语言：javascript复制alias 简称 电子邮件地址例如 'alias jeff jfriedl@regex.info'（在这里，分隔符是每个部分之间的空白和换行符）。
匹配引文字符串（quoted string），但是允许其中包含转义的引号。例如 'a passport needs a "2"x3" likeness" of the holder'。
解析 CSV（逗号分隔值，comma-separated values）文件。
总的来说，处理这些任务的步骤是：
- 匹配起始分隔符（opening delimiter）。
- 匹配正文（main text，即结束分隔符之前的所有文本）。
- 匹配结束分隔符。
来看 2"x3" 的例子，这里的结束分隔符是一个引号，匹配开始和结束分隔符很容易，一下就能写出的正则表达式为：'".*"'。本例中它恰巧可以利用量词缺省的贪婪特性，直接匹配出正文中的双引号。
代码语言：javascript复制mysql> set @s:='a passport needs a "2\"x3\" likeness" of the holder';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='".*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
----------------------------------------------------- ------ --------------------
| @s | c | s |
----------------------------------------------------- ------ --------------------
| a passport needs a "2"x3" likeness" of the holder | 1 | "2"x3" likeness" |
----------------------------------------------------- ------ --------------------
1 row in set (0.00 sec)下面考虑一种更为通用的方法。仔细想想正文里能够出现的字符，如果一个字符不是引号，也就是说如果这个字符能由 '[^"]' 匹配，那么它肯定属于正文。如果这个字符是一个引号，而它前面又有一个反斜线，那么这个引号也属于正文。把这个意思表达出来，使用环视功能来处理“如果之前有反斜线”的情况，就得到 '"([^"]|(?<=\)")*"'，这个表达式完全能够匹配 2"x3"。
代码语言：javascript复制mysql> set @s:='a passport needs a "2\"x3\" likeness" of the holder';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"([^"]|(?<=\\)")*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
----------------------------------------------------- -------------------- ------ --------------------
| @s | @r | c | s |
----------------------------------------------------- -------------------- ------ --------------------
| a passport needs a "2"x3" likeness" of the holder | "([^"]|(?<=\)")*" | 1 | "2"x3" likeness" |
----------------------------------------------------- -------------------- ------ --------------------
1 row in set (0.00 sec)不过，这个例子也能用来说明，看起来正确的表达式如何会匹配意料之外的文本。例如文本：Darth Symbol: "/-|-\" or "[^-^]"
希望它匹配的是 "/-|-\"，但它匹配的是 "/-|-\" or "。
代码语言：javascript复制mysql> set @s:='"/-|-\\" or "[^-^]"';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"([^"]|(?<=\\)")*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
--------------------- -------------------- ------ ---------------
| @s | @r | c | s |
--------------------- -------------------- ------ ---------------
| "/-|-\" or "[^-^]" | "([^"]|(?<=\)")*" | 1 | "/-|-\" or " |
--------------------- -------------------- ------ ---------------
1 row in set (0.00 sec)这是因为，第一个比引号之前的确存在一个反斜线，但这个反斜线本身是转义的，它不是用来转义之后的双引号的，也就是说这个引号其实是表示引用文本的结束。而逆序环视无法识别这个被转义的反斜线，如果在这个引号之前有任意多个 ‘\’，用逆序环视只会更糟。本例中可以利用量词的懒惰特性，直接匹配出想要的结果。
代码语言：javascript复制mysql> set @s:='"/-|-\\" or "[^-^]"';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='".*?"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
--------------------- ------- ------ ------------------
| @s | @r | c | s |
--------------------- ------- ------ ------------------
| "/-|-\" or "[^-^]" | ".*?" | 2 | "/-|-\","[^-^]" |
--------------------- ------- ------ ------------------
1 row in set (0.00 sec)更为细致的写法是，将可能出现在正文部分的文本都列出，其中可以包括转义的字符（'\.'），也可以包括非引号的任何字符 '[^"]'，于是得到 '"(\.|[^"])*"'。
代码语言：javascript复制mysql> set @s:='"/-|-\\" or "[^-^]"';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"(\\.|[^"])*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
--------------------- --------------- ------ ------------------
| @s | @r | c | s |
--------------------- --------------- ------ ------------------
| "/-|-\" or "[^-^]" | "(\.|[^"])*" | 2 | "/-|-\","[^-^]" |
--------------------- --------------- ------ ------------------
1 row in set (0.00 sec)现在这个问题解决了，但这个表达式还有问题，不期望的匹配仍然会发生。比如对下面这个文本："You need a 2"x3" Photo.
它应该无法匹配，因为其中没有结束分隔符，但结果却匹配到了。
代码语言：javascript复制mysql> set @s:='"You need a 2\"x3\" Photo.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"(\\.|[^"])*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
---------------------------- --------------- ------ ---------------------
| @s | @r | c | s |
---------------------------- --------------- ------ ---------------------
| "You need a 2"x3" Photo. | "(\.|[^"])*" | 1 | "You need a 2"x3" |
---------------------------- --------------- ------ ---------------------
1 row in set (0.00 sec)这个表达式一开始匹配到了引号之后的文本，但没找到结束的引号，于是它就会回溯，达到 3 后面的反斜线时，'[^"]' 匹配到了反斜线，之后的那个引号被认为是一个结束的引号。
这个例子的重要启示是：如果回溯会导致不期望，与多选结构有关的匹配结果，问题很可能在于，任何成功的匹配都不过是多选分支的排列顺序造成的偶然结果。
实际上，如果把这个正则表达式的多选分支反过来排列，它就会错误地匹配任何包含转义双引号的字符串。
代码语言：javascript复制mysql> set @s:='"You need a 2\"x3\" Photo.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"([^"]|\\.)*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
---------------------------- --------------- ------ -----------------
| @s | @r | c | s |
---------------------------- --------------- ------ -----------------
| "You need a 2"x3" Photo. | "([^"]|\.)*" | 1 | "You need a 2" |
---------------------------- --------------- ------ -----------------
1 row in set (0.00 sec)真正的问题在于，各个多选分支能够匹配的内容发生了重叠。解决方法是，保证各个多选分支能够匹配的内容互斥。本例中必须确保反斜线不能以其他的方式匹配，也就是说把 '[^"]' 改为 '[^\"]'。这样就能识别双引号和文本中的“特殊”反斜线，必须根据情况分别处理。结果就是 '"(\.|[^\"])*"'。
代码语言：javascript复制mysql> set @s:='"You need a 2\"x3\" Photo.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"(\\.|[^\\"])*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
---------------------------- ----------------- ------ ------
| @s | @r | c | s |
---------------------------- ----------------- ------ ------
| "You need a 2"x3" Photo. | "(\.|[^\"])*" | 0 | |
---------------------------- ----------------- ------ ------
1 row in set (0.00 sec)如果有占有量词优先或者是固化分组，这个表达式可以重写做 '"(\.|[^"])* "' 或 '"(?>(\.|[^"])*)"'。这两个表达式禁止引擎回溯到可能出问题的地方，所以它们都可以满足需求。
代码语言：javascript复制mysql> set @s:='"You need a 2\"x3\" Photo.';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='"(\\.|[^"])* "';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
---------------------------- ---------------- ------ ------
| @s | @r | c | s |
---------------------------- ---------------- ------ ------
| "You need a 2"x3" Photo. | "(\.|[^"])* " | 0 | |
---------------------------- ---------------- ------ ------
1 row in set (0.00 sec)
mysql> set @r:='"(?>(\\.|[^"])*)"';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
---------------------------- ------------------- ------ ------
| @s | @r | c | s |
---------------------------- ------------------- ------ ------
| "You need a 2"x3" Photo. | "(?>(\.|[^"])*)" | 0 | |
---------------------------- ------------------- ------ ------
1 row in set (0.00 sec)占有优先量词和固化分组解决此问题效率更高，因为这样报告匹配失败的速度更快。
七、除去文本首尾的空白字符
去除文本首尾的空白字符是经常要完成的任务。总的来说最好的办法使用两个替换。
代码语言：javascript复制mysql> set @s1:='';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:=' aaa bbb ccc';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:=' aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(regexp_replace(@s1,'^\s ',''),'\s $','') s1,
-> regexp_replace(regexp_replace(@s2,'^\s ',''),'\s $','') s2,
-> regexp_replace(regexp_replace(@s3,'^\s ',''),'\s $','') s3,
-> regexp_replace(regexp_replace(@s4,'^\s ',''),'\s $','') s4,
-> regexp_replace(regexp_replace(@s5,'^\s ',''),'\s $','') s5;
------ ------ --------------- --------------- ---------------
| s1 | s2 | s3 | s4 | s5 |
------ ------ --------------- --------------- ---------------
| | | aaa bbb ccc | aaa bbb ccc | aaa bbb ccc |
------ ------ --------------- --------------- ---------------
1 row in set (0.00 sec)为了增加效率，这里使用 ' ' 而不是 '*'，因为如果事实上没有要删除的空白字符，就不用做替换。
出于某些原因，人们似乎更希望用一个正则表达式来解决整个问题。这里提供方法供比较，旨在理解这些正则表达式的工作原理及其问题所在，并不推荐这些办法。在 MySQL 8.0.16 中，对空串用该正则表达式执行替换时报错：
代码语言：javascript复制mysql> set @r:='^\s*(.*?)\s*$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s1,@r,'$1') s1;
ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> set @r:='^\s*(.*?)\s*$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:=' aaa bbb ccc';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:=' aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s2,@r,'$1') s2,
-> regexp_replace(@s3,@r,'$1') s3,
-> regexp_replace(@s4,@r,'$1') s4,
-> regexp_replace(@s5,@r,'$1') s5;
------ --------------- --------------- ---------------
| s2 | s3 | s4 | s5 |
------ --------------- --------------- ---------------
| | aaa bbb ccc | aaa bbb ccc | aaa bbb ccc |
------ --------------- --------------- ---------------
1 row in set (0.00 sec)这个表达式比普通的办法慢得多（在 Perl 中要慢 5 倍）。之所以效率这么低，是因为忽略优先（懒惰匹配）约束的点号每次应用时都要检查 's*$'，这需要大量的回溯。在 MySQL 8.0.16 中，对空串用该正则表达式执行替换时报错：
代码语言：javascript复制mysql> set @r:='^\s*((?:.*\S)?)\s*$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s1,@r,'$1') s1;
ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> set @r:='^\s*((?:.*\S)?)\s*$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:=' aaa bbb ccc';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:=' aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s2,@r,'$1') s2,
-> regexp_replace(@s3,@r,'$1') s3,
-> regexp_replace(@s4,@r,'$1') s4,
-> regexp_replace(@s5,@r,'$1') s5;
------ --------------- --------------- ---------------
| s2 | s3 | s4 | s5 |
------ --------------- --------------- ---------------
| | aaa bbb ccc | aaa bbb ccc | aaa bbb ccc |
------ --------------- --------------- ---------------
1 row in set (0.01 sec)这个表达式看起来比上一个复杂，不过它所花的时间只是普通方法的 2 倍。在 '^s*' 匹配了文本开头的空格之后，'.*' 马上匹配到文本的末尾。后面的 'S' 强迫它回溯直到找到一个非空字符，把剩下的空白字符留给最后的 's*$'，捕获括号之外。非捕获组外的问号在这里是必须的，因为如果一行数据只包含空白字符的行，必须出现问号，表达式才能正常工作。如果没有问号，可能会无法匹配，错过这种只有空白符的行。
代码语言：javascript复制mysql> set @r:='^\s |\s $';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:=' aaa bbb ccc';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s5:=' aaa bbb ccc ';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s1,@r,'') s1,
-> regexp_replace(@s2,@r,'') s2,
-> regexp_replace(@s3,@r,'') s3,
-> regexp_replace(@s4,@r,'') s4,
-> regexp_replace(@s5,@r,'') s5;
------ ------ --------------- --------------- ---------------
| s1 | s2 | s3 | s4 | s5 |
------ ------ --------------- --------------- ---------------
| | | aaa bbb ccc | aaa bbb ccc | aaa bbb ccc |
------ ------ --------------- --------------- ---------------
1 row in set (0.00 sec)这是最容易想到的正则表达式，但这种顶级的（top-leveled）多选分支排列严重影响本来可能使用的优化措施。这个表达式所用的时间是简单办法的 4 倍。
简单的首尾两次替换几乎总是最快的，而且显然最容易理解。
八、HTML相关范例
1. 匹配 HTML Tag
最常见的办法就是用 '<[^>] >' 来匹配 HTML 标签。它通常都能工作，例如去除标签：
代码语言：javascript复制mysql> set @s:='<tag> aaa </tag>';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='<[^>] >';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s,@r,'');
--------------------------
| regexp_replace(@s,@r,'') |
--------------------------
| aaa |
--------------------------
1 row in set (0.00 sec)如果 tag 中含有‘>’，它就不能正常匹配了，但 HTML 语言确实容许在引号内的 tag 属性中出现非转义的 ‘<’和‘>’：<input name=dir value=">">。这样，简单的 '<[^>] >' 就无法匹配了。
代码语言：javascript复制mysql> set @s:='<input name=dir value=">">';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='<[^>] >';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s,@r,'');
--------------------------
| regexp_replace(@s,@r,'') |
--------------------------
| "> |
--------------------------
1 row in set (0.00 sec)‘<...>’ 中能够出现引用文本和非引用形式的 “其他文本（other stuff）”，其中包括除了 ‘>’ 和引号之外的任意字符。HTML 的引文可以用单引号，也可以用双引号，但不容许转义嵌套的引号，所以可以直接用 '"[^"]"*' 和 ''[^']*'' 来匹配。把这些和 “其他文本” 表达式 '[^'">]' 合起来得到：'<("[^"]"*|'[^']*'|[^'">])*>'。
代码语言：javascript复制mysql> set @s:='<input name=dir value=">">';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='<("[^"]"*|'[^']*'|[^'">])*>';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_replace(@s,@r,'');
---------------------------- ----------------------------- --------------------------
| @s | @r | regexp_replace(@s,@r,'') |
---------------------------- ----------------------------- --------------------------
| <input name=dir value=">"> | <("[^"]"*|'[^']*'|[^'">])*> | |
---------------------------- ----------------------------- --------------------------
1 row in set (0.00 sec)这个表达式把每个引用部分单作为一个单元，而且清楚地说明了在匹配的什么位置容许出现什么字符。这个表达式的各个部分不会匹配重复的字符，因此不存在模糊性，也就不需要担心前面例子中，“不小心冒出来（sneaking in）” 非期望匹配。
最开始的两个多选分支的引号中使用了 * 而不是 。引用字符串可能为空（例如‘alt=""’），所以要用 * 来处理这种情况。而第三个分支 '[^'">]' 只接受括号外的 * 的限定，给它添加一个加号得到 '([^'">] )*'，可能导致非常奇怪的结果。
在使用 NFA（如MySQL）引擎时还需要考虑效率问题：既然没有用到括号匹配的文本，就可以把它们改为非捕获型括号'(?:...)'。因为多选分支不存在重叠，如果最后的 '>' 无法匹配，那么回头尝试其他的多选分支也是徒劳的。如果一个多选分支能够在某个位置匹配，那么其他多选分支肯定无法在这里匹配。所以，不保存状态也无所谓，这样做还可以更快地导致失败，如果找不到匹配结果的话。可以用固化分组 '(?>...)' 而不是非捕获型括号，或者用占有优先的星号限定 '* '，来避免回溯。
2. 匹配 HTML Link
假设需要从一份文档中提取 URL 和链接文本，例如从下面的文本中取出 http://www.oreilly.com 和 O'Reilly Media：
代码语言：javascript复制...<a href="http://www.oreilly.com">O'Reilly Media</a>...<A> 标签的内容可能相当复杂，因此可以分两步实现。第一步是提取 <A> 标签内部的内容，也就是链接文本，然后从 <A> 标签中提取 URL 地址。
实现第一步的正则表达式为：
代码语言：javascript复制'<ab([^>] )>(.*?)</a>'它会把 <A> 的内容放入
代码语言：javascript复制mysql> set @s:='<a href="http://www.oreilly.com">O'Reilly Media</a>';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='<a\b([^>] )>(.*?)</a>';
Query OK, 0 rows affected (0.01 sec)
mysql> select @s, @r, regexp_replace(@s, @r, '$2', 1, 0, 'n');
----------------------------------------------------- ----------------------- -----------------------------------------
| @s | @r | regexp_replace(@s, @r, '$2', 1, 0, 'n') |
----------------------------------------------------- ----------------------- -----------------------------------------
| <a href="http://www.oreilly.com">O'Reilly Media</a> | <ab([^>] )>(.*?)</a> | O'Reilly Media |
----------------------------------------------------- ----------------------- -----------------------------------------
1 row in set (0.00 sec)这里的匹配类型使用了dotall。MySQL 的正则表达式没有提供获取单个捕获组的方法，只能用 regexp_replace 函数以替换的方式间接获取，并且要想确保只返回捕获组，最好每次调用 regexp_replace 时只返回一个捕获组。显然用这种方法获取所有捕获组性能低下，因为明明应用一次正则表达式，引擎就已经获取了所有捕获组的值，只是 MySQL 没给用户提供相应的函数。
如果愿意，可以使用分隔符一次性得到所有捕获组，如 regexp_replace(@s, @r, '1|2', 1, 0, 'n')，用 | 符号作为分隔符连接起多个捕获组。但为了后续处理需确保原字符串中没有 | 字符。
<A> 的内容存入 $1 后，就可以用独立的正则表达式来检查它。URL 是 href 属性的值。HTML 容许等号的任意一侧出现空白字符，值可以以引用形式出现，也可以以非引用形式出现。因此匹配 URL 的正则表达式如下：
代码语言：javascript复制bhrefs*=s*(?:"([^"]*)"|'([^']*)'|([^'">s] ))说明：
- bhref 匹配“href”属性。
- s*=s* 匹配 “=” 两端可能出现空白字符。
- "([^"]*)" 匹配双引号字符串。
- '([^']*)' 匹配单引号字符串。
- ([^'">s] ) 其他文本，匹配除单双引号、> 和空白符以外的任意字符。
匹配值的每个多选结构都加了括号，来捕获确切的文本。最外层的分组不需要捕获，因此使用非捕获型括号 ?:，这样做既清楚又高效。因为需要捕获整个 href 的值，这里使用了 来限制其他文本多选分支。这个加号不会导致奇怪的结果，因为这外面没有直接作用于整个多选结构的量词。
代码语言：javascript复制mysql> set @s:='<a href="http://www.oreilly.com">O'Reilly Media</a>';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r1:='<a\b([^>] )>(.*?)</a>';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r2:='\bhref\s*=\s*(?:"([^"]*)"|'([^']*)'|([^'">\s] ))';
Query OK, 0 rows affected (0.00 sec)
mysql> select if(regexp_like(url,@r2),regexp_replace(url, @r2, '$1$2$3', 1, 0, 'n'),'') url, link
-> from (select trim(regexp_replace(@s, @r1, '$1', 1,0,'n')) url, regexp_replace(@s, @r1, '$2', 1,0,'n') link) t;
------------------------ ----------------
| url | link |
------------------------ ----------------
| http://www.oreilly.com | O'Reilly Media |
------------------------ ----------------
1 row in set (0.00 sec)内层子查询执行第一步处理，其中的 trim 函数去掉 @r1 表达式中 b 位置匹配到的空白字符。外层查询执行第二步提取 URL 的处理。根据具体文本的不同，最后 URL 可能保存在 1、2或者
3. 检查 HTTP URL
看看得到的 URL 地址是否是 HTTP URL，如果是，就把它分解为主机名（hostname）和路径（path）两部分。主机名是 '^http://' 之后和第一个反斜线（如果有的话）之前的内容，而路径就是除此之外的内容：'^http://([^/] )(/.*)?$'。
URL 中可能包含端口号，它位于主机名和路径之间，以一个冒号开头：'^http://([^/:] )(:(d ))?(/.*)?$'。
代码语言：javascript复制mysql> set @s:='http://www.oreilly.com:8080/book/details/130986791?spm=1001.2014.3001.5501';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='^http://([^/:] )(:(\d ))?(/.*)?$';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_replace(@s, @r, '$1') host, regexp_replace(@s, @r, '$3') port, regexp_replace(@s, @r, '$4') path;
----------------- ------ -------------------------------------------------
| host | port | path |
----------------- ------ -------------------------------------------------
| www.oreilly.com | 8080 | /book/details/130986791?spm=1001.2014.3001.5501 |
----------------- ------ -------------------------------------------------
1 row in set (0.00 sec)4. 验证主机名
从已知文本（例如现成的 URL）中提取主机名：
代码语言：javascript复制mysql> set @r:='https?://([^/:] )(:(\d ))?(/.*)?';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='http://www.google.com/';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_replace(@s, @r, '$1') hostname;
------------------------ ---------------------------------- ----------------
| @s | @r | hostname |
------------------------ ---------------------------------- ----------------
| http://www.google.com/ | https?://([^/:] )(:(d ))?(/.*)? | www.google.com |
------------------------ ---------------------------------- ----------------
1 row in set (0.00 sec)从随机文本中准确提取主机名：
代码语言：javascript复制mysql> set @r:='https?://([-a-z0-9] (\.[-a-z0-9] )*\.(com|edu|info))(:(\d ))?(/.*)?';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='http://www.google.com/';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s, @r, regexp_replace(@s, @r, '$1') hostname;
------------------------ --------------------------------------------------------------------- ----------------
| @s | @r | hostname |
------------------------ --------------------------------------------------------------------- ----------------
| http://www.google.com/ | https?://([-a-z0-9] (.[-a-z0-9] )*.(com|edu|info))(:(d ))?(/.*)? | www.google.com |
------------------------ --------------------------------------------------------------------- ----------------
1 row in set (0.00 sec)可以用正则表达式来验证主机名。按规定，主机名由点号分隔的部分组成，每个部分不能超过 63 个字符，可以包括 ASCII 字符、数字和连字符，但是不能以连字符作为开头和结尾。所以可以在不区分大小写的模式下使用这个正则表达式：'[a-z0-9]|[a-z0-9][-a-z0-9]{0,61}[a-z0-9]'。结尾的后缀部分（com、edu、uk 等）只有有限多个可能。结合起来，下面的正则表达式就能够匹配一个语意正确的主机名： '^(?i)(?:[a-z0-9].|[a-z0-9][-a-z0-9]{0,61}[a-z0-9].)*(?:com|edu|gov|int|mil|net|org|biz|info|name|museum|coop|aero|[a-z][a-z])$'。
代码语言：javascript复制mysql> set @r:='^(?i)(?:[a-z0-9]\.|[a-z0-9][-a-z0-9]{0,61}[a-z0-9]\.)*(?:com|edu|gov|int|mil|net|org|biz|info|name|museum|coop|aero|[a-z][a-z])$';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s1:='ai';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s2:='www.google';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s3:='google.com';
Query OK, 0 rows affected (0.00 sec)
mysql> set @s4:='www.google.com';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s1 hostname, regexp_like(@s1, @r) isvalid
-> union all
-> select @s2, regexp_like(@s2, @r)
-> union all
-> select @s3, regexp_like(@s3, @r)
-> union all
-> select @s4, regexp_like(@s4, @r);
---------------- ---------
| hostname | isvalid |
---------------- ---------
| ai | 1 |
| www.google | 0 |
| google.com | 1 |
| www.google.com | 1 |
---------------- ---------
4 rows in set (0.00 sec)5. 在真实世界中提取 URL
从纯文本中识别（recognize）出主机名和 URL 比验证（validate）它们困难得多。下面的正则表达式从文本中提取出 mailto、ftp、http、https 等几种类型的 URL。如果在文本中找到‘http://’，就知道这肯定是一个 URL 的开头，所以可以直接用 'http://[-w] (.w[-w]*) ' 来取代 '-a-z0-9'。'w' 同样可以匹配下划线。
不过，URL 通常不是以 http:// 或者 mailto: 开头的，这种情况匹配主机名的正则表达式为： '(?i:[a-z0-9](?:[-a-z0-9]*[a-z0-9])?.) (?-i:comb|edub|bizb|govb|in(?:t|fo)b|milb|netb|orgb|[a-z][a-z]b)'
接在主机名后面的是 path（路径）部分，它使用逆序环视来确保 URL 不会以句末的点号结尾。
代码语言：javascript复制mysql> set @r_protocol:='(ftp|https?)://';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r_hostname:='[-\w] (\.\w[-\w]*) |(?i:[a-z0-9](?:[-a-z0-9]*[a-z0-9])?\.) (?-i:com\b|edu\b|biz\b|gov\b|in(?:t|fo)\b|mil\b|net\b|org\b|[a-z][a-z]\b)';
Query OK, 0 rows affected (0.01 sec)
mysql> set @r_port:='(:\d )?';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r_path:='(/[-a-z0-9_:\@&?= ,.!/~*'%\$]*(?<![.,?!]))?';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:=concat('\b(',@r_protocol,@r_hostname,')',@r_port,@r_path);
Query OK, 0 rows affected (0.00 sec)
mysql> set @s:='https://www.tetet.com:8080/index.html?q=1';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_count(@s, @r, 'n') c, regexp_extract(@s, @r, 'n') s;
------ -------------------------------------------
| c | s |
------ -------------------------------------------
| 1 | https://www.tetet.com:8080/index.html?q=1 |
------ -------------------------------------------
1 row in set (0.00 sec)九、保持数据的协调性
假设需要处理的数据是一系列连续的 5 位数美国邮政编码（ZIP Codes），而需要提取的是以 44 开头的那些编码。下面是一点抽样，需要提取的数值是 44182 和 44272： 03824531449411615213441829505344272752010217443235
最容易想到的是 'd{5}'，它能匹配所有的邮编。在 MySQL 中，只需要循环调用 regexp_substr 函数。这里关注的正则表达式本身，而不是语言的实现机制。
假设所有数据都是规范的（此假设与具体情况密切相关），'d{5}' 在整个解析过程中任何时候都能匹配，绝对没有传动装置的驱动和重试。
代码语言：javascript复制set @s:='03824531449411615213441829505344272752010217443235';
set @r:='\d{5}';
with t1 as
(select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
t2 as
(with recursive tab1(lv) as
(select 1 lv union all select t1.lv 1 from tab1 t1 where lv < length(@s)/5)
select lv from tab1),
t3 as
(select substring_index(substring_index(s,',',lv),',',-1) s from t1,t2)
select * from t3 where s like '44%';把 'd{5}' 改为 '44\d{3}' 来查找以 44 开头的邮编是不行的。在匹配失败后，传动装置会驱动前进一个字符，对 '44' 匹配不再是从每个邮编的第一位开始，因此 '44\d{3}' 会错误地匹配 44941：
代码语言：javascript复制mysql> set @r:='44\d{3}';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
------ -------------------------
| c | s |
------ -------------------------
| 4 | 44941,44182,44272,44323 |
------ -------------------------
1 row in set (0.00 sec)这里需要手动保持正则引擎的协调，才能忽略不需要的邮编。关键是要跳过完整的邮编，而不是使用传动装置的驱动过程（bump-along）来进行单个字符的移动。
1. 根据期望保持匹配的协调性
下面列举了几种办法用来跳过不需要的邮编。把它们加到正则表达式 '44d{3}' 之前，可以获得期望的结果。非捕获型括号用来匹配不期望的邮编，这样能够快速地略过它们，找到匹配的邮编，在 $1 的捕获括号中。
代码语言：javascript复制mysql> set @s:='03824531449411615213441829505344272752010217443235';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='\d{5}';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t1.lv 1 from tab1 t1 where lv < length(@s)/5)
-> select lv from tab1),
-> t3 as
-> (select substring_index(substring_index(s,',',lv),',',-1) s from t1,t2)
-> select * from t3 where s like '44%';
-------
| s |
-------
| 44182 |
| 44272 |
-------
2 rows in set (0.00 sec)这种硬办法（brute-force method）主动略过非 44 开头邮编。注意不能使用 '(?:[1235-9][1235-9]d{3})*'，因为它不会匹配（也就无法略过） 43210 这样不期望的邮编。
代码语言：javascript复制mysql> set @r:='(?:(?!44)\d{5})*(44\d{3})';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
| 44323 |
--------
3 rows in set (0.01 sec)这个办法跳过非 44 开头的邮编，其中的想法与前一方法并无差别。在这里，期望的邮编（以 44 开头）导致否定向前查看（逆序环视） (?!44) 失败，于是略过停止。
代码语言：javascript复制mysql> set @r:='(?:\d{5})*?(44\d{3})';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
| 44323 |
--------
3 rows in set (0.00 sec)这个办法使用忽略优先量词，只有在需要的时候才略过某些文本。把它放在真正需要匹配的正则表达式前面，如果那个表达式失败，它就会匹配一个邮编。忽略优先 '(...)*?' 导致这一切的发生。因为存在忽略优先量词，在后面的表达式失败之前，'(?:d{5})' 甚至都不会尝试匹配。星号确保了，它会重复失败，直到最终找到匹配文本，这样就只能跳过希望跳过的文本。
把这个表达式和 '(44d{3})' 合起来，就能够提取 44 开头的邮编，而主动跳过其他的邮编。这个表达式能够重复应用于字符串，因为每次匹配的“起始匹配位置”都是某个邮编的开头位置，也就是保证下一次匹配是从一个邮编的开始，这正是正则表达式期望的。
前两种方法本质上是利用了 * 量词默认进行贪婪匹配（匹配优先）的特性，不会错误匹配出 44941。第三种方法因为是懒惰匹配（忽略优先），只会依次 5 个字符一组地略过不期望的邮编，同样不会错误匹配出 44941。但是，三种方法有一个共同的问题，就是因为回溯而错误地匹配了 44323，下面看具体分析及如何解决。
2. 不匹配时也应当保证协调性
前面的正则表达式手动跳过了不符合要求的邮编，可一旦不需要继续匹配，本轮匹配失败之后自然就是驱动过程和重试（回溯），这样就会从邮编字符串之中的某个位置开始。
再看数据样本，在 44272 匹配之后，目标文本中再也找不到匹配，所以本轮尝试宣告失败。但总的尝试并没有宣告失败。传动机构会进行驱动，从字符串的下一个字符开始应用正则表达式，这样就破坏了协调性。在第四次驱动之后，正则表达式略过 10217，错误地匹配 44323。
如果在字符串的开头应用，这三个表达式都没有问题，但是传动装置的驱动过程会破坏协调性。办法之一是禁止驱动过程，即在前两种办法中的 '(44d{3})' 之后添加 '?' 量词，将其改为匹配优先的可选项。这样，刻意安排的 '(?:(?!44)d{5})*...' 或 '(?:[1235-9]d{4}|d[1235-9]d{3})*...' 就只会在两种情况下停止：发生符合要求的匹配，或者邮编字符串结束。这样，如果存在符合要求的邮编，'(44d{3})' 就能匹配，而不会强迫回溯。
代码语言：javascript复制mysql> set @r:='(?:[1235-9]\d{4}|\d[1235-9]\d{3})*(44\d{3})?';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c-1)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
| |
--------
3 rows in set (0.00 sec)
mysql> set @r:='(?:(?!44)\d{5})*(44\d{3})?';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c-1)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
| |
--------
3 rows in set (0.01 sec)t1.c-1 是为了去掉字符串末尾的空匹配。这个方法仍然不够完善。原因之一是，即便目标字符串中没有符合要求的邮编，甚至是空串，也会匹配成功，接下来的处理程序会变得更复杂。不过其优点在于速度快，因为不需要回溯，也不需要传动装置进行任何驱动过程。
此方法不适用于第三个表达式，'(?:d{5})*?' * 量词忽略优先，'(44d{3})?' ? 量词可选匹配，因此存在很多空匹配。
代码语言：javascript复制mysql> set @r:='(?:\d{5})*?(44\d{3})?';
Query OK, 0 rows affected (0.00 sec)
mysql> select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s;
------ --------------------------------------------------------
| c | s |
------ --------------------------------------------------------
| 35 | ,,,,,,,,44941,,,,,,,,44182,,,,,,44272,,,,,,,,,,44323,, |
------ --------------------------------------------------------
1 row in set (0.00 sec)3. 使用 G 保证协调
更通用的办法是在这三个表达式开头添加 'G'。因为如果表达式的每次匹配以符合要求的邮编结尾，下次匹配开始时就不会进行驱动。而如果有驱动过程，开头的 'G' 会立刻导致匹配失败，因为在大多数流派中，只有在未发生驱动过程的情况下，它才能成功匹配。
代码语言：javascript复制mysql> set @r:='\G(?:[1235-9]\d{4}|\d[1235-9]\d{3})*(44\d{3})';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
--------
2 rows in set (0.00 sec)
mysql> set @r:='\G(?:(?!44)\d{5})*(44\d{3})';
Query OK, 0 rows affected (0.01 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
--------
2 rows in set (0.00 sec)
mysql> set @r:='\G(?:\d{5})*?(44\d{3})';
Query OK, 0 rows affected (0.00 sec)
mysql> with t1 as
-> (select regexp_count(@s, @r, '') c, regexp_extract(@s, @r, '') s),
-> t2 as
-> (with recursive tab1(lv) as
-> (select 1 lv union all select t.lv 1 from tab1 t,t1 where lv < t1.c)
-> select lv from tab1)
-> select regexp_replace(regexp_substr(@s, @r, 1, lv),@r,'$1') zip_44
-> from t1,t2;
--------
| zip_44 |
--------
| 44182 |
| 44272 |
--------
2 rows in set (0.01 sec)4. 本例的意义
这个例子有点极端，不过包含了许多保证正则表达式与数据协调性的知识。如果实际中需要处理这样的问题，可能不会用正则表达式来解决。比如在 MySQL8 中，直接用递归查询构造数字辅助表，然后在笛卡尔连接调用 substring 函数取得每个邮编，然后判断它是否以 44 开头。
代码语言：javascript复制mysql> -- MySQL解法
mysql> select s
-> from (select substring(@s,(lv-1)*5 1,5) s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1 where lv < length(@s)/5) select lv from tab1) t) t
-> where s like '44%';
-------
| s |
-------
| 44182 |
| 44272 |
-------
2 rows in set (0.00 sec)十、解析CSV文件
逗号分隔的值要么是“纯粹的”，仅仅包含在逗号之前，要么是在双引号之间，这时数据中的双引号以一对双引号表示。下面是一个例子：
代码语言：javascript复制Ten Thousand,10000, 2710 ,,"10,000","It's ""10 Grand"", baby",10K这一行包含七个字段（fields）：
代码语言：javascript复制Ten Thousand
10000
2710
空字段
10,000
It's "10 Grand", baby
10K为了从此行解析出各个字段，正则表达式需要能够处理两种格式。非引号格式包含引号和逗号之外的任何字符，可以用 '[^",] ' 匹配。
双引号字段可以包含双引号之外的任何字符（包括逗号和空格），还可以包含连在一起的两个双引号。所以，双引号字段可以由 "..." 之间的任意数量的 [^"]|"" 匹配，也就是 '"(?:[^"]|"")"'。
综合起来，'[^",] |"(?:[^"]|"")*"' 能够匹配一个字段。现在这个表达式可以实际应用到包含 CSV 文本行的字符串上了，对于双引号字符串，还需要去掉首尾两端的双引号，并把其中紧挨着的两个双引号替换为单个双引号。
在 MySQL 中，不需要知道具体是哪个多选分支匹配，统一用 trim 函数替换掉首尾的双引号，对于非双引号字符串该函数会原样返回字段值。
代码语言：javascript复制mysql> set @s:='Ten Thousand,10000, 2710 ,,"10,000","It's ""10 Grand"", baby",10K';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='[^",] |"(?:[^"]|"")*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
6 rows in set (0.00 sec)输出结果只有六行，没有输出为空的第四个字段，这显然不对。把 '[^",] ' 改为 '[^",]*' 是不行的。
代码语言：javascript复制mysql> set @r:='[^",]*|"(?:[^"]|"")*"';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
--------------
| s |
--------------
| Ten Thousand |
| |
| 10000 |
| |
| 2710 |
| |
| |
| |
| 10 |
| |
| 000 |
| |
| |
| |
| It's |
| |
| |
| 10 Grand |
| |
| |
| |
| baby |
| |
| |
| 10K |
| |
--------------
26 rows in set (0.00 sec)考虑第一个字段匹配之后的情况，此时表达式中没有元素可以匹配逗号（就本例来说），就会发生长度为 0 的成功匹配。所以每个有效匹配之间还有一个空匹配，在每个引号字段之前会多出一个空匹配，字符串末尾还会有一个空匹配。
实际上，这样的匹配可能有无穷多次，因为正则引擎可能在同一位置重复这样的匹配，现代的正则引擎会强迫进行驱动过程，所以同一位置不会发生两次长度为 0 的匹配。
1. 分解驱动过程
要解决问题，就不能依赖传动机构的驱动过程来越过逗号，而需要手工控制。能想到的办法有两个：
- 手工匹配逗号。如果采取此办法，需要把逗号作为普通字段匹配的一部分，在字符串中“迈步（pace ourselves）”。
- 确保每次匹配都从字段能够开始的位置开始。字段可以从行首，或者逗号开始。
可能更好的办法是把两者结合起来。从第一种办法（匹配逗号本身）出发，只需要保证逗号出现在最后一个字段之外的所有字段的末尾。可以在表达式前面添加 '^|,'，或者后面添加 '$|,'，用括号控制范围。
代码语言：javascript复制mysql> set @r:='(?:^|,)(?:[^",]*|"(?:[^"]|"")*")';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s,@r;
------------------------------------------------------------------- ----------------------------------
| @s | @r |
------------------------------------------------------------------- ----------------------------------
| Ten Thousand,10000, 2710 ,,"10,000","It's ""10 Grand"", baby",10K | (?:^|,)(?:[^",]*|"(?:[^"]|"")*") |
------------------------------------------------------------------- ----------------------------------
1 row in set (0.00 sec)
mysql> select regexp_substr(@s,@r,1,lv) s, length(convert(regexp_substr(@s,@r,1,lv) using utf8mb4)) l
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-------------- ------
| s | l |
-------------- ------
| Ten Thousand | 12 |
| ,10000 | 6 |
| , 2710 | 7 |
| , | 1 |
| , | 1 |
| ,000 | 4 |
| , | 1 |
| , baby | 6 |
| ,10K | 4 |
-------------- ------
9 rows in set (0.00 sec)结果不对。如果多个多选分支能够在同一位置匹配，必须小心地排列顺序。第一个多选分支 '[^",]*' 不需要匹配任何字符就能成功，除非之后的元素强迫，第二个多选分支不会获得尝试的机会。而这两个多选分支之后没有任何元素，所以第二个多选分支永远不会得到尝试的机会，这就是问题所在！
OK，交换一下多选分支的顺序：
代码语言：javascript复制mysql> set @r:='(?:^|,)(?:"(?:[^"]|"")*"|[^",]*)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)现在至少对测试数据来说是对了。更保险的办法是用 'G' 来确保每次匹配从上一次匹配结束的位置开始。
代码语言：javascript复制mysql> set @r:='\G(?:^|,)(?:"(?:[^"]|"")*"|[^",]*)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)再看一下在交换多选分支的顺序前，加上 'G' 的匹配结果：
代码语言：javascript复制mysql> set @r:='\G(?:^|,)(?:[^",]*|"(?:[^"]|"")*")';
Query OK, 0 rows affected (0.00 sec)
mysql> select @s,@r;
------------------------------------------------------------------- ------------------------------------
| @s | @r |
------------------------------------------------------------------- ------------------------------------
| Ten Thousand,10000, 2710 ,,"10,000","It's ""10 Grand"", baby",10K | G(?:^|,)(?:[^",]*|"(?:[^"]|"")*") |
------------------------------------------------------------------- ------------------------------------
1 row in set (0.00 sec)
mysql> select regexp_substr(@s,@r,1,lv) s, length(convert(regexp_substr(@s,@r,1,lv) using utf8mb4)) l
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-------------- ------
| s | l |
-------------- ------
| Ten Thousand | 12 |
| ,10000 | 6 |
| , 2710 | 7 |
| , | 1 |
| , | 1 |
-------------- ------
5 rows in set (0.00 sec)在匹配到 "10,000" 的双引号时，本轮尝试失败，传动机构会进行驱动，从字符串的下一个字符开始应用正则表达式。而如果有驱动过程，开头的 'G' 会立刻导致整个匹配失败。
2. 另一个办法
本节开头提到过第二个正确匹配各个字段的办法是，确保匹配只能容许出现字段的地方开始。从表面上看，这类似于添加 '^|,'，只是使用了逆序环视 '(?<=^|,)'。
代码语言：javascript复制mysql> set @r:='(?:(?<=^|,))(?:"(?:[^"]|"")*"|[^",]*)';
ount(@s, @r, '')) select lv from tab1) t;
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)注意这个表达式开头不能添加 'G'。环视是零宽断言，不消耗字符，因此每轮遇到逗号匹配失败时都会触发传动机构会进行驱动，这会导致 'G' 匹配失败而立刻返回。
代码语言：javascript复制mysql> set @r:='\G(?:(?<=^|,))(?:"(?:[^"]|"")*"|[^",]*)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
--------------
| s |
--------------
| Ten Thousand |
--------------
1 row in set (0.00 sec)有些正则引擎只允许使用定长的逆序环视，那么可以把 '(?<=^|,)' 替换为 '(?:^|(?<=,))'。
代码语言：javascript复制mysql> set @r:='(?:(?:^|(?<=,)))(?:"(?:[^"]|"")*"|[^",]*)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)相比第一种办法，这个实现太麻烦了。而且，它仍然依赖传动装置的驱动过程越过逗号，如果别的地方出了什么差错，它会容许 '..."10,000"...' 中逗号处的匹配。总的来说就是，不如第一种办法保险。
不过可以在表达式结尾添加 '(?=$|,)'，要求在逗号之前，或者是一行结束之前结束。简单理解就是要求字段内容两边都得是逗号，这样可以确保不会进行错误的匹配。
代码语言：javascript复制mysql> set @r:='(?:(?<=^|,))(?:"(?:[^"]|"")*"|[^",]*)(?=$|,)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)3. 进一步提高效率
可以使用固化分组提高效率，如把匹配双引号字段的子表达式从 '(?:[^"]|"")*' 改为 '(?>[^"] |"")*'
代码语言：javascript复制mysql> set @r:='\G(?:^|,)(?:"(?>[^"] |"")*"|[^",]*)';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)还可以使用占有优先量词提高效率。
代码语言：javascript复制mysql> set @r:='\G(?:^|,)(?:"(?>[^"] |"")* "|[^",]* )';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ',' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)4. 其他格式
- 使用任意字符，例如 ';' 或者制表符作为分隔。
只需要把逗号替换为对应的分隔符。
代码语言：javascript复制mysql> set @s:='Ten Thousand;10000; 2710 ;;"10,000";"It's ""10 Grand"", baby";10K';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='\G(?:^|;)(?:"(?>[^"] |"")* "|[^";]* )';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (trim(leading ';' from regexp_substr(@s,@r,1,lv)))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)- 容许分隔符之后出现空格，但不把它们作为值的一部分。
需要在分隔符之后添加 's*'，例如以 '(?:^|,s* )' 开头。
代码语言：javascript复制mysql> set @s:='Ten Thousand, 10000, 2710 , , "10,000", "It's ""10 Grand"", baby", 10K';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='\G(?:^|,\s* )(?:"(?>[^"] |"")* "|[^",]* )';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (regexp_replace(regexp_substr(@s,@r,1,lv),'^,\s*',''))),'""','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)- 用反斜线转义引号，例如用 " 而不是 "" 来表示值内部的引号。
通常这意味着反斜线可以在任何字符前出现并忽略，可以把 '[^"] |""' 替换为 '[^\"] |\.'。
代码语言：javascript复制mysql> set @s:='Ten Thousand, 10000, 2710 , , "10,000", "It's \"10 Grand\", baby", 10K';
Query OK, 0 rows affected (0.00 sec)
mysql> set @r:='\G(?:^|,\s* )(?:"(?>[^\\"] |\\.)* "|[^",]* )';
Query OK, 0 rows affected (0.00 sec)
mysql> select replace(trim(both '"' from (regexp_replace(regexp_substr(@s,@r,1,lv),'^,\s*',''))),'\"','"') s
-> from (with recursive tab1(lv) as (select 1 lv union all select t1.lv 1 from tab1 t1
-> where lv < regexp_count(@s, @r, '')) select lv from tab1) t;
-----------------------
| s |
-----------------------
| Ten Thousand |
| 10000 |
| 2710 |
| |
| 10,000 |
| It's "10 Grand", baby |
| 10K |
-----------------------
7 rows in set (0.00 sec)
