mysql模糊查询

mysql 中一般使用like 来进行模糊查询，但like 的效率非常的低，容易导致全表扫描，因此不推荐使用。

那有木有其他的方法代替like来进行模糊查询呢？

替代方法肯定是有的，以下是几个可替代like 进行模糊查询的 关键词

• instr
• locate
• position　　
• find_in_set

 

下面简单介绍下各个方法的使用情况

 

测试对象　　content 表

SELECT COUNT(mlzm_content.id) FROM mlzm_content

数据量： 33034 条信息

 

 

LIKE

SELECT id,Title from mlzm_content WHERE Title LIKE '%美女%'

结果：294 条记录

耗时： 0.0130 秒

SELECT id,Title from mlzm_content WHERE Title LIKE '%模特%'

结果：10 条记录

耗时： 0.0550 秒

结论：但查询到结果集的数据量少的时候like耗时会增加，即总数据量不变，结果越少耗时约大

 

INSTR

语法：INSTR(str, substr)

SELECT id,title from mlzm_content WHERE INSTR(Title,'美女')>0;

结果：294 条

耗时：0.0150 秒

INSTR(str, substr) 与LOCATE(substr, str) 类似，只是参数的位置变了

 

LOCATE

语法：LOCATE(substr, str)

SELECT id,title from mlzm_content WHERE LOCATE('美女',Title)>0;

结果：294 条

耗时： 0.0150 秒

SELECT id,title from mlzm_content WHERE LOCATE('模特',Title)>0

结果：10条

耗时： 0.0760 秒

 

普通用法：

  SELECT `column` from `table` where locate('keyword', `condition`)>0

 类似 js 的 indexOf(); locate() (返回的结果为子串在字符串中第一次出现的位置，因为从1开始计数，因此只要匹配结果均大于0)，没有查找到才返回0；

 

指定位置查找：

SELECT LOCATE('bar', 'foobarbar',5);  --> 7 (从foobarbar的第五个位置开始查找)

返回 'bar' 在 'foobarbar' 第一次出现的位置（这里限制了从第5位后面开始查找，因此结果为7），位置下标是从1开始计数的。

 

POSITION

语法：POSITION(substr IN str)

SELECT id,title from mlzm_content WHERE POSITION('美女' IN Title);

结果：294 条

耗时： 0.0150 秒

 

小结：LOCATE（）、POSITION（）、INSTR（）类似，执行效率前2这基本一致，INSTR()要比前2这稍微快一点。但3者的执行效率都要比like 的略低。

（可能是数据量不大，真实的效率有待考察，后续会利用大数据来进行效率测试后再做定论。这里的效率问题并不是一定用哪个就要好，一切都要根据实际需求测试后再做定论。）

 

FIND_IN_SET

语法：FIND_IN_SET(str, strlist)

SELECT id,title from mlzm_content WHERE FIND_IN_SET('美女',Title);

结果：1 条

耗时：  0.0470 秒

SELECT id,title from mlzm_content WHERE FIND_IN_SET('模特',Title)

结果：1 条

耗时：  0.0460 秒

SELECT FIND_IN_SET('b','a,b,c,d');

耗时：  0.0020 秒

 

 

总结：

LIKE是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。

Find_IN_SET  的执行效率在同样的结果集下，要比LIKE高。

Find_IN_SET 通常用做关键词匹配，因此在实际运用中大量数据查询可用Find_IN_SET 来代替LIKE ，减少信息量（因此关键词设置非常有必要）。