MySQL中比like语句更高效的写法locate,position,instr,find_in_set,in

mysql 中 find_in_set() 方法：

让我们先列举一个示例：

1. 测试代码：   

2. CREATE TABLE `test` (  

3.   `id` int(8) NOT NULL auto_increment,  

4.   `name` varchar(255) NOT NULL,  

5.   `list` varchar(255) NOT NULL,  

6.   PRIMARY KEY  (`id`)  

7. )  

8. 

9. INSERT INTO `test` VALUES (1, 'name', 'daodao,xiaohu,xiaoqin');  

10. INSERT INTO `test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin');  

11. INSERT INTO `test` VALUES (3, 'name3', 'xiaoqin,daodao,xiaohu');  

12.   

13. test1:sql = select * from `test` where 'daodao' IN (`list`);  

14. 得到结果空值.  

15. test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`);  

16. 得到三条数据。 

17. Test3:sql = select * from `test` where `list` like '%daodao%'; 

18. 得到三条数据。 

 

比较：

In比较是否相等、可以利用索引

find_in_set比较是否包含，是精确匹配，字段值以英文”,”分隔、 find_in_set 是全表扫描的

like是广泛的模糊匹配，字符串中没有分隔符、

like本身效率就比较低，应该尽量避免查询条件使用like;对于like ‘%...%’(全模糊)这样的条件，是无法使用索引的，全表扫描自然效率很低;另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。

解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的;左模糊like

 

find_in_set用法：

FIND_IN_SET(str, list);

Str为指定字符串，list为范围字符串（list需使用英文”,”分隔）

当任意一个参数为NULL，返回NULL

当list为空字符串或者str在list中不存在，返回0

Str在list中存在，返回1-N

举例：

Select FIND_IN_SET(‘b’,  ‘a,b,c,d’);  ->2

你是否一直在寻找比MySQL的LIKE语句更高效的方法的，下面我就为你介绍几种。

LIKE语句
SELECT `column` FROM `table` where `condition` like`%keyword%'

事实上，可以使用 locate(position) 和 instr这两个函数来代替

LOCATE语句
SELECT `column` from `table` where locate(‘keyword’,`condition`)>0

或是 locate 的別名 position
POSITION语句
SELECT `column` from `table` where position(‘keyword’ IN`condition`)

或是
INSTR语句
SELECT `column` from `table` where instr(`condition`, ‘keyword’)>0

locate、position 和 instr 的差別只是参数的位置不同，同时locate多一个起始位置的参数外，两者是一样的。
mysql> SELECT LOCATE(‘bar’, ‘foobarbar’,5);

-> 7

速度上这三个比用 like 稍快了一点。