MySQL Official manual Learn notes 2 MySQL fuzzy query and regular expression _mysql

SQL pattern matching allows you to use“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。下面给出一些例子。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

Want to find out“b”开头的名字：

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 |

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

Want to find out“fy”结尾的名字：

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 out which contains“w”的名字：

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 contains exactly 5 characters, use “_”模式字符： the

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 |

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

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you match this pattern to a test, 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 class [...] matches any character within the square brackets. For example, " [ABC] " matches " a ", " b " or " " c . To name a range of characters, use a "-". " [A-z] " matches any letter, while " [0-9] " matches any number.

·          “ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。

• If the regexp pattern matches anywhere that is tested, the pattern matches (unlike like pattern matching, which matches only the entire value ).

• In order to locate a pattern so that it must match the start or end of the tested value, use at the beginning of the pattern“^”或在模式的结尾用“$”。

To illustrate how an extended regular expression works, use RegExp to rewrite the like query shown above :

In order to find out“b”开头的名字，使用“^”匹配名字的开始：

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 the regexp comparison to be case-sensitive , use the binary keyword to make one of the strings into a binary string. The query matches only the lowercase ' b ' of the first letter of the name.

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

In order to find out“fy”结尾的名字，使用“$”匹配名字的结尾：

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 |

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

In order to find the inclusion of a“w”的名字，使用以下查询：

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 and its pattern matches, there is no need to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value, just as you would use an SQL schema.

To find a name that contains exactly 5 characters, use “^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间： the

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}”“重复n次”操作符重写前面的查询：

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 |

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