当前位置： 当前位置：首页 >IT科技类资讯 >浅析MySQL模式匹配查询（标准SQL模式匹配和正则模式匹配）正文

在业务开发中，浅析经常有需要模糊匹配某个字段的模模式需求。比如按某个名字匹配，式匹式匹但用户可能只记得部分字，配查匹配配没记住全名，询标如果能支持模糊匹配那用户体验就会好很多。准S则模

MySQL 提供了标准SQL模式匹配以及一种基于扩展正则表达式的和正模式匹配，类似于 Unix 实用程序（如 vi、浅析grep和 sed ）使用的模模式那些。

SQL模式匹配

SQL模式匹配使您能够用"_"匹配任何单个字符，式匹式匹使用"%"匹配任意数量的配查匹配配字符（包括零个字符）。

在MySQL中，询标SQL模式默认不区分大小写。准S则模不要在使用SQL模式时使用=或<>，和正请改用LIKE或者NOT LIKE运算符。浅析

要查找以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 |+--------+--------+---------+------+------------+------------+1.2.3.4.5.6.7.

查找以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 |+--------+--------+---------+------+------------+-------+1.2.3.4.5.6.7.

要查找包含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 |+----------+-------+---------+------+------------+------------+1.2.3.4.5.6.7.8.

要查找恰好包含五个字符的名称，请使用模式字符"_"：

复制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 |+-------+--------+---------+------+------------+-------+1.2.3.4.5.6.7. 正则模式匹配

在通常情况下，上述功能已经可以满足大部分业务的开发需求。但偶尔也会遇到一些更复杂的查询匹配需求，可能需要正则才能满足。MySQL 提供的另一种模式匹配使用扩展的正则表达式。当您测试这种类型的IT技术网模式是否匹配时，请使用REGEXP和NOT REGEXP运算符（或 RLIKE与NOT RLIKE，它们是同义词）。

个人比较偏爱RLIKE、NOT RLIKE，与LIKE、NOT LIKE类似，好记。

. 匹配任何单个字符。字符类 [...] 匹配括号内的任何字符。例如， [abc]匹配a、 b或c。要命名一系列字符，请使用破折号，[a-z] 匹配任何字母，而[0-9] 匹配任何数字。* 匹配它前面的事物的零个或多个实例。例如，x* 匹配任意数量的x字符、 [0-9]*匹配任意数量的数字以及.*匹配任意数量的任何内容。如果模式匹配正在测试的值中的任何位置，则正则表达式模式匹配成功。云服务器（这与LIKE模式匹配不同，LIKE模式匹配只有在模式匹配整个值时才会成功。）要锚定一个模式，使其必须匹配被测试值^的开头或$结尾，请在模式的开头或结尾使用。

为了演示扩展正则表达式是如何工作的，LIKE前面显示的查询在这里用正则重写。

要查找以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 |+--------+--------+---------+------+------------+------------+1.2.3.4.5.6.7.

要强制REGEXP比较区分大小写，请使用BINARY关键字使其中一个字符串成为二进制字符串。此查询仅匹配b名称开头的小写字母：

复制SELECT * FROM pet WHERE name REGEXP BINARY ^b;1.

要查找以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 |+--------+--------+---------+------+------------+-------+1.2.3.4.5.6.7.

要查找包含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 |+----------+-------+---------+------+------------+------------+1.2.3.4.5.6.7.8.

因为正则表达式模式会匹配出现在值的任何位置，因此在前面的查询中，没有必要像SQL 模式那样在模式的任一侧放置通配符以使其匹配整个值。

要查找恰好包含五个字符的名称，使用^、$、亿华云. 来匹配名称的开头和结尾：

复制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 |+-------+--------+---------+------+------------+-------+1.2.3.4.5.6.7.

还可以使用{n} (“repeat-n-times”)运算符：

复制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 |+-------+--------+---------+------+------------+-------+1.2.3.4.5.6.7. 注意事项两种模式匹配区别

Like匹配原则是要求模式串与整个目标字段匹配时，才返回该条记录。

正则匹配则是当目标字段包含模式串时即返回该条记录。

模式匹配可能无法走索引

InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效，正则也类似。

最好能结合其他索引字段一起查询，这样效率比较高。

如果数据比较多，建议用全文索引。

正则匹配注意特殊字符

正则匹配功能强大，但使用时一定需要注意特殊字符的干扰，尤其是在mysql中查中括号或者小括号，一定要转义：

复制mysql> SELECT REGEXP_LIKE((, (

);

ERROR 3692 (HY000): Mismatched parenthesis in regular expression

.

mysql> SELECT REGEXP_LIKE((, \\(

);

+-------------------------+| REGEXP_LIKE((, \\() |+-------------------------+| 1 |+-------------------------+mysql> SELECT REGEXP_LIKE(), )

);

ERROR 3692 (HY000): Mismatched parenthesis in regular expression

.

mysql> SELECT REGEXP_LIKE(), \\)

);

+-------------------------+| REGEXP_LIKE(), \\)) |+-------------------------+| 1 |+-------------------------+mysql> SELECT REGEXP_LIKE([, [

);

ERROR 3696 (HY000): The regular expression contains anunclosed bracket expression

.

mysql> SELECT REGEXP_LIKE([, \\[

);

+-------------------------+| REGEXP_LIKE([, \\[) |+-------------------------+| 1 |+-------------------------+mysql> SELECT REGEXP_LIKE(], ]

);

+-----------------------+| REGEXP_LIKE(], ]) |+-----------------------+| 1 |+-----------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.

解决办法：

限制使用的字符范围，排除元字符。查询之前先转义，例如golang中，使用func QuoteMeta(s string) string 复制QuoteMeta returns a string that escapes all regular expression metacharacters inside the argument text; the returned string is a regular expression matching the literal text.1.