Detailed description of MySQL Regular Expression REGEXP

Before starting this topic, we should first conduct a small experiment to compare the high efficiency of REGEXP and Like. 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 ");
// Function: Timing Function
// 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 response results are as follows: 2300 ms to 2330 ms
For ($ I = 0; I I <10000; $ I ++ ){
$ Rs = $ Mysql-> Get ("SELECT * FROM 'Hotel 'where address REGEXP' ^ Lijiang region | Taoyuan City '");
}

// The following response results are as follows: 2300 ms to 2330 ms
For ($ I = 0; I I <10000; $ I ++ ){
$ Rs = $ Mysql-> Get ("SELECT * FROM 'Hotel 'where address like 'lijiang River region %' or address like '% Taoyuan city % '");
}

Echo Runtime (1 );
The results of the experiment are surprising, that is, the efficiency of the two of them is almost the same, and the ups and downs are less than dozens of milliseconds, so we need to study REGEXP in depth, because it can greatly shorten the length of SQL statements. for a long query condition, this query can shorten the SQL length.If you want to publish this article, please indicate the location, without the right to publish the copyright, I do not like to see that kind of website my work does not indicate the person QQ9256114
Regular Expressions are a powerful way to specify patterns for complex searches.

MySQL adopts the Regular Expression of Henry Spencer, and its goal is to comply with POSIX
1003.2. See Appendix C: Thank you. MySQL adopts an extended version to support the pattern matching operation 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 provide some examples. This appendix is not available in Henry
All the details found on Spencer's regex (7) manual page. This manual page is included in the regex.7 file in the regex directory of the MySQL source code distribution edition.

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

Extraordinary Regular Expressions use special structures so that they can match more than one string. 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 that starts with B, ends with s, and contains any number of a or n characters. If you want to publish this article, please indicate the location, without the right to publish the copyright, I do not like to see that kind of website my work does not indicate the person QQ9256114

For the REGEXP operator, regular expressions can use any of the following special characters and structures:

·
^

Match the start part of the string.

Mysql>SELECT 'fo \ nfo 'regexp '^ fo $ ';-> 0 mysql>SELECT 'fofofo' REGEXP '^ fo ';-> 1

·
$

Matches the end part of the string.

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

·
.

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

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

·
A *

Matches any sequence of 0 or multiple a characters.

Mysql>SELECT 'Ban 'regexp' ^ Ba * n ';-> 1. mysql>SELECT 'baa' REGEXP '^ Ba * n ';-> 1. mysql>SELECT 'bn 'regexp' ^ Ba * n ';-> 1

·
A +

Matches any sequence of one or more a characters.

Mysql>SELECT 'Ban 'regexp' ^ Ba + N ';-> 1. mysql>SELECT 'bn 'regexp' ^ Ba + N ';-> 0

·
A?

Matches 0 or 1 a character.

Mysql>SELECT 'bn 'regexp' ^ Ba? N ';-> 1. mysql>SELECT 'Ban 'regexp' ^ Ba? N ';-> 1. mysql>SELECT 'baance' REGEXP '^ Ba? N ';-> 0

·
De | abc

Match the sequence de or abc.

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

·
(Abc )*

Matches 0 or multiple instances of the sequence abc.

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

·
{1 },
{2, 3}

The {n} or {m, n} symbol provides a more common way to write regular expressions and can match many of the aforementioned atoms (or "parts") 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 accurately, a {n} exactly matches n instances of. A {n,} matches n or more instances of. A {m, n} matches m ~ of ~ N instances, including m and n.

M and n must be between 0 and ~ The range of RE_DUP_MAX (255 by default) includes 0 and RE_DUP_MAX. If both m and n are given, m must be less than or equal to n.

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

·
[A-dX],
[^ A-dX]

Match any character that is (or is not, if ^ is used) a, B, c, d, or X. The "-" character between two other characters constitutes a range, and matches all characters starting from 1st characters to 2nd characters. For example, [0-9] matches any decimal number
. To include the text character "]", it must be followed by the brackets. To contain the text character "-", it must be written first or last. For any character that does not define any special meaning for [], it only matches itself.

Mysql>SELECT 'axbc' REGEXP '[a-dXYZ]';-> 1. mysql>SELECT 'axbc' REGEXP '^ [a-dXYZ] $ ';-> 0 mysql>SELECT 'axbc' REGEXP '^ [a-dXYZ] + $ ';-> 1. mysql>SELECT 'axbc' REGEXP '^ [^ a-dXYZ] + $ ';-> 0 mysql>SELECT 'gheis 'regexp' ^ [^ a-dXYZ] + $ ';-> 1. mysql>SELECT 'gheisa 'regexp' ^ [^ a-dXYZ] + $ ';-> 0

·
[. Characters.]

In the brackets expression (using [and]), match the character sequence used for checking the element. The character is a single character or a new line or another character name. In the regexp/cname. h file, you can find the complete list of character names.

Mysql>SELECT '~ 'Regexp' [[...] ';-> 1. mysql>SELECT '~ 'Regexp' [[. tilde.] ';-> 1

·
[= Character_class =]

In the brackets (using [and]), [= character_class =] indicates the same type. It matches all characters with the same value, including itself. For example, if both o and (+) are similar members, [[= o =], [[= (+) =], and [o (+)] are synonyms. And so on.

·
[: Character_class:]

In a bracket expression (using [and]), [: character_class:] indicates the character class that matches all characters in the term class. The standard class name is:

Alnum	Character
Alpha	Character
Blank	White space characters
Cntrl	Control characters
Digit	Numeric characters
Graph	Graphical characters
Lower	Lowercase characters
Print	Graphical or space characters
Punct	Punctuation
Space	Space, tab, new line, and carriage return
Upper	Uppercase characters
Xdigit	Hexadecimal numeric characters

They represent character classes defined on the ctype (3) manual page. Other class names may be provided for specific regions. Character classes cannot be used as endpoints of a range.

Mysql>SELECT 'justnums' REGEXP '[[: alnum:] + ';-> 1. mysql>SELECT '!! 'Regexp' [[: alnum:] + ';-> 0

·
[[: <:],
[[:>:]

These tags indicate word boundaries. They match the start and end of word respectively. Word is a series of character characters, with no character at the front and back. The character is a letter, digit, or underscore (_) in the alnum class (_).

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

To use a text instance with special characters in a regular expression, add two backslash (\) characters before it. The MySQL parser is responsible for interpreting one of them, and the regular expression library is responsible for interpreting the other. For example, to match the string "1 + 2" that contains the special character "+", in the following regular expression, only the last one is correct:

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