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

SQL mode matching allows you to use"_"Match any single character, while"%"Matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. The following are some examples. Note that = or! =; Instead, use the LIKE or not like comparison 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 a name that exactly contains five characters, use"_"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. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).

Some Characters of the extended regular expression are:

· '.' Matches any single character.

· Character classes"[...]"Matches any character in square brackets. For example,"[Abc]"Match"","B"Or"C". To name the character range, use a hyphen (-)."[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.

• If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern 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"^"OrUse "$" at the end of the Pattern".

To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above:

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 force REGEXP to be case sensitive, use the BINARY keyword to convert a string to a BINARY string. This query only matches the lowercase 'B' of the first letter of the name '.

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 were using an SQL mode.

To find a name that contains exactly five characters, use"^"And"$"Match the start and end of a name with five"."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 }"The "Repeat n times" operator overrides 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  |

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