PHPmysqli隐约查询技巧_你知道什么是 MySQL 的模糊查询

责编 | 郭芮

本文对MySQL中几种常用的模糊搜索办法进行了先容，包括LIKE通配符、RegExp正则匹配、内置字符串函数以及全文索引，末了给出了性能比拟。

弁言

MySQL根据不同的运用处景，支持的模糊搜索办法有多种，例如运用最广泛的可能是Like匹配和RegExp正则匹配，二者虽然用法和事理都很相似，但实际上匹配原则却不尽相同，个中Like哀求模式串与全体目标字段完备匹配才检索该记录，而RegExp则是哀求目标字段包含模式串即可。

对付大略的判断模式串是否存在类型的模糊搜索，运用MySQL内置函数即可实现，例如Instr、Locate、Position等。

当然，提到MySQL查询性能就不得不提到索引，对付字段模糊查询需求，我们也可以考虑添加全文索引(Fulltext)。

注：本文所用MySQL版本8.0.19，可视化工具Navicat Primium。

4种模糊查询

为了便于描述和测试不同模糊查询办法结果，首先给出一个大略的测试用数据表tests如下：

个中，tests表仅含有一个名为words的字段，并对该字段添加全文索引。
表中共有6条记录。

Like

Like算作MySQL中的谓词，其运用与is、=、>和<等符号用法类似。
Like紧张支持两种通配符，分别是\"大众_\"大众和\"大众%\公众，个中前者代表匹配1个任意字符，常用于充当占位符；而后者代表匹配0个或多个任意字符。
从某种意义上讲，Like可看作是一个精简的正则表达式功能。

例如，在如上表中查找所有以\"大众hello\"大众开头的记录，则其SQL语句为：

1SELECT words FROM tests WHERE words LIKE 'hello%';

查询结果：

如果想查找所有以\"大众hello\"大众开头且至少含有6个字符的记录，则可大略修正SQL语句如下：

1SELECT words FROM tests WHERE words LIKE 'hello_%';

查询结果：

其余：当在Like模式字段中，若不包含任何\公众_\"大众和\"大众%\"大众通配符，则等价于\公众=\公众，表示精确匹配，例如查询语句……Like \"大众hello\公众，则仅返回hello一条记录；还可在Like前加限定词Not，表示结果取反。

RegExp

正则表达式具有弘大而丰富的语法，MySQL语法中支持绝大部分正则表达式功能，险些可以知足所有需求。
本文不过多展开正则表达式干系先容，仅在Like的根本上，大略先容其与Like模糊搜索办法的差异。

如前所述，Like匹配原则是哀求模式串与全体目标字段匹配时，才返回该条记录；而RegExp中则是当目标字段包含模式串时即返回该条记录。
例如如下SQL语句将返回所有包含\"大众hello\"大众的记录：

1SELECT words FROM tests WHERE words REGEXP 'hello';

而在Like中这样的写法仅返回记录=\"大众hello\"大众的记录。
为了限定正则表达式以某个模式串开头或者结尾，可以通过添加\"大众^\公众和\"大众$\"大众标识符来限定，例如仍旧搜索以\"大众hello\公众开头的目标字段，则其SQL语句为：

1SELECT words FROM tests WHERE words REGEXP '^hello';

内置函数

对付包含某些特定模式串的模糊搜索，可以通过MySQL内置函数实现。
可以完成这一功能的函数包括Instr、Locate和Position等，其功能语法很附近，均是返回子串在字符串中的索引，且索引下标从1开始，当子串不存在是返回0。
须要把稳的是三个函数中子串和字符串的先后顺序是不一致的。
例如以下语句均成功检索，且返回目标索引1

1SELECT INSTR(\公众hello,world\公众, 'hello');-- 12SELECT LOCATE('hello', \"大众hello,world\"大众);-- 13SELECT POSITION('hello' in \"大众hello, world\"大众); -- 1

运用以上3个内置函数，搜索上述测试表中包含\"大众hello\"大众的记录，则相应SQL语句为：

1SELECT words FROM tests WHERE INSTR(words, 'hello');2SELECT words FROM tests WHERE LOCATE('hello', words);3SELECT words FROM tests WHERE POSITION('hello' in words);

全文索引

抛开索引谈查询性能，都是耍泼皮！

全文索引是MySQL中索引的一种，曾经仅在引擎为MyISAM的表中支持，从5.6版本开始在InnoDB中也开始支持全文索引，支持的字段格式包括CHAR、VARCHAR和TEXT。
在如上已经添加了全文索引的tests表中，仍旧查询包含\"大众hello\"大众的记录，运用全文索引查询的SQL语句为：

1SELECT words FROM tests WHERE MATCH(words) against('hello');

实际上，MATCH(words) against('hello')返回的是字段words对目标字符\公众hello\公众的匹配程度：当不存在任何匹配结果时，返回0；否则，根据匹配次数的多少和位置先后返回一个匹配度。
例如，如下SQL语句返回表中每条记录对目标字段\公众hello\"大众的匹配度：

1SELECT MATCH(words) against('hello') FROM tests;

返回结果如下：

查询性能比拟

为了比拟以上4种模糊搜索办法的性能，我们这里构建一个规模较大且更具一样平常性的数据表。
本文选择采集多少条英文格言，用于创建目标数据库。

创建数据表

为大略起见，仅创建一个名为says的字段，且对其添加全文索引。

1CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says));

