精通正则表达式 - 正则表达式实用技巧

用户1148526

发布于 2023-10-14 09:53:37

5100

发布于 2023-10-14 09:53:37

一、匹配连续行

希望匹配连续多行文本，常见的情况是，一个逻辑行（logical line）可以分为许多现实的行，每一行以反斜杠结尾。

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 @s\G
*************************** 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 模式的点号可以匹配换行符。

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') s\G
*************************** 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 中，每个反斜杠要用两个连续的反斜杠进行转义。

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, '') s\G
*************************** 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的数字

([01]?\d\d?|2[0-4]\d|25[0-5])

第一个分支可以匹配一位数 0-9、两位数 01-99、0 或 1 开头的三位数 000-199；第二个分支匹配 200-249；第三个分支匹配 250-255。

2. 第一段要求非零

(?!0+\.)([01]?\d\d?|2[0-4]\d|25[0-5])

使用顺序否定环视，指定不能出现 0.、00.、000. 等等。

3. 四段合并

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, '') s\G
*************************** 1. row ***************************
@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]))$
 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, '') s\G
*************************** 1. row ***************************
@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]))$
 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, '') s\G
*************************** 1. row ***************************
@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]))$
 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, '') s\G
*************************** 1. row ***************************
@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]))$
 c: 0
 s: 
1 row in set (0.00 sec)

4. 确定应用场合

上面的正则表达式必须借助锚点 ^ 和 $ 才能正常工作，否则可能匹配错误。

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, '') s\G
*************************** 1. row ***************************
@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]))
 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, '') s\G
*************************** 1. row ***************************
@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]))
 c: 1
 s: 123.3.21.22
1 row in set (0.00 sec)

为了避免匹配这样内嵌的文本，必须确保匹配文本两侧至少没有数字或者点号，可以使用否定环视实现。

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, '') s\G
*************************** 1. row ***************************
@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.])
 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, '') s\G
*************************** 1. row ***************************
@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.])
 c: 1
 s: 123.3.21.223
1 row in set (0.00 sec)

三、处理文件名

1. 去掉文件名开头的路径

例如把/usr/local/bin/gcc变成gcc。

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. 从路径中获取文件名

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 提供的函数实现更好：

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. 所在路径和文件名

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 函数先判断有无斜杠。

四、匹配对称的括号

为了匹配括号部分，可以尝试下面这些正则表达式：

$.*$ 括号及括号内部的任何字符。
$[^)]*$ 从一个开括号到最近的闭括号。
$[^()]*$ 从一个开括号到最近的闭括号，但是不允许其中包含开括号。

下面显示了对一行简单字符串应用这些表达式的结果。

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)，但无法匹配所需的内容。

这三个表达式都不合适。真正的问题在于，大多数系统中，正则表达式无法匹配任意深度的嵌套结构。可以用正则表达式来匹配特定深度的嵌套括号，如处理单层嵌套的正则表达式是：

\([^()]*(\([^()]*\)[^()]*)*\)

测试：

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' 或是空字符串。

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'开头的空字符，因为这个空字符比数字出现得更早。

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]*)?'。

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]+)'。

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' 这样的数字。要想真正匹配期望的文本，同时忽略不期望的文本，求得平衡，就必须了解实际的待匹配文本。用来提取浮点数的正则表达式必须包含在一个大的正则表达式内部，例如用 '^...' 或者 'num\s*=\s*...'。

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 文件中的一行内容，这个文件的每一行都按下面的数据格式组织：

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\" 的例子，这里的结束分隔符是一个引号，匹配开始和结束分隔符很容易，一下就能写出的正则表达式为：'".*"'。本例中它恰巧可以利用量词缺省的贪婪特性，直接匹配出正文中的双引号。

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\"。

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 "。

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)

这是因为，第一个比引号之前的确存在一个反斜线，但这个反斜线本身是转义的，它不是用来转义之后的双引号的，也就是说这个引号其实是表示引用文本的结束。而逆序环视无法识别这个被转义的反斜线，如果在这个引号之前有任意多个 ‘\\’，用逆序环视只会更糟。本例中可以利用量词的懒惰特性，直接匹配出想要的结果。

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)

更为细致的写法是，将可能出现在正文部分的文本都列出，其中可以包括转义的字符（'\\.'），也可以包括非引号的任何字符 '[^"]'，于是得到 '"(\\.|[^"])*"'。

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.

它应该无法匹配，因为其中没有结束分隔符，但结果却匹配到了。

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 后面的反斜线时，'[^"]' 匹配到了反斜线，之后的那个引号被认为是一个结束的引号。

这个例子的重要启示是：如果回溯会导致不期望，与多选结构有关的匹配结果，问题很可能在于，任何成功的匹配都不过是多选分支的排列顺序造成的偶然结果。

