MySql official manual Study Notes 2 MySql fuzzy query and Regular Expression

SQLPattern Matching allows you to use"_"Match any single character, while"%"Match any number of characters(Including zero characters). InMySQLMedium,SQLIs case-insensitive by default. The following are some examples. NoteSQLMode, cannot use=Or! =Instead, useLIKEOrNOT LIKEComparison operator.

To find"B"Name starting:

 

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

To find"Fy"End Name:

 

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

+--------+--------+---------+------+------------+-------+

To find"W"Name:

 

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

To find out exactly include5Characters in length."_"Mode character:

 

mysql> SELECT * FROM pet WHERE name LIKE '_____';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

ByMySQLOther types of pattern matching are extended regular expressions. UseREGEXPAndNOT REGEXPOperator(OrRLIKEAndNOT RLIKE, They are synonyms).

Some Characters of the extended regular expression are:

·'.'Match any single character.

·Character class"[...]"Matches any character in square brackets. For example,"[Abc]"Match"A","B"Or"C". To name the character range, use a"-"."[A-z]"Match any letter, while"[0-9]"Match any number.

·"*"Matches zero or multiple characters before it. For example,"X *"Match any number"X"Character,"[0-9] *"Match any number of numbers, while".*"Matches any number of characters.

• IfREGEXPThe pattern matches any part of the tested value, and the pattern matches(This is different fromLIKEPattern match. The pattern matches only the entire value.).

• To locate a pattern so that it must match the start or end of the tested value, use"^"OrAt the end of the Pattern"$".

To demonstrate how the extended regular expression works, we useREGEXPRewriteLIKEQuery:

To find"B"Name starting"^"Start of matching name:

 

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

If you want to forceREGEXPCase sensitive.BINARYKeyword to convert a string to a binary string. This query matches only the lowercase letters of the first letter of the name'B'.

 

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

To find"Fy"The end name."$"End of matched Name:

 

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

+--------+--------+---------+------+------------+-------+

To find"W"Use the following query:

 

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

Since a regular expression appears anywhere in the value, its pattern matches, you do not have to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as if you usedSQLMode.

To find out5Characters in length."^"And"$"Match the start and end of the name, and5Items"."There are two instances:

 

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

You can also use"{N}""RepeatN"Times" operator overwrites the previous query:

 

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+