英文格言信息获取

在网上找了个英文格言的网站，并写了一个python小爬虫爬取页面全部300条英文格言，爬虫源码如下（为了增加记录条数，将300条记录重写100词，即数据库中包含30000条记录）：

1from pyquery import PyQuery as pq 2from pymysql import connect 3 4doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030') 5items=doc(\"大众div.content>p:nth-child(2n+1)\公众).items 6hots = [item.text() for item in items] 7with connect(host=\"大众localhost\公众, user=\"大众root\公众, password=\公众123456\公众, db='teststr', charset='utf8') as cur: 8 sql_insert = 'insert into sayings values (%s);' 9 for _ in range(100):10 cur.executemany(sql_insert, hots)

对爬取的英文短句写入创建的数据表中，结果如下：

既然是英文励志格言短句，那么我们就来查询个中包括\公众success\"大众的记录。

首先查询语句中任意位置包含\"大众success\公众的记录，4种办法SQL语句及实行韶光为：

1-- LIKE通配符 2SELECT says FROM sayings WHERE says LIKE '%success%' 3> OK 4> 韶光: 0.036s 5 6-- REGEXP正则匹配 7SELECT says FROM sayings WHERE says REGEXP 'success' 8> OK 9> 韶光: 0.053s1011-- 内置函数查找12SELECT says FROM sayings WHERE INSTR(says, 'success')13> OK14> 韶光: 0.045s1516SELECT says FROM sayings WHERE LOCATE('success', says)17> OK18> 韶光: 0.044s1920SELECT says FROM sayings WHERE POSITION('success' in says)21> OK22> 韶光: 0.047s2324-- 全文索引25SELECT says FROM sayings WHERE MATCH(says) against('Success')26> OK27> 韶光: 0.006s

可见，全文索引速率最宽，领先其他办法靠近一个量级；Like通配符速率其次，但与其他几种查询办法效率相差不大。

通过Explain查询操持，我们可以创造全文索引办法由于运用了索引而无需全表查询，以是实行速率快，而其他三种模糊查询办法均为实行全表查询。

全文索引查询操持

Like通配符查询操持

实际上，对付添加索引的字段运用Like查询时，可以运用索引加速查询，为勒验证全文索引条件下是否仍旧可以运用索引，我们进行第二组性能测试：

查询语句中以\"大众success\公众开头的记录（全文索引办法不支持指定单词开头的查询任务），相应SQL语句即实行韶光如下：

1SELECT says FROM sayings WHERE says LIKE 'success%' 2> OK 3> 韶光: 0.015s 4 5SELECT says FROM sayings WHERE says REGEXP '^success' 6> OK 7> 韶光: 0.046s 8 9SELECT says FROM sayings WHERE INSTR(says, 'success')=110> OK11> 韶光: 0.042s1213SELECT says FROM sayings WHERE LOCATE('success', says)=114> OK15> 韶光: 0.051s1617SELECT says FROM sayings WHERE POSITION('success' in says)=118> OK19> 韶光: 0.049s2021SELECT says FROM sayings WHERE MATCH(says) against('Success')22> OK23> 韶光: 0.007s

可以看到，修正后的Like查询效率提升明显，并大幅超过其他办法。
但阐明查询操持创造，虽然possible_key显示了索引字段，但实际仍旧未运用任何索引（key为），即仍旧进行全表查询（Type = All）。
之以是带来速率上的大幅提升，仅仅是由于对'success%'要比'%success%'实行字符串匹配要快得多（后者要整列匹配，前者仅需匹配开头的单词即可），而与索引无关。

Like'success%'仍旧无法运用全文索引

以是，得到的结论是Like通配符无法有效利用全文索引加速查询，但在特定模式下的查询速率可快于通配符%模式下的查询。

总结

本文磋商了MySQL中4中模糊查询办法，包括：

Like通配符用于查询目标字段与模式串完备匹配的记录，且无法运用全文索引提高查询速率，但以特定字符开头的模糊查询比以\"大众%\"大众开头时速率提升明显；

RegExp正则表达式功能强大，可实现任意模式查询，但实行效率一样平常；

大略的子串有无查询还可运用MySQL内置函数，包括Instr、Locate和Position等，用法附近，但效率一样平常；

对付包含全文索引的目标字段查询，运用全文索引查询效率最高，但可定制性差，不支持任意匹配查询；

记录数目较少时，几种查询办法效率均可接管，可根据任务需求灵巧选用。

声明：本文为作者投稿，版权归其所有。

今日福利

遇见大咖

由 CSDN 全新专为技能人打造的高端对话栏目《大咖来了》来啦！

CSDN 创始人&董事长、极客帮创投创始合资人蒋涛携手京东集团技能副总裁、IEEE Fellow、京东人工智能研究院常务副院长、深度学习及语音和措辞实验室卖力人何晓冬，来也科技 CTO 胡一川，共话中国 AI 运用元年来了，开拓者及企业的路径及发展方向！