MySQL的函数和运算符 - 字符串函数和运算符 - 正则表达式（2）

数据库杂货铺 2021-06-26

495

正则表达式语法

正则表达式描述一组字符串。最简单的正则表达式中没有特殊字符。例如，正则表达式 hello 只匹配 hello 而不匹配其他内容。

非普通正则表达式使用特定的结构，以便它们可以匹配多个字符串。例如，正则表达式 hello|world 包含变换操作符 |，并匹配 hello 或 world。

举一个更复杂的示例，正则表达式 B[an]*s 匹配字符串 Bananas、Baaaaas、B，以及任何其他以 B 开头、以 s 结尾、中间包含任意数量的 a 或 n 字符的字符串。

下面的列表涵盖了一些可以在正则表达式中使用的基本特殊字符和结构。

● ^

匹配字符串的开头。

mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$');                   -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^fo');                      -> 1

● $

匹配字符串的结尾。

mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$');                 -> 1
mysql> SELECT REGEXP_LIKE('fo\no', '^fo$');                    -> 0

● .

匹配任何字符（包括回车符和换行符，但要在字符串中间匹配这些字符，必须指定 m（多行）匹配控制字符或在模式中指定 (?m) 修饰符）。

mysql> SELECT REGEXP_LIKE('fofo', '^f.*$');                    -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');                -> 0
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');           -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');           -> 1

● a*

匹配任意由 0 个或多个 a 字符组成的序列。

mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n');                   -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n');                      -> 1

● a+

匹配一个或多个 a 字符的序列。

mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');                      -> 0

● a?

匹配 0 个或 1 个 a 字符。

mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n');                      -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n');                    -> 0

● de|abc

交替；匹配 de 或 abc 序列。

mysql> SELECT REGEXP_LIKE('pi', 'pi|apa');                     -> 1
mysql> SELECT REGEXP_LIKE('axe', 'pi|apa');                    -> 0
mysql> SELECT REGEXP_LIKE('apa', 'pi|apa');                    -> 1
mysql> SELECT REGEXP_LIKE('apa', '^(pi|apa)$');                -> 1
mysql> SELECT REGEXP_LIKE('pi', '^(pi|apa)$');                 -> 1
mysql> SELECT REGEXP_LIKE('pix', '^(pi|apa)$');                -> 0

● (abc)*

匹配序列 abc 的 0 个或多个实例。

mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$');                    -> 1
mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$');                   -> 0
mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$');                  -> 1

● {1}, {2,3}

重复; {n} 和 {m,n} 符号提供了一种更通用的书写正则表达式的方式，可以匹配模式中前面的原子(或“片段”)的多次出现。m 和 n 是整数。

■ a*

可以写成 a{0,}。

■ a+

可以写成 a{1,}。

■ a?

可以写成 a{0,1}。

更准确地，a{n} 正好匹配 a 的 n 个实例。a{n,} 匹配 a 的 n 个或多个实例。a{m,n} 匹配 a 的 m 到 n 个实例（包含 m 个或者 n 个）。如果 m 和 n 都给定，则 m 必须小于或等于 n。

mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e');              -> 0
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');              -> 1
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');           -> 1

● [a-dX], [^a-dX]

匹配 a、b、c、d 或 X 中的任何字符(如果使用 ^ 则不匹配)。两个字符之间的 - 字符形成一个匹配从第一个字符到第二个字符的范围。例如，[0-9] 匹配任何十进制数字。要包含一个字面量的 ] 字符，它必须紧跟着左括号 [。要包含一个字面量 - 字符，它必须写在第一个或最后一个。在 [] 对中没有定义特殊含义的任何字符只匹配它自己。

mysql> SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');                 -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$');               -> 0
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');              -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');             -> 0
mysql> SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');            -> 1
mysql> SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');           -> 0

● [=character_class=]

用在括号表达式中(使用 [ 和 ])，[=character_class=] 表示一个等价类。它匹配所有具有相同排序值的字符，包括它本身。例如，如果 o 和 (+) 是等价类的成员，那么 [[=o=]]，[[=(+)=]] 和 [o(+)] 都是同义的。等价类不能用作范围的端点。

● [:character_class:]

在一个方括号表达式中([ 和 ])，[:character_class:] 表示一个字符类，它匹配属于该类的所有字符。下表列出了标准类名。这些名称代表在 ctype(3) 手册页中定义的字符类。特定区域设置可能提供其他类名。字符类不能用作范围的端点。

ctype(3) 手册页（这个东东在哪里？未找到）

字符类名称	含义
alnum	字母数字字符
alpha	字母字符
blank	空格字符
cntrl	控制字符
digit	数字字符
graph	图形字符
lower	小写字母字符
print	图形或空格字符
punct	标点符号
space	空格、制表符、换行符和回车符
upper	大写字母字符
xdigit	十六进制数字字符

mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+');       -> 1
mysql> SELECT REGEXP_LIKE('!!', '[[:alnum:]]+');               -> 0

若要在正则表达式中使用特殊字符的文字实例，请在其前面加上两个反斜杠字符(\)。MySQL 解析器解释其中一个反斜杠，而正则表达式库解释另一个。例如，要匹配包含特殊 + 字符的字符串 1+2，下面的正则表达式中只有最后一个是正确的:

mysql> SELECT REGEXP_LIKE('1+2', '1+2');                       -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\+2');                      -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\\+2');                     -> 1

