在业务开发中,配查匹配配经常有需要模糊匹配某个字段的询标需求。比如按某个名字匹配,准S则模但用户可能只记得部分字,和正没记住全名,浅析如果能支持模糊匹配那用户体验就会好很多。模模式
MySQL 提供了标准SQL模式匹配以及一种基于扩展正则表达式的式匹式匹模式匹配,类似于 Unix 实用程序(如 vi、配查匹配配grep和 sed )使用的询标那些。
SQL模式匹配使您能够用"_"匹配任何单个字符,准S则模使用"%"匹配任意数量的和正字符(包括零个字符)。
在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$';
+--------+--------+---------+------+------------+-------+
| 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 '^.....$';
+-------+--------+---------+------+------------+-------+
| 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}$';
+-------+--------+---------+------+------------+-------+
| 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 |
+-----------------------+
解决办法:
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.
(责任编辑:热点)
ST步森(002569.SZ)公布消息:终止收购微动天下100%的股权
DJI Mic 2无线麦克风发布:适配DJI OSMO设备,可蓝牙直连手机
三季度基金代销机构公募基金保有规模前100强名单 银行C位不变
《数字安全免疫力建设指南》发布,提出企业安全4大核心6大模块建设路径
北交所开市在即!11月13日进行通关测试 首批星宿股达81家
最高法再次发声裁判文书网:加大上网力度,公布文书需隐去识别信息