Mysql函数INSTR、LOCATE、POSITION VS LIKE

Mysql 大家都会用这样的語法：

SELECT `column` FROM `table` where `condition` like `%keyword%’

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

SELECT `column` from `table` where locate(‘keyword’, `condition`)>0
或是 locate 的別名 position
SELECT `column` from `table` where position(‘keyword’ IN `condition`)

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

locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个请始位置的参数外，两者是一样的。

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

另付三个函数的说明：

• INSTR(str,substr)

返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，只是参数的顺序被颠倒。

mysql> SELECT INSTR(‘foobarbar’, ‘bar’);

-> 4

mysql> SELECT INSTR(‘xbar’, ‘foobar’);

-> 0

这个函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写。

• LOCATE(substr,str) , LOCATE(substr,str,pos)

第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中，则返回值为0。

mysql> SELECT LOCATE(‘bar’, ‘foobarbar’);

-> 4

mysql> SELECT LOCATE(‘xbar’, ‘foobar’);

-> 0

mysql> SELECT LOCATE(‘bar’, ‘foobarbar’,5);

-> 7

这个函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写。

• POSITION(substr IN str)

POSITION(substr IN str)是 LOCATE(substr,str)同义词。

1. 使用locate()方法

        1.1.普通用法：

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

         类似于 java 的 indexOf();不过 locate() 只要找到返回的结果都大于0(即使是查询的内容就是最开始部分)，没有查找到才返回0；

        1.2. 指定其实位置：

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

2.使用instr()函数 (据说是locate()的别名函数)

       SELECT `column` from `table` where instr(`condition`, ‘keyword’ )>0

       唯一不同的是 查询内容的位置不同，见SQL语句中过的keyword

3.使用position()方法，(据说也是locate()方法的别名函数，功能一样) 

         SELECT `column` from `table` where position(‘keyword’ IN `condition`)

         不过它不再是通过返回值来判断，而是使用关键字 in

4.使用find_in_set()函数

       如： find_in_set(str,strlist)，strlist必须要是以逗号分隔的字符串

    如果字符串str是在的strlist组成的N子串的字符串列表，返回值的范围为1到N

SQL> SELECT FIND_IN_SET('b','a,b,c,d');+---------------------------------------------------------+| SELECT FIND_IN_SET('b','a,b,c,d')                       |+---------------------------------------------------------+| 2                                                       |+---------------------------------------------------------+1 row in set (0.00 sec) 

 

    总结： locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个起始位置的参数外，两者是一样的。

              find_in_set()是个比较特殊的存在，但它们都是返回要查找的子字符串 在 指定字符串中的位置。

              速度上前3个比用 like 稍快了一点。(不过这四个函数都不能使用索引，这是个遗憾)