mysql的正则表达式

跟着官网学习

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

Regular Expressions

Table 12.13 Regular Expression Functions and Operators
Name Description
NOT REGEXP Negation of REGEXP
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression
RLIKE Whether string matches regular expression

A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the functions and operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations. See also Section 3.3.4.7, “Pattern Matching”.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer’s implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

Regular Expression Functions and Operators

Regular Expression Syntax

Regular Expression Resource Control

Regular Expression Compatibility Considerations

Regular Expression Functions and Operators

expr NOT REGEXP pat, expr NOT RLIKE pat

This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

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 and RLIKE are synonyms for 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 |
+---------------------+
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
+----------------+-----------------------+
| 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' |
+----------------+-----------------------+
|              1 |                     0 |
+----------------+-----------------------+

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

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() takes these optional arguments:

pos: The position in expr at which to start the search. If omitted, the default is 1.

occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring's first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.

match_type: A string that specifies how to perform matching. The meaning is as described for 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])

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.

The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:

c: Case sensitive matching.

i: Case-insensitive matching.

m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.

n: The . character matches line terminators. The default is for . matching to stop at the end of a line.

u: Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators. 

If characters specifying contradictory options are specified within match_type, the rightmost one takes precedence.

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 10.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 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.
Note

Because MySQL uses the C escape syntax in strings (for example, \n to represent the newline character), you must double any \ that you use in your expr and pat arguments.

mysql> SELECT REGEXP_LIKE(‘Michael!’, ‘.’);
±------------------------------+
| REGEXP_LIKE(‘Michael!’, '.’) |
±------------------------------+
| 1 |
±------------------------------+
mysql> SELECT REGEXP_LIKE(‘new*\nline’, 'new\.\line’);
±---------------------------------------------+
| REGEXP_LIKE('new\nline’, 'new\.\*line’) |
±---------------------------------------------+
| 0 |
±---------------------------------------------+
mysql> SELECT REGEXP_LIKE(‘a’, ‘¹’);
±---------------------------+
| REGEXP_LIKE(‘a’, ‘²’) |
±---------------------------+
| 1 |
±---------------------------+
mysql> SELECT REGEXP_LIKE(‘a’, ‘A’), REGEXP_LIKE(‘a’, BINARY ‘A’);
±----------------------±-----------------------------+
| REGEXP_LIKE(‘a’, ‘A’) | REGEXP_LIKE(‘a’, BINARY ‘A’) |
±----------------------±-----------------------------+
| 1 | 0 |
±----------------------±-----------------------------+

mysql> SELECT REGEXP_LIKE(‘abc’, ‘ABC’);
±--------------------------+
| REGEXP_LIKE(‘abc’, ‘ABC’) |