注册网站时审核是人工审核吗还是电脑审核,优秀企业门户网站建设,网络广告人社区,在线观看网址最新电影作者 | luanhz责编 | 郭芮本文对MySQL中几种常用的模糊搜索方式进行了介绍#xff0c;包括LIKE通配符、RegExp正则匹配、内置字符串函数以及全文索引#xff0c;最后给出了性能对比。引言MySQL根据不同的应用场景#xff0c;支持的模糊搜索方式有多种#xff0c;例如应用最… 作者 | luanhz责编 | 郭芮本文对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条记录。LikeLike算作MySQL中的谓词其应用与is、、和例如在如上表中查找所有以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。需要注意的是三个函数中子串和字符串的先后顺序是不一致的。例如以下语句均成功检索且返回目标索引11SELECT 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(urlhttp://www.1juzi.com/new/43141.html, encoding gb18030) 5itemsdoc(div.contentp:nth-child(2n1)).items() 6hots [item.text() for item in items] 7with connect(hostlocalhost, userroot, password123456, dbteststr, charsetutf8) 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为null)即仍然进行全表查询(Type All)。之所以带来速度上的大幅提升仅仅是因为对success%要比%success%执行字符串匹配要快得多(后者要整列匹配前者仅需匹配开头的单词即可)而与索引无关。Likesuccess%仍然无法应用全文索引所以得到的结论是Like通配符无法有效利用全文索引加速查询但在特定模式下的查询速度可快于通配符%模式下的查询。总结本文探讨了MySQL中4中模糊查询方式包括Like通配符用于查询目标字段与模式串完全匹配的记录且无法应用全文索引提高查询速度但以特定字符开头的模糊查询比以%开头时速度提升明显RegExp正则表达式功能强大可实现任意模式查询但执行效率一般简单的子串有无查询还可应用MySQL内置函数包括Instr()、Locate()和Position()等用法相近但效率一般对于包含全文索引的目标字段查询应用全文索引查询效率最高但可定制性差不支持任意匹配查询记录数目较少时几种查询方式效率均可接受可根据任务需求灵活选用。声明本文为作者投稿版权归其所有。【END】今日福利遇见大咖由 CSDN 全新专为技术人打造的高端对话栏目《大咖来了》来啦CSDN 创始人董事长、极客帮创投创始合伙人蒋涛携手京东集团技术副总裁、IEEE Fellow、京东人工智能研究院常务副院长、深度学习及语音和语言实验室负责人何晓冬来也科技 CTO 胡一川共话中国 AI 应用元年来了开发者及企业的路径及发展方向直达报名https://t.csdnimg.cn/uZfQ
