MySQL Regular expression regexp detailed

Before we start this topic we first do a small experiment, compare regexp and like them two which is high efficiency, if the efficiency is too low, we do not need to do too much research, the experiment code is as follows:
<?php
Require ("config.php");
Functions: Timing functions
Usage: Echo Runtime (1);
Function Runtime ($mode =0) {
Static $s;
IF (! $mode) {
$s =microtime ();
Return;
}
$e =microtime ();
$s =explode ("", $s);
$e =explode ("", $e);
Return Sprintf ("%.2f MS", ($e [1]+ $e [0]-$s [1]-$s [0]) *1000);
}

The following results are 2300 ms to 2330 MS
for ($i =0; $i <10000; $i + +) {
$Rs = $Mysql->get ("select * from ' Hotel ' where address REGEXP ' ^ River County | Taoyuan City '");
}

The following results are 2300 ms to 2330 MS
for ($i =0; $i <10000; $i + +) {
$Rs = $Mysql->get ("select * from ' Hotel ' where address like ' River County% ' or address like '% Peach Garden City ');
}

Echo Runtime (1);
The result of the experiment is very surprising, that is, their efficiency is almost the same, the fluctuation is not dozens of milliseconds, then we need to do in-depth study of RegExp, Because he can greatly shorten the length of the SQL statement. This query can be a good example of how to shorten SQL length for long query conditions.If you want to download this article, please note that it is free from the copyright colorful, I do not like to see the people who have uploaded my work and do not mention it QQ9256114
Regular expressions are a powerful way to specify patterns for complex searches.

MySQL is implemented with Henry Spencer's regular expression, which targets POSIX 1003.2. See Appendix C: Thank you. MySQL uses an extended version to support pattern matching operations that are used with the regexp operator in SQL statements. See section 3.3.4.7, "pattern matching".

In this appendix, we summarize the special characters and structures that can be used for regexp operations in MySQL, and give some examples. This appendix does not contain all the details that can be found on the Regex (7) manual page of Henry Spencer. This manual page is included in the MySQL source distribution, in the regex.7 file in the Regex directory.

A regular expression describes a set of strings. The simplest regular expression is a regular expression that does not contain any special characters. For example, the regular expression hello matches hello.

Non-trivial regular expressions use special specific structures that allow them to match more than 1 strings. For example, the regular expression Hello|word matches the string hello or string word.

As a more complex example, the regular expression b[an]*s matches any of the following strings: Bananas,baaaaas,bs, and any other string starting with B, ending with S, and containing any number of a or n characters. If you want to download this article, please note that it is free from the copyright colorful, I do not like to see the people who have uploaded my work and do not mention it QQ9256114

For the regexp operator, the regular expression can use any of the following special characters and structures:

· ^

Matches the starting part of the string.

mysql> SELECT ' fo\nfo ' REGEXP ' ^fo$ ';                      -0mysql> SELECT ' fofo ' REGEXP ' ^fo '; 1

· $

Matches the ending part of the string.

mysql> SELECT ' fo\no ' REGEXP ' ^fo\no$ ';                    -1mysql> SELECT ' fo\no ' REGEXP ' ^fo$ '; 0

· .

Matches any character (including carriage return and new line).

mysql> SELECT ' fofo ' REGEXP ' ^f.*$ ';                -1mysql> SELECT ' fo\r\nfo ' REGEXP ' ^f.*$ '; 1

· A *

Matches any sequence of 0 or more a characters.

mysql> SELECT ' Ban ' REGEXP ' ^ba*n ';                   -1mysql> SELECT ' Baaan ' REGEXP ' ^ba*n ';                      -1mysql> SELECT ' Bn ' REGEXP ' ^ba*n '; 1

· A +

Matches any sequence of 1 or more a characters.

mysql> SELECT ' Ban ' REGEXP ' ^ba+n ';                      -1mysql> SELECT ' Bn ' REGEXP ' ^ba+n '; 0

· A?

Match 0 or 1 a characters.

mysql> SELECT ' Bn ' REGEXP ' ^ba?n ';                     -1mysql> SELECT ' Ban ' REGEXP ' ^ba?n ';                    -1mysql> SELECT ' Baan ' REGEXP ' ^ba?n '; 0

· De|abc

Match the sequence de or ABC.

Mysql>SELECT ' pi ' REGEXP ' Pi|apa ';1mysql>SELECT ' axe ' REGEXP ' Pi|apa ';0mysql>SELECT ' apa ' REGEXP ' Pi|apa ';1mysql>SELECT ' apa ' REGEXP ' ^ (Pi|apa) $ ';1mysql>SELECT ' pi ' REGEXP ' ^ (Pi|apa) $ ';1mysql>SELECT ' pix ' REGEXP ' ^ (Pi|apa) $ ';0

· (ABC) *

Match 0 or more instances of a sequence ABC.

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

· {1}, {2,3}

The {n} or {m,n} symbols provide a more general way to write regular expressions that match many of the aforementioned atoms (or "portions") of the pattern. Both M and n are integers.

o A *

Can be written as a{0,}.

o A +

Can be written as A{1,}.

o A?

Can be written as a{0,1}.

More precisely, a{n} matches the n instances of a exactly. A{n,} matches N or more instances of a. A{m,n} matches the m~n instance of A, containing M and N.

M and n must be in the range of 0~re_dup_max (default 255) and contain 0 and Re_dup_max. If both M and n,m are given, they must be less than or equal to N.

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

· [A-DX], [^A-DX]

The

matches any character that is (or is not, if you use ^) A, B, C, D, or X. The "-" character between the two other characters forms a range that matches all characters from the beginning of the 1th character to the 2nd character. For example, [0-9] matches any decimal number. To include the literal character "]", it must be immediately after the opening parenthesis "[". To include the literal character "-", it must be written first or last. for [] Any character that does not have any special meaning defined internally, it only matches itself.

mysql>  SELECT ' axbc ' REGEXP ' [a-dxyz] ';                  1mysql >  SELECT ' axbc ' REGEXP ' ^[a-dxyz]$ ';                0mysql>  SELECT ' axbc ' REGEXP ' ^[a-dxyz]+$ ';               1mysql>  SELECT ' AxBC ' REGEXP ' ^[^a-dxyz]+$ ';              0mysql>  SELECT ' Gheis ' REGEXP ' ^[^a-dxyz]+$ ';             1mysql>  SELECT ' Gheisa ' REGEXP ' ^[^a-dxyz]+$ ';            0

· [. Characters.]

In the parentheses expression (using [and]), match the sequence of characters used to proofread the element. Characters are single characters or character names such as new lines. In file regexp/cname.h, you can find a complete list of character names.

mysql> SELECT ' ~ ' REGEXP ' [[. ~.]] ';                 1mysql> SELECT ' ~ ' REGEXP ' [[. Tilde.]] '; 1

· [=character_class=]

In the parentheses expression (using [and]), [=character_class=] represents the same type. It matches all characters that have the same collation value, including itself, for example, if O and (+) are members of the equivalent class, then [[[=o=]], [[= (+) =]], and [O (+)] are synonyms. The same class must not be used as a range endpoint.

· [: Character_class:]

In the parentheses expression (using [and]), [: Character_class:] represents the character class that matches all the characters of the term class. The standard class name is:

Alnum	Literal numeric characters
Alpha	Literal characters
Blank	White space characters
Cntrl	Control characters
Digit	numeric characters
Graph	Graphic characters
Lower	lowercase text characters
Print	Graphic or space character
Punct	Punctuation character
Space	Spaces, tabs, new lines, and carriage returns
Upper	Uppercase literal characters
Xdigit	hexadecimal numeric characters

They represent the character classes defined in the CType (3) manual page. Other class names may be available in specific regions. The character class must not be used as the endpoint of a range.

mysql> SELECT ' justalnums ' REGEXP ' [[: alnum:]]+ '; -1mysql> SELECT '! '               REGEXP ' [[: alnum:]]+ '; 0

· [[: <:]], [[:;:]]

These tokens represent word boundaries. They match the start and end of Word, respectively. Word is a series of character characters, preceded and followed by no word characters. Character characters are alphanumeric characters or underscores (_) in the Alnum class.

mysql> SELECT ' A word a ' REGEXP ' [[: <:]]word[[:>:]] ';  1mysql> SELECT ' A xword a ' REGEXP ' [[: <:]]word[[:>:]] '; 0

To use a literal instance of a special character in a regular expression, precede it with a 2 backslash "\" character. The MySQL parser is responsible for interpreting one, and the regular expression Library is responsible for interpreting the other. For example, to match the string "1+2" that contains the special character "+", only the last one is correct in the following regular expression:

mysql> SELECT ' 1+2 ' REGEXP ' 1+2 ';                      -0mysql> SELECT ' 1+2 ' REGEXP ' 1\+2 ';                     -0mysql> SELECT ' 1+2 ' REGEXP ' 1\\+2 '; 1

MySQL Regular expression regexp detailed