01介绍MySQL根据不同的应用场景，支持多种模糊搜索方式。比如Like匹配和RegExp正则匹配可能是应用最广泛的。类似，但实际上匹配原则不同。Like要求模式字符串与整个目标字段完全匹配才能检索记录，而RegExp只要求目标字段包含模式字符串。对于简单的判断模式字符串是否存在的模糊搜索，可以使用MySQL内置函数实现，如Instr()、Locate()、Position()等。当然，涉及到MySQL查询时性能，就不得不提到指标。对于字段的模糊查询需求，我们还可以考虑加入全文索引（Fulltext）。注：本文使用的MySQL版本为8.0.19，可视化工具NavicatPrimium。024种模糊查询为了方便描述和测试不同的模糊查询结果，给出一个简单的测试数据表tests如下：其中，tests表只包含一个名为words的字段，全文索引为添加到此字段。表中有6条记录。LikeLike在MySQL中算作谓词，其应用类似于is、=、>、<等符号的用法。Like主要支持两种通配符，分别是“_”和“%”。前者表示匹配1个任意字符，常用作占位符；而后者意味着匹配0个或多个任意字符。从某种意义上说，Like可以看作是一个精简的正则表达式函数。例如，要查找上表中所有以“hello”开头的记录，SQL语句为：1SELECTwordsFROMtestsWHEREwordsLIKE'hello%';查询结果：如果要查找所有以“hello”开头且至少包含6个字符的记录，则可以简单修改SQL语句如下：1SELECTwordsFROMtestsWHEREwordsLIKE'hello_%';查询结果：另外：当Like模式字段不包含任何“_”和“%”通配符时，相当于“=”，表示完全匹配，例如查询语句...like"hello",只会返回一条hello记录；限定符Not也可以加在Like之前，表示结果是否定的。RegExp正则表达式语法庞大丰富，MySQL语法支持大部分正则表达式功能，几乎可以满足所有需求。本文不展开对正则表达式的介绍，只简单介绍它与基于Like的模糊搜索方式的区别。前面说过，Like匹配的原则是只有pattern字符串匹配了整个目标字段才返回记录；而在RegExp中，当目标字段包含模式字符串时，将返回记录。例如，下面的SQL语句将返回所有包含“hello”的记录：1SELECTwordsFROMtestsWHEREwordsREGEXP'hello';而在Like中，只会返回带“hello”的记录。为了限制正则表达式以某种模式字符串开始或结束，可以添加“^”和“$”标识符来限制。例如，如果仍然搜索以“hello”开头的目标字段，则SQL语句为：1SELECTwordsFROMtestsWHEREwordsREGEXP'^hello';内置函数对于包含特定模式字符串的模糊搜索，您可以使用MySQL内置函数。可以完成这个功能的函数有Instr()、Locate()、Position()等，它们的函数语法非常相似。它们都返回子串在字符串中的索引，索引下标从1开始。当子串不存在时返回0。需要注意的是，三个函数中子串和字符串的顺序是不一致的。例如以下语句检索成功，返回目标索引11SELECTINSTR("hello,world",'hello');--12SELECTLOCATE('hello',"hello,world");--13SELECTPOSITION('hello'in"hello,world");--1应用上面的三个内置函数在上面的test表中查找包含"hello"的记录，则对应的SQL语句为：1SELECTwordsFROMtestsWHEREINSTR(words,'hello');2SELECTwordsFROMtestsWHERELOCATE（'你好'，单词）；3SELECTwordsFROMtestsWHEREPOSITION('你好'inwords);全文索引撇开索引谈查询性能，都是耍流氓！全文索引是MySQL中的一种索引。它曾经只在引擎为MyISAM的表中受支持。从5.6版本开始，InnoDB也支持全文索引。支持的字段格式包括CHAR、VARCHAR和TEXT。在上面添加了全文索引的tests表中，仍然查询到包含“hello”的记录。应用全文索引查询的SQL语句为：1SELECTwordsFROMtestsWHEREMATCH(words)against('hello');实际上，MATCH(words)against('hello')返回字段words与目标字符“hello”的匹配度：当没有匹配结果时，返回0；否则根据匹配次数和位置返回匹配度。例如，下面的SQL语句返回表中每条记录与目标字段“hello”的匹配度：1SELECTMATCH(words)against('hello')FROMtests;返回结果如下：03查询性能比较为了比较以上四种模糊搜索方法的性能，这里我们建立一个更大更通用的数据表。本文选择收集几句创建目标数据库的英文格言。创建数据表。为简单起见，只创建一个名为says的字段，并为其添加全文索引。1CREATETABLEIFNOTEXISTSsayings(saysTEXT,FULLTEXT(says));英文谚语信息的获取在网上找了一个英文谚语网站，写了一个python爬虫，把页面上的300条英文谚语全部抓取。爬虫源码如下（为了增加记录数，用100个词重写300条记录，即数据库有30000条记录）：1frompyqueryimportPyQueryaspq2frompymysqlimportconnect34doc=pq(url='http://www.1juzi.com/new/43141.html',encoding='gb18030')5items=doc("div.content>p:nth-child(2n+1)").items()6hots=[item.text()foriteminitems]7withconnect(host="localhost",user="root",password="123456",db='teststr',charset='utf8')ascur:8sql_insert='insertintosayingsvalues(%s);'9for_inrange(100):10cur.executemany(sql_insert,hots)注：如果你对pyquery爬虫的使用感兴趣，可以移步：使用pyquery5行代码爬取百度热点新闻文章，将爬取的英文词组写入创建的数据表中。结果如下：既然是英文励志格言的短句，那我们来查询一下其中包含“成功”的记录。首先查询语句中任意位置包含“success”的记录。SQL语句的4种方式和执行时间分别为：1--LIKE通配符2SELECTsaysFROMsayingsWHEREsaysLIKE'%success%'3>OK4>Time:0.036s56--REGEXP正则匹配7SELECTsaysFROMsayingsWHEREsaysREGEXP'success'8>OK9>Time:0.053s1011--Built-在函数搜索12SELECTsaysFROMsayingsWHEREINSTR(says,'success')13>OK14>Time:0.045s1516SELECTsaysFROMsayingsWHERELOCATE('success',says)17>OK18>Time:0.044s1920ccHELECTsaysFROMsaying(POSsaying'POS')21>OK22>Times:0.047-full-textindex25SELECTsaysFROMsayingsWHEREMATCH(says)against('Success')26>OK27>Time:0.006s可以看出，全文索引的速度是最宽的，领先了接近一个数量级其他方法；像通配符速度其次，但效率与其他查询方式相差不大。通过Explain查询计划可以发现，全文索引方式使用的是索引，不需要进行全表查询，所以执行速度快，而其他三种模糊查询方式都是进行全表查询。全文索引查询计划Like通配符查询计划实际上，当对加了索引的字段进行Like查询时，可以应用索引来加快查询速度。为了验证在全文索引条件下是否仍然可以应用索引，我们进行了第二组性能测试：words)，对应的SQL语句执行时间如下：1SELECTsaysFROMsayingsWHEREsaysLIKE'success%'2>OK3>Time:0.015s45SELECTsaysFROMsayingsWHEREsaysREGEXP'^success'6>OK7>Time:0.046s89SELECTsaysFROMsayingsWHEREINSTR(says,'success')=110>OK11>Time:0.042s1213SELECTsaysFROMsayingsWHERELOCATE('success',says)=114>OK15>Time:0.051s1617SELECTsaysFROMsayingsWHEREPOSITION=OK18>instants98)0.049s2021SELECTsaysFROMsayingsWHEREMATCH(says)against('Success2.0')22>OK修改后的Like查询效率明显提升，大大超过其他方法。但是在解释查询计划时发现，虽然possible_key显示的是索引字段，但实际上并没有应用索引（key为null），即仍然进行全表查询（Type=All）。之所以速度有很大的提升，仅仅是因为对'success%'进行字符串匹配比对'%success%'进行字符串匹配要快得多（后者需要匹配整列，前者只需要匹配开头词的)，无论索引如何。like'success%'还是不能应用全文索引，所以结论是Like通配符不能有效的利用全文索引来加速查询，但是在特定模式下的查询速度可以比in中的查询快通配符%模式。04小结本文讨论了MySQL中的4种模糊查询方式，包括：like通配符查询目标字段与模式字符串完全匹配的记录，不能应用全文索引提高查询速度，但是以特定字符开头的模糊查询比“%开始提速明显”要好。RegExp正则表达式功能强大，可以实现任意模式查询，但执行效率一般。MySQL内置函数也可以用于查询子串是否存在，包括Instr()、Locate()和Position()等，用法类似，但效率一般用于目标字段的查询其中包括全文索引，全文索引的查询效率最高，但可定制性较差，不支持任何匹配查询。当查询记录数较少时，几种查询方式的效率是可以接受的。根据任务要求灵活选择
