MySQL模式匹配查询（标准SQL模式匹配和正则模式匹配）
发布时间：2023-02-08 12:54:41 所属栏目：MySql教程 来源：
导读： 在业务开发中，经常有需要模糊匹配某个字段的需求。比如按某个名字匹配，但用户可能只记得部分字，没记住全名，如果能支持模糊匹配那用户体验就会好很多。
MySQL 提供了标准SQL模式匹配以及一种基于扩展
MySQL 提供了标准SQL模式匹配以及一种基于扩展
|
在业务开发中，经常有需要模糊匹配某个字段的需求。比如按某个名字匹配，但用户可能只记得部分字，没记住全名，如果能支持模糊匹配那用户体验就会好很多。 MySQL 提供了标准SQL模式匹配以及一种基于扩展正则表达式的模式匹配，类似于 Unix 实用程序（如 vi、grep和 sed ）使用的那些。 SQL模式匹配 SQL模式匹配使您能够用"_"匹配任何单个字符，使用"%"匹配任意数量的字符（包括零个字符）。 在MySQL中，SQL模式默认不区分大小写。不要在使用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 | +--------+--------+---------+------+------------+------------+ 查找以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 | +--------+--------+---------+------+------------+-------+ 要查找包含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 | +----------+-------+---------+------+------------+------------+ 要查找恰好包含五个字符的名称，请使用模式字符"_"： 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 | +-------+--------+---------+------+------------+-------+ 正则模式匹配 在通常情况下，上述功能已经可以满足大部分业务的开发需求。但偶尔也会遇到一些更复杂的查询匹配需求，可能需要正则才能满足。MySQL 提供的另一种模式匹配使用扩展的正则表达式。当您测试这种类型的模式是否匹配时，请使用REGEXP和NOT REGEXP运算符（或 RLIKE与NOT RLIKE，它们是同义词）。 个人比较偏爱RLIKE、NOT RLIKE，与LIKE、NOT 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 | +--------+--------+---------+------+------------+------------+ 要强制REGEXP比较区分大小写，请使用BINARY关键字使其中一个字符串成为二进制字符串。此查询仅匹配b名称开头的小写字母： SELECT * FROM pet WHERE name REGEXP BINARY '^b'; 要查找以fy结尾的名称，使用$匹配名称的结尾： mysql> SELECT * FROM pet WHERE name REGEXP 'fy#39;; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 要查找包含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 | +----------+-------+---------+------+------------+------------+ 因为正则表达式模式会匹配出现在值的任何位置，因此在前面的查询中，没有必要像SQL 模式那样在模式的任一侧放置通配符以使其匹配整个值。 要查找恰好包含五个字符的名称，使用^、$、. 来匹配名称的开头和结尾： mysql> SELECT * FROM pet WHERE name REGEXP '^.....#39;; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 还可以使用{n} (“repeat-n-times”)运算符： mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}#39;; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 注意事项 两种模式匹配区别 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 an unclosed bracket expression. mysql> SELECT REGEXP_LIKE('[', '\\['); +-------------------------+ | REGEXP_LIKE('[', '\\[') | +-------------------------+ | 1 | +-------------------------+ mysql> SELECT REGEXP_LIKE(']', ']'); +-----------------------+ | REGEXP_LIKE(']', ']') | +-----------------------+ | 1 | +-----------------------+ 解决办法： 限制使用的字符范围，排除元字符。查询之前先转义，例如golang中mysql通配符使用，使用func QuoteMeta(s string) string QuoteMeta returns a string that escapes all regular expression metacharacters inside the argument （编辑：百客网 - 百科网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