浅析关于sql中like操作符的使用及效率问题及如何使用locate或position或instr函数进行优化

一、like 操作符

　　LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

1、基本语法：% 表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示

// 从 “Persons” 表中选取居住在以 “N” 开始的城市里的人
SELECT * FROM Persons WHERE City LIKE ‘N%’

// 从 “Persons” 表中选取居住在以 “g” 结尾的城市里的人
SELECT * FROM Persons WHERE City LIKE ‘%g’

// 从 “Persons” 表中选取居住在包含 “lon” 的城市里的人
SELECT * FROM Persons WHERE City LIKE ‘%lon%’

// 从 “Persons” 表中选取居住在不包含 “lon” 的城市里的人：
SELECT * FROM Persons WHERE City NOT LIKE ‘%lon%’

2、基本语法：_ 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句

SELECT * FROM [user] WHERE u_name LIKE ‘三’
// 例如只找出“唐三藏”，这样u_name为三个字且中间一个字是“三”的

3、基本语法：[ ] 表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个

SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’
// 将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

4、基本语法：[^] 表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符

SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’
// 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

二、like 效率问题及使用locate或position或instr函数进行优化

　　在使用msyql进行模糊查询的时候，很自然的会用到like语句，通常情况下，在数据量小的时候，不容易看出查询的效率，但在数据量达到百万级，千万级的时候，查询的效率就很容易显现出来。这个时候查询的效率就显得很重要！

　　一般情况下like模糊查询的写法为（field已建立索引）：

SELECT `column` FROM `table` WHERE `field` like '%keyword%';

　　上面的语句用explain解释来看，SQL语句并未用到索引，而且是全表搜索，如果在数据量超大的时候，可想而知最后的效率会是这样。对比下面的写法：

SELECT `column` FROM `table` WHERE `field` like 'keyword%';

　　这样的写法用explain解释看到，SQL语句使用了索引，搜索的效率大大的提高了

　　但是有的时候，我们在做模糊查询的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，"keywork%"并不合适所有的模糊查询。这个时候，我们可以考虑用其他的方法

1、LOCATE（'substr',str,pos）方法

SELECT LOCATE('xbar',`foobar`); 
###返回0 

SELECT LOCATE('bar',`foobarbar`); 
###返回4

SELECT LOCATE('bar',`foobarbar`,5);
###返回7

　　备注：返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。

SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0

　　备注：keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据

2、POSITION('substr' IN `field`)方法

　　position可以看做是locate的别名，功能跟locate一样

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

3、INSTR(`str`,'substr')方法

SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 

　　select id, name from users where instr(id, ‘101’) > 0;
　　效果等价于
　　select id, name from users where id like ‘%101%’

　　效率:

　　select * from gt_alarm where alarm_date like ‘%2016/4/3%’;     --11s

　　select * from gt_alarm where instr(alarm_date,‘2016/4/3’)>0;     --9s

　　时间上的差异很明显，INSTR在一瞬间执行完成，因为这个是查找的字段，而非走全表扫描