实际上，如果把这个正则表达式的多选分支反过来排列，它就会错误地匹配任何包含转义双引号的字符串。

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)

真正的问题在于，各个多选分支能够匹配的内容发生了重叠。解决方法是，保证各个多选分支能够匹配的内容互斥。本例中必须确保反斜线不能以其他的方式匹配，也就是说把 '[^"]' 改为 '[^\\"]'。这样就能识别双引号和文本中的“特殊”反斜线，必须根据情况分别处理。结果就是 '"(\\.|[^\\"])*"'。

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 @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)

占有优先量词和固化分组解决此问题效率更高，因为这样报告匹配失败的速度更快。

七、除去文本首尾的空白字符

去除文本首尾的空白字符是经常要完成的任务。总的来说最好的办法使用两个替换。

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 中，对空串用该正则表达式执行替换时报错：

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 query

mysql> 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 中，对空串用该正则表达式执行替换时报错：

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 query

mysql> 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*$'，捕获括号之外。非捕获组外的问号在这里是必须的，因为如果一行数据只包含空白字符的行，必须出现问号，表达式才能正常工作。如果没有问号，可能会无法匹配，错过这种只有空白符的行。

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 标签。它通常都能工作，例如去除标签：

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=">">。这样，简单的 '<[^>]+>' 就无法匹配了。

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 的引文可以用单引号，也可以用双引号，但不容许转义嵌套的引号，所以可以直接用 '"[^"]"*' 和 ''[^']*'' 来匹配。把这些和 “其他文本” 表达式 '[^'">]' 合起来得到：'<("[^"]"*|'[^']*'|[^'">])*>'。

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：

...<a href="http://www.oreilly.com">O'Reilly Media</a>...

<A> 标签的内容可能相当复杂，因此可以分两步实现。第一步是提取 <A> 标签内部的内容，也就是链接文本，然后从 <A> 标签中提取 URL 地址。

实现第一步的正则表达式为：

'<a\b([^>]+)>(.*?)</a>'

它会把 <A> 的内容放入

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> | <a\b([^>]+)>(.*?)</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 的正则表达式如下：

\bhref\s*=\s*(?:"([^"]*)"|'([^']*)'|([^'">\s]+))

说明：

\bhref 匹配“href”属性。
\s*=\s* 匹配 “=” 两端可能出现空白字符。
"([^"]*)" 匹配双引号字符串。
'([^']*)' 匹配单引号字符串。
([^'">\s]+) 其他文本，匹配除单双引号、> 和空白符以外的任意字符。

匹配值的每个多选结构都加了括号，来捕获确切的文本。最外层的分组不需要捕获，因此使用非捕获型括号 ?:，这样做既清楚又高效。因为需要捕获整个 href 的值，这里使用了 + 来限制其他文本多选分支。这个加号不会导致奇怪的结果，因为这外面没有直接作用于整个多选结构的量词。

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+))?(/.*)?$'。

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）中提取主机名：

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)

从随机文本中准确提取主机名：

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])$'。

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' 同样可以匹配下划线。

接在主机名后面的是 path（路径）部分，它使用逆序环视来确保 URL 不会以句末的点号结尾。

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}' 在整个解析过程中任何时候都能匹配，绝对没有传动装置的驱动和重试。

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：

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. 根据期望保持匹配的协调性

下面列举了几种办法用来跳过不需要的邮编。把它们加到正则表达式 '44\d{3}' 之前，可以获得期望的结果。非捕获型括号用来匹配不期望的邮编，这样能够快速地略过它们，找到匹配的邮编，在 $1 的捕获括号中。

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 这样不期望的邮编。

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) 失败，于是略过停止。

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})' 甚至都不会尝试匹配。星号确保了，它会重复失败，直到最终找到匹配文本，这样就只能跳过希望跳过的文本。

把这个表达式和 '(44\d{3})' 合起来，就能够提取 44 开头的邮编，而主动跳过其他的邮编。这个表达式能够重复应用于字符串，因为每次匹配的“起始匹配位置”都是某个邮编的开头位置，也就是保证下一次匹配是从一个邮编的开始，这正是正则表达式期望的。

前两种方法本质上是利用了 * 量词默认进行贪婪匹配（匹配优先）的特性，不会错误匹配出 44941。第三种方法因为是懒惰匹配（忽略优先），只会依次 5 个字符一组地略过不期望的邮编，同样不会错误匹配出 44941。但是，三种方法有一个共同的问题，就是因为回溯而错误地匹配了 44323，下面看具体分析及如何解决。

2. 不匹配时也应当保证协调性

前面的正则表达式手动跳过了不符合要求的邮编，可一旦不需要继续匹配，本轮匹配失败之后自然就是驱动过程和重试（回溯），这样就会从邮编字符串之中的某个位置开始。

