Regular expressions (regex) are a powerful tool for defining complex queries. Here is a simple document, which ignores some detailed information. A regular expression defines a string rule. The simplest regular expression does not contain any reserved words. For example, the regular expression "hello" only matches the string "hello. The regular expression uses some special regular expressions.
Regular expressions (regex) are a powerful tool for defining complex queries.
Here is a simple document, which ignores some detailed information.
A regular expression defines a string rule. The simplest regular expression does not contain any reserved words. For example, the regular expression "hello" only matches the string "hello.
The regular expression uses some special structures, so it can match more strings. For example, the regular expression hello | word can match both the string "hello" and the string "word ". To give a more complex example, regular expression B [an] * s can match strings "Bananas", "Baaaaas", "Bs", and any other string starting with B and ending with s, it can include a combination of any a and any n.
The following reserved words can be used in a regular expression:
^
Start of the string following the matched string
Mysql> select "fonfo" REGEXP "^ fo $";-> 0 (mismatch)
Mysql> select "fofo" REGEXP "^ fo";-> 1 (matching)
$
The end of the matched string
Mysql> select "fono" REGEXP "^ fono $";-> 1 (matching)
Mysql> select "fono" REGEXP "^ fo $";-> 0 (mismatch)
.
Match any characters (including new lines)
Mysql> select "fofo" REGEXP "^ f. *";-> 1 (matching)
Mysql> select "fonfo" REGEXP "^ f. *";-> 1 (matching)
A *
Match any number of a (including empty strings)
Mysql> select "Ban" REGEXP "^ Ba * n";-> 1 (matching)
Mysql> select "Baaan" REGEXP "^ Ba * n";-> 1 (matching)
Mysql> select "Bn" REGEXP "^ Ba * n";-> 1 (matching)
A +
Match any number of a (not including empty strings)
Mysql> select "Ban" REGEXP "^ Ba + n";-> 1 (matching)
Mysql> select "Bn" REGEXP "^ Ba + n";-> 0 (indicating mismatch)
A?
Match one or zero
Mysql> select "Bn" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Ban" REGEXP "^ Ba? N ";-> 1 (indicating matching)
Mysql> select "Baan" REGEXP "^ Ba? N ";-> 0 (indicating mismatch)
De | abc
Match de or abc
Mysql> select "pi" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "axe" REGEXP "pi | apa";-> 0 (mismatch)
Mysql> select "apa" REGEXP "pi | apa";-> 1 (indicating matching)
Mysql> select "apa" REGEXP "^ (pi | apa) $";-> 1 (indicating matching)
Mysql> select "pi" REGEXP "^ (pi | apa) $";-> 1 (indicating matching)
Mysql> select "pix" REGEXP "^ (pi | apa) $";-> 0 (indicating mismatch)
(Abc )*
Match any number of abc (including empty strings)
Mysql> select "pi" REGEXP "^ (pi) * $";-> 1 (matching)
Mysql> select "pip" REGEXP "^ (pi) * $";-> 0 (mismatch)
Mysql> select "pipi" REGEXP "^ (pi) * $";-> 1 (matching)
{1}
{2, 3}
This is a more comprehensive method, which can implement the functions of the previous several reserved words
A *
Can be written as a {0 ,}
A +
Can be written as a {1 ,}
A?
Can be written as a {0, 1}
There is only one integer parameter I in {}, indicating that the character can only appear I times; there is an integer parameter I in {}, followed by a ",", it indicates that the character can appear for I or more times. in {}, there is only one integer parameter I, followed by a ",", followed by an integer parameter j, it indicates that the character can only appear more than I times, less than j times (including I times and j times ). The integer parameter must be greater than or equal to 0 and less than or equal to RE_DUP_MAX (default value: 255 ). If two parameters exist, the second parameter must be greater than or equal to the first parameter.
[A-dX]
Match "a", "B", "c", "d", or "X"
[^ A-dX]
Matches any character except "a", "B", "c", "d", and "X. "[", "]" Must be used in pairs
Mysql> select "aXbc" REGEXP "[a-dXYZ]";-> 1 (indicating matching)
Mysql> select "aXbc" REGEXP "^ [a-dXYZ] $";-> 0 (indicating mismatch)
Mysql> select "aXbc" REGEXP "^ [a-dXYZ] + $";-> 1 (indicating matching)
Mysql> select "aXbc" REGEXP "^ [^ a-dXYZ] + $";-> 0 (indicating no matching)
Mysql> select "gheis" REGEXP "^ [^ a-dXYZ] + $";-> 1 (indicating matching)
Mysql> select "gheisa" REGEXP "^ [^ a-dXYZ] + $";-> 0 (indicating mismatch)
------------------------------------------------------------
[[. Characters.]
Indicates the order of comparison elements. The character sequence in parentheses is unique. However, parentheses can contain wildcards, so they can match more characters. For example, the regular expression [[. ch.] * c matches the first five characters of chchcc.
[= Character_class =]
It indicates an equal class, which can replace other equal elements in the class, including its own. For example, if o and (+) are members of an equal class, [[= o =], [[= (+) =] and [o (+)] are completely equivalent.
[: Character_class:]
In parentheses, the name of the character class is in the middle of [: And:], which can represent all characters of the class.
The character classes are named alnum, digit, punct, alpha, graph, space, blank, lower, upper, cntrl, print, and xdigit.
Mysql> select "justalnums" REGEXP "[[: alnum:] +";-> 1 (indicating matching)
Mysql> select "!! "REGEXP" [[: alnum:] + ";-> 0 (not matching)
[[:]
Matches an empty string at the beginning and end of a word. the start and end of the word are neither characters contained in alnum nor underlines.
Mysql> select "a word a" REGEXP "[[:]";-> 1 (indicating matching)
Mysql> select "a xword a" REGEXP "[[:]";-> 0 (indicating mismatch)
Mysql> select "weeknights" REGEXP "^ (wee | week) (knights | nights) $";-> 1 (match)
