Home>

In the past, I used like to find data. I later found that there are regular expressions in mysql and I feel that the performance is better than like. Let me share a detailed explanation of the use of mysql regexp regular expressions.Hope this method is helpful to everyone.

MySQL uses henry spencer's regular expression implementation,The goal is to comply with posix 1003.2. See Appendix c:Thanks. mysql uses an extended version,To support pattern matching operations used with the regexp operator in SQL statements.See Section 3.3.4.7, "Pattern Matching".

In this appendix,Summarizes the special characters and structures that can be used for regexp operations in mysql,Some examples are given.This appendix does not contain all the details that can be found in the regex (7) manual page of Henry Spencer.The manual page is included in the mysql source distribution.Located in the regex.7 file under the regex directory.

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.

Non-trivial regular expressions use special specific structures,So that they can match more than one string.For example, the regular expression hello | word matches the string hello or the 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.

Following are the patterns that can be used for tables with the regexp operator.

Application examples,Find user records with incorrect email format in the user table:

select *
from users
where email not regexp "^ [a-z0-9 ._%-] [email protected][a-z0-9 .-] +. [a-z] {2,4} $"

The syntax of regular expressions in the mysql database,Including the meaning of various symbols.

(^) Character

Match the beginning of the string,For example, "^ a" represents a string starting with the letter a.

mysql>select "xxxyyy" regexp "^ xx";
+ ----------------------- +
| "xxxyyy" regexp "^ xx" |
+ ----------------------- +
| 1 |
+ ----------------------- +
1 row in set (0.00 sec)

Query whether the xxxyyy string starts with xx. The result value is 1, which means the value is true, and the condition is met.

($) Character

Match the end of the string,For example, "x ^" represents a character string ending with the letter x.

(.)character

This character is the dot in English,It matches any one character,Including carriage returns, line feeds, etc.

(*)character

The asterisk matches zero or more characters.There must be content before it.Such as:

mysql>select "xxxyyy" regexp "x *";

In this SQL statement, the regular match is true.

(+) Character

The plus sign matches 1 or more characters.There must also be content before it.The plus sign is similar to the asterisk,Just the asterisk is allowed to appear 0 times, the plus sign must appear at least once.

(?) character

The question mark matches 0 or 1 times.

Example:

Now according to the table above,Various types of SQL queries can be installed to meet requirements.List some understanding here.Consider we have a table for person_tbl and have a field named name:

The query finds all names that begin with "st"

mysql>select name from person_tbl where name regexp "^ st";

The query finds all names ending in "ok"

mysql>select name from person_tbl where name regexp "ok $";

Query finds all strings with the name "mar"

mysql>select name from person_tbl where name regexp "mar";

The query finds all names that start with a vowel and end with "ok"

mysql>select name from person_tbl where name regexp "^ [aeiou] | ok $";

The following reserved words can be used in a regular expression

^

The matched string starts with the following string

mysql>select "fonfo" regexp "^ fo $";->0 (meaning no match)
mysql>select "fofo" regexp "^ fo";->1 (meaning match)

$

The matched string ends with the previous string

mysql>select "fono" regexp "^ fono $";->1 (meaning match)
mysql>select "fono" regexp "^ fo $";->0 (meaning no match)
.

Matches any character (including new lines)

mysql>select "fofo" regexp "^ f. *";->1 (meaning match)
mysql>select "fonfo" regexp "^ f. *";->1 (meaning match)

a *

Match any number of a (including empty string)

mysql>select "ban" regexp "^ ba * n";->1 (meaning match)
mysql>select "baaan" regexp "^ ba * n";->1 (meaning match)
mysql>select "bn" regexp "^ ba * n";->1 (meaning match)

a +

Matches any number of a (excluding the empty string)

mysql>select "ban" regexp "^ ba + n";->1 (meaning match)
mysql>select "bn" regexp "^ ba + n";->0 (meaning no match)

a?

Match one or zero a

mysql>select "bn" regexp "^ ba?n";->1 (indicating a match)
mysql>select "ban" regexp "^ ba?n";->1 (indicating a match)
mysql>select "baan" regexp "^ ba?n";->0 (indicating no match)

de | abc

Match de or abc

mysql>select "pi" regexp "pi | apa";->1 (meaning match)
mysql>select "axe" regexp "pi | apa";->0 (meaning no match)
mysql>select "apa" regexp "pi | apa";->1 (meaning match)
mysql>select "apa" regexp "^ (pi | apa) $";->1 (meaning match)
mysql>select "pi" regexp "^ (pi | apa) $";->1 (meaning match)
mysql>select "pix" regexp "^ (pi | apa) $";->0 (indicating no match)

(abc) *

Match any number of abc (including empty string)

mysql>select "pi" regexp "^ (pi) * $";->1 (meaning match)
mysql>select "pip" regexp "^ (pi) * $";->0 (indicating no match)
mysql>select "pipi" regexp "^ (pi) * $";->1 (meaning match)

{1}

{2,3}

This is a more comprehensive approach,It can implement the functions of 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 {}, which means that the character can only appear i times;there is an integer parameter i in {}, followed by a ",", which means that the character can appear i times or more than i times;in { } Has only one integer parameter i, followed by a "," followed by an integer parameter j, which means that the character can only appear more than i times and 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 is 255). If there are two parameters,The second must be greater than or equal to the first

[a-dx]

Matches "a", "b", "c", "d", or "x"

[^ a-dx]

Matches any character except "a", "b", "c", "d", "x".

"[", "]" Must be used in pairs

mysql>select "axbc" regexp "[a-dxyz]";->1 (indicating a match)
mysql>select "axbc" regexp "^ [a-dxyz] $";->0 (indicating no match)
mysql>select "axbc" regexp "^ [a-dxyz] + $";->1 (meaning match)
mysql>select "axbc" regexp "^ [^ a-dxyz] + $";->0 (indicating no match)
mysql>select "gheis" regexp "^ [^ a-dxyz] + $";->1 (meaning match)
mysql>select "gheisa" regexp "^ [^ a-dxyz] + $";->0 (indicating no match)
  • Previous Summary of PHP code optimization tips
  • Next JQuery to achieve left and right scroll menu special effects