再看数据样本，在 44272 匹配之后，目标文本中再也找不到匹配，所以本轮尝试宣告失败。但总的尝试并没有宣告失败。传动机构会进行驱动，从字符串的下一个字符开始应用正则表达式，这样就破坏了协调性。在第四次驱动之后，正则表达式略过 10217，错误地匹配 44323。

如果在字符串的开头应用，这三个表达式都没有问题，但是传动装置的驱动过程会破坏协调性。办法之一是禁止驱动过程，即在前两种办法中的 '(44\d{3})' 之后添加 '?' 量词，将其改为匹配优先的可选项。这样，刻意安排的 '(?:(?!44)\d{5})*...' 或 '(?:[1235-9]\d{4}|\d[1235-9]\d{3})*...' 就只会在两种情况下停止：发生符合要求的匹配，或者邮编字符串结束。这样，如果存在符合要求的邮编，'(44\d{3})' 就能匹配，而不会强迫回溯。

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})*?' * 量词忽略优先，'(44\d{3})?' ? 量词可选匹配，因此存在很多空匹配。

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' 会立刻导致匹配失败，因为在大多数流派中，只有在未发生驱动过程的情况下，它才能成功匹配。

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 开头。

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文件

逗号分隔的值要么是“纯粹的”，仅仅包含在逗号之前，要么是在双引号之间，这时数据中的双引号以一对双引号表示。下面是一个例子：

Ten Thousand,10000, 2710 ,,"10,000","It's ""10 Grand"", baby",10K

这一行包含七个字段（fields）：

Ten Thousand
10000
 2710 
空字段
10,000
It's "10 Grand", baby
10K

为了从此行解析出各个字段，正则表达式需要能够处理两种格式。非引号格式包含引号和逗号之外的任何字符，可以用 '[^",]+' 匹配。

双引号字段可以包含双引号之外的任何字符（包括逗号和空格），还可以包含连在一起的两个双引号。所以，双引号字段可以由 "..." 之间的任意数量的 [^"]|"" 匹配，也就是 '"(?:[^"]|"")"'。

综合起来，'[^",]+|"(?:[^"]|"")*"' 能够匹配一个字段。现在这个表达式可以实际应用到包含 CSV 文本行的字符串上了，对于双引号字符串，还需要去掉首尾两端的双引号，并把其中紧挨着的两个双引号替换为单个双引号。

在 MySQL 中，不需要知道具体是哪个多选分支匹配，统一用 trim 函数替换掉首尾的双引号，对于非双引号字符串该函数会原样返回字段值。

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)

输出结果只有六行，没有输出为空的第四个字段，这显然不对。把 '[^",]+' 改为 '[^",]*' 是不行的。

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）”。
确保每次匹配都从字段能够开始的位置开始。字段可以从行首，或者逗号开始。

可能更好的办法是把两者结合起来。从第一种办法（匹配逗号本身）出发，只需要保证逗号出现在最后一个字段之外的所有字段的末尾。可以在表达式前面添加 '^|,'，或者后面添加 '$|,'，用括号控制范围。

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，交换一下多选分支的顺序：

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' 来确保每次匹配从上一次匹配结束的位置开始。

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' 的匹配结果：

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. 另一个办法

本节开头提到过第二个正确匹配各个字段的办法是，确保匹配只能容许出现字段的地方开始。从表面上看，这类似于添加 '^|,'，只是使用了逆序环视 '(?<=^|,)'。

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' 匹配失败而立刻返回。

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)

有些正则引擎只允许使用定长的逆序环视，那么可以把 '(?<=^|,)' 替换为 '(?:^|(?<=,))'。

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"...' 中逗号处的匹配。总的来说就是，不如第一种办法保险。

不过可以在表达式结尾添加 '(?=$|,)'，要求在逗号之前，或者是一行结束之前结束。简单理解就是要求字段内容两边都得是逗号，这样可以确保不会进行错误的匹配。

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. 进一步提高效率

可以使用固化分组提高效率，如把匹配双引号字段的子表达式从 '(?:[^"]|"")*' 改为 '(?>[^"]+|"")*'

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)

还可以使用占有优先量词提高效率。

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. 其他格式

使用任意字符，例如 ';' 或者制表符作为分隔。

只需要把逗号替换为对应的分隔符。

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*+)' 开头。

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)

用反斜线转义引号，例如用 \" 而不是 "" 来表示值内部的引号。

通常这意味着反斜线可以在任何字符前出现并忽略，可以把 '[^"]+|""' 替换为 '[^\\"]+|\\.'。

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)

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-07-05，如有侵权请联系 cloudcommunity@tencent.com 删除

正则表达式