expr NOT REGEXP pat, expr NOT RLIKE pat

这等同于 NOT (expr REGEXP pat)。This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

如果字符串expr与模式pat指定的正则表达式匹配，则返回 1，否则返回 0。如果expr或pat为NULL，则返回值为NULL。Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

REGEXP和RLIKE是REGEXP_LIKE()的同义词。REGEXP and RLIKE are synonyms for REGEXP_LIKE().

关于匹配如何发生的更多信息，请参见REGEXP_LIKE()的描述。For additional information about how matching occurs, see the description for REGEXP_LIKE().

mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

返回字符串 expr 中与模式 pat 匹配的子字符串的起始索引，如果没有匹配则返回 0。如果 expr 或 pat 为 NULL，则返回值为 NULL。字符索引从 1 开始。Returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match. If expr or pat is NULL, the return value is NULL. Character indexes begin at 1.

REGEXP_INSTR() 接受以下可选参数：REGEXP_INSTR() takes these optional arguments:

关于匹配如何发生的更多信息，请参见REGEXP_LIKE()的描述。For additional information about how matching occurs, see the description for REGEXP_LIKE().

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+

REGEXP_LIKE(expr, pat[, match_type])

如果字符串expr与模式pat指定的正则表达式匹配，则返回 1，否则返回 0。如果expr或pat为NULL，则返回值为NULL。Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

模式可以是扩展正则表达式，其语法在正则表达式语法中有讨论。模式不必是字面字符串。例如，它可以指定为字符串表达式或表列。The pattern can be an extended regular expression, the syntax for which is discussed in Regular Expression Syntax. The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

可选的 match_type 参数是一个字符串，可以包含以下任意或全部字符，用于指定匹配方式：The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:

如果在match_type中指定了相互矛盾的选项字符，则最右边的选项优先。If characters specifying contradictory options are specified within match_type, the rightmost one takes precedence.

默认情况下，正则表达式操作在决定字符类型和执行比较时使用expr和pat参数的字符集和排序规则。如果参数具有不同的字符集或排序规则，则适用第 12.8.4 节，“表达式中的排序规则强制性”中描述的强制性规则。可以使用显式排序规则指示符来改变比较行为。By default, regular expression operations use the character set and collation of the expr and pat arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 12.8.4, “Collation Coercibility in Expressions”. Arguments may be specified with explicit collation indicators to change comparison behavior.

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
+---------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+

match_type 可以用 c 或 i 字符来指定，以覆盖默认的大小写敏感性。例外情况：如果任一参数是二进制字符串，则即使 match_type 包含 i 字符，这些参数也将作为二进制字符串以区分大小写的方式处理。match_type may be specified with the c or i characters to override the default case sensitivity. Exception: If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings, even if match_type contains the i character.

mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+

mysql> SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
|                         1 |
+---------------------------+
mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
|                              0 |
+--------------------------------+

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

将字符串 expr 中符合模式 pat 的正则表达式的出现替换为替换字符串 repl，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL。Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

REGEXP_REPLACE() 接受以下可选参数：REGEXP_REPLACE() takes these optional arguments:

此函数返回的结果使用用于匹配搜索的表达式的字符集和排序规则。The result returned by this function uses the character set and collation of the expression searched for matches.

关于匹配如何发生的更多信息，请参见REGEXP_LIKE()的描述。For additional information about how matching occurs, see the description for REGEXP_LIKE().

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

返回匹配由模式 pat 指定的正则表达式的字符串 expr 的子串，如果没有匹配则返回 NULL。如果 expr 或 pat 为 NULL，则返回值为 NULL。Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL.

REGEXP_SUBSTR() 接受以下可选参数：REGEXP_SUBSTR() takes these optional arguments:

此函数返回的结果使用用于匹配搜索的表达式的字符集和排序规则。The result returned by this function uses the character set and collation of the expression searched for matches.

关于匹配如何发生的更多信息，请参见REGEXP_LIKE()的描述。For additional information about how matching occurs, see the description for REGEXP_LIKE().

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+

^

匹配字符串的开头。Match the beginning of a string.

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

$

匹配字符串的结尾。Match the end of a string.

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

.

匹配任意字符（包括回车和换行，但要在字符串中间匹配这些字符，必须使用m（多行）匹配控制字符或在模式中使用(?m)修饰符）。Match any character (including carriage return and newline, although to match these in the middle of a string, the m (multiple line) match-control character or the (?m) within-pattern modifier must be given).

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*

匹配零个或多个a字符的任意序列。Match any sequence of zero or more a characters.

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字符的任意序列。Match any sequence of one or more a characters.

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

a?

匹配零个或一个a字符。Match either zero or one a character.

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之一。Alternation; match either of the sequences de or 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序列的实例。Match zero or more instances of the sequence abc.

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

{1}, {2,3}

重复；{n} 和 {m,n} 符号提供了一种更通用的方式来书写匹配前一个原子（或模式的“片段”）多次出现的正则表达式。m 和 n 是整数。Repetition; {n} and {m,n} notation provide a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern. m and n are integers.

更准确地说，a{n} 精确匹配 n 个 a。a{n,} 匹配 n 个或更多 a。a{m,n} 匹配 m 到 n 个 a（包括两端）。如果同时提供了 m 和 n，则 m 必须小于或等于 n。To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive. If both m and n are given, m must be less than or equal to 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]匹配任何十进制数字。要包含字面量的]字符，它必须紧跟在开括号[之后。要包含字面量的-字符，它必须写在开头或结尾。任何在[]对中没有定义特殊意义的字符只匹配它自身。Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

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(+)] 都是同义的。等价类不能用作范围的端点。Within a bracket expression (written using [ and ]), [=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, if o and (+) are the members of an equivalence class, [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

[:character_class:]

在方括号表达式（使用 [ 和 ] 编写）中，[:character_class:] 表示一个字符类，匹配属于该类的所有字符。下表列出了标准的类名。这些名称代表在 ctype(3) 手册页中定义的字符类。特定的语言环境可能提供其他类名。字符类不能用作范围的端点。Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

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

匹配引擎使用内存来管理其内部堆栈。要控制堆栈可用的最大内存（以字节为单位），请设置 regexp_stack_limit 系统变量。The match engine uses memory for its internal stack. To control the maximum available memory for the stack in bytes, set the regexp_stack_limit system variable.

匹配引擎按照步骤进行操作。要控制引擎执行的最大步骤数（从而间接控制执行时间），请设置 regexp_time_limit 系统变量。由于此限制以步骤数表示，因此仅间接影响执行时间。通常，这个量级为毫秒级。The match engine operates in steps. To control the maximum number of steps performed by the engine (and thus indirectly the execution time), set the regexp_time_limit system variable. Because this limit is expressed as number of steps, it affects execution time only indirectly. Typically, it is on the order of milliseconds.