谈一谈MySQL的LOCATE、FIND_IN_SET、INSTR、POSITION的差别

ゞ浴缸里的玫瑰 2022-06-13 02:06 512阅读 0赞

1、LOCATE(*substr*,*str*) , LOCATE(*substr*,*str*,*pos*)

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

SELECT LOCATE("a","A") locate;-- 注意:该函数不区分大小写  
SELECT LOCATE('bar', 'foobarbar') locate;  
SELECT LOCATE('xbar', 'foobar') locate;-- 未找到值  
SELECT LOCATE('bar', 'foobarbar',5) locate;  
SELECT LOCATE(null, 'foobarbar',5) locate;-- 当某个参数为null，则返回NULL

结果分别为:

![Center][]

![Center 1][]

![Center 2][]

![Center 3][]

![Center 4][]

2、FIND\_IN\_SET(*str*,*strlist*)

假如字符串*str* 在由*N* 子链组成的字符串列表*strlist* 中， 则返回值的范围在 1 到*N* 之间 。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则   FIND\_IN\_SET() 函数被优化，使用比特计算。如果*str*不在*strlist* 或*strlist* 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。 这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

SELECT FIND\_IN\_SET("A","a") find;-- 不区分大小写  
SELECT FIND\_IN\_SET("A","A") find;  
SELECT FIND\_IN\_SET("A","AB") find;  
SELECT FIND\_IN\_SET("A","AB,qw") find;  
SELECT FIND\_IN\_SET(NULL,"AB") find;

结果分别为:

![Center 5][]

![Center 6][]

![Center 7][]

![Center 8][]

![Center 9][]

3、INSTR(*str*,*substr*) （这个函数和locate作用很像）

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

SELECT INSTR("abcdefg","a") instr;  
SELECT INSTR("abcdefg","ac") instr;  
SELECT INSTR("abcdefg","A") instr;-- 不区分大小写  
SELECT INSTR("abcdefg",NULL) instr;

结果分别如下:

![Center 10][]

![Center 11][]

![Center 12][]

![Center 13][]

4、POSITION(*substr* IN *str*)

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

SELECT POSITION("p" IN 'php');   
      SELECT POSITION("P" IN 'php');  
      SELECT POSITION("php" IN 'p');  
      SELECT POSITION(NULL IN 'php');-- 返回null

结果分别为:

![Center 14][]

![Center 15][]

![Center 16][]

![Center 17][]

PS:以上所说的情况都是不区分大小写的，这是因为mysql的两个系统变量导致的，即lower\_case\_file\_system和lower\_case\_table\_names，

lower\_case\_file\_system

改变了描述数据文件名的大小写问题。OFF：区分大小写；ON：不区分大小写。

[Linux][]下[数据库][Link 1]和表名是大小写敏感的，windows下不敏感。

lower\_case\_table\_names

0：表名是指定的类型，大小写敏感

1：表名在磁盘上存储小写，大小写不敏感

2：表名是指定的类型，比较时时小写

同样适用于database名。

如果是windows系统，希望[MySQL][Link 1]支持大小写，可以使用该系统变量

如果在不支持大小写的系统上设置0，那么访问myisam表时使用不同大小写字符，可能会导致索引corrupt。

执行SQL语句，查看这两个系统变量的默认值:

SHOW VARIABLES

找到这两个变量的默认值

![Center 18][]

但是，如果我们重新设置他们的值，发现会报错，原来他们是只读的变量，也就是说不能修改的

执行该语句  
SET lower\_case\_file\_system = "OFF";  
SET lower\_case\_table\_names = "1";

报以下错误:

![Center 19][]

那怎么办呢？如果业务需求需要区分大小呢？

[mysql][Link 1]在查询字符串时，大小写不敏感。如某字段需要区分大小写，可以在定义该字段时指定binary属性。也可以在查询语句中，使用binary命令强行区分大小写：

现在我们来看下面这两句SQL:

SELECT FIND\_IN\_SET(binary "A","a") find;-- 区分大小写  
![Center 20][]  
SELECT FIND\_IN\_SET("A","a") find;-- 不区分大小写

![Center 21][]

通过结果，我们可以发现，现在已经可以实现字母大小写的比较查询了。

[Center]: /images/20220613/03669a952798447b86e096cbd683a261.png
[Center 1]: /images/20220613/6eeb9f69ede048e2a05b1a3ac056c739.png
[Center 2]: /images/20220613/3cc08cc358c342d78106e5c1be775a03.png
[Center 3]: /images/20220613/9f119db99d4a40a393c50cb6047c4aa0.png
[Center 4]: /images/20220613/56c1fb9e85494e9a93bd8f2f8f93b372.png
[Center 5]: /images/20220613/d4ae4a8d8f814cb2b8778b7827cd6f3b.png
[Center 6]: /images/20220613/01f32f11990149b3b332793850e948b9.png
[Center 7]: /images/20220613/58dd2a91cb52406bb874ef044c69367c.png
[Center 8]: /images/20220613/464af47a4edc47d1a23a3b406879d918.png
[Center 9]: /images/20220613/40acab7c22f64b3ba39d767470483fe1.png
[Center 10]: /images/20220613/9f4cc88cfed649abbe82c0ff2718b2c6.png
[Center 11]: /images/20220613/02a710be92c04a55882b621ac696ee95.png
[Center 12]: /images/20220613/7ff397d30c354e6e9f0fd28f0be18d74.png
[Center 13]: /images/20220613/13fe34e2e6ee4344a1c2e798e24743d6.png
[Center 14]: /images/20220613/6b792775d23948958137c6bf561b14c1.png
[Center 15]: /images/20220613/e3f367ac1b9748a082e279d164c3488a.png
[Center 16]: /images/20220613/5f18089a477d41a3b60782a9af726600.png
[Center 17]: /images/20220613/7be0bf7d484d4e778acb7cf7f2103ca9.png
[Linux]: http://lib.csdn.net/base/linux
[Link 1]: http://lib.csdn.net/base/mysql
[Center 18]: /images/20220613/4223d2d299da4e35a475ff640c268490.png
[Center 19]: /images/20220613/c7df0785ca5f43f9b25378537d282f05.png
[Center 20]: /images/20220613/d09fb9ffd02e4a73a3c598a16f309cb7.png
[Center 21]: /images/20220613/6e2d55f61a7444458abc2ce9b06b58b0.png