正则表达式资源控制

REGEXP_LIKE() 和类似的函数使用的资源可以通过设置系统变量来控制:

● 匹配引擎为其内部堆栈使用内存。要控制堆栈的最大可用内存(以字节为单位)，请设置 regexp_stack_limit 系统变量。

● 匹配引擎是按步骤运行的。要控制引擎执行的最大步骤数(从而间接地控制执行时间)，请设置 regexp_time_limit 系统变量。因为这个限制是用步骤数表示的，所以它只间接地影响执行时间。一般来说，它是以毫秒计的。

正则表达式兼容性注意事项

在 MySQL 8.0.4 之前，MySQL 使用 Henry Spencer 正则表达式库来支持正则表达式操作，而不是 Unicode 国际化组件(ICU)。下面的讨论描述了可能影响应用程序的 Spencer 和 ICU 库之间的差异:

● 在 Spencer 库中，REGEXP 和 RLIKE 操作符以字节方式工作，因此它们不是多字节安全的，可能会在多字节字符集中产生意外的结果。此外，这些操作符按字节值比较字符，即使给定的排序规则将重音字符视为相等，比较时也可能会被认为不相等。

ICU 完全支持 Unicode，并且是多字节安全的。它的正则表达式函数将所有字符串视为 UTF-16。应该记住，位置索引基于16位块，而不是代码点（Code Point）。这意味着，当传递给这些函数时，使用多个块(chunk) 的字符可能会产生意想不到的结果，如下面所示:

mysql> SELECT REGEXP_INSTR('🍣🍣b', 'b');
+--------------------------+
| REGEXP_INSTR('??b', 'b') |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4);
+--------------------------------+
| REGEXP_INSTR('??bxxx', 'b', 4) |
+--------------------------------+
|                              5 |
+--------------------------------+
1 row in set (0.00 sec)

Unicode 基本多语言平面(Unicode Basic Multilingual Plane)中的字符，包括大多数现代语言使用的字符，在这方面是安全的:

mysql> SELECT REGEXP_INSTR('бжb', 'b');
+----------------------------+
| REGEXP_INSTR('бжb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('עבb', 'b');
+----------------------------+
| REGEXP_INSTR('עבb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('µå周çб', '周');
+------------------------------------+
| REGEXP_INSTR('µå周çб', '周')       |
+------------------------------------+
|                                  3 |
+------------------------------------+
1 row in set (0.00 sec)

前两个例子中使用的表情符号“sushi”字符 🍣(U+1F363) 不包含在基本多语言平面中，而是包含在 Unicode 的补充多语言平面中。当 REGEXP_SUBSTR() 或类似的函数开始在字符中间搜索时，表情符号和其他 4 字节字符可能会出现另一个问题。下面示例中的两个语句都从第一个参数中的第二个 2 字节位置开始。第一条语句工作于仅由 2 字节(BMP)字符组成的字符串。第二个语句包含 4 字节的字符，结果中错误地解释了这些字符，因为前两个字节被去掉了，所以字符数据的其余部分没有对齐。

mysql> SELECT REGEXP_SUBSTR('周周周周', '.*', 2);
+----------------------------------------+
| REGEXP_SUBSTR('周周周周', '.*', 2)     |
+----------------------------------------+
| 周周周                                 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2);
+--------------------------------+
| REGEXP_SUBSTR('????', '.*', 2) |
+--------------------------------+
| ?㳟揘㳟揘㳟揘                  |
+--------------------------------+
1 row in set (0.00 sec)

● 对于 . 操作符，Spencer 库匹配字符串表达式中的任何位置(包括中间)的行结束符(回车符、换行符)。要在 ICU 中匹配字符串中间的行结束符，请指定 m 匹配控制字符。

● Spencer 库支持单词开始和结束边界标记([[:<:]] 和 [[:>:]] 符号)。ICU 不支持。对于 ICU，可以使用 \b 匹配单词边界；双写反斜杠，因为 MySQL 将它解释为字符串中的转义字符。

● Spencer 库支持对元素括号表达式([.characters.] 符号)进行排序。ICU 不支持。

● 对于重复计数({n} 和 {m,n} 符号)，Spencer 库允许的最大值为 255。ICU 没有这样的限制，但是可以通过设置 regexp_time_limit 系统变量来限制匹配引擎步骤的最大数量。

● ICU 将圆括号解释为元字符（metacharacters）。要在正则表达式中指定左括号或右括号 (，必须转义：

mysql> SELECT REGEXP_LIKE('(', '(');
ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE('(', '\\(');
+-------------------------+
| REGEXP_LIKE('(', '\\(') |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT REGEXP_LIKE(')', ')');
ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE(')', '\\)');
+-------------------------+
| REGEXP_LIKE(')', '\\)') |
+-------------------------+
|                       1 |
+-------------------------+

● ICU 也将方括号解释为元字符，但只有左方括号需要转义再用作字面值字符:

mysql> SELECT REGEXP_LIKE('[', '[');
ERROR 3696 (HY000): The regular expression contains an
unclosed bracket expression.
mysql> SELECT REGEXP_LIKE('[', '\\[');
+-------------------------+
| REGEXP_LIKE('[', '\\[') |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT REGEXP_LIKE(']', ']');
+-----------------------+
| REGEXP_LIKE(']', ']') |
+-----------------------+
|                     1 |
+-----------------------+

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 字符串函数和运算符 - 正则表达式（2）

评论