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

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

结果分别为:

 

 

 

 

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;

结果分别为:

 

 

 

 

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;

结果分别如下:

 

 

 

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

结果分别为:

 

 

 

PS:以上所说的情况都是不区分大小写的，这是因为mysql的两个系统变量导致的，即lower_case_file_system和lower_case_table_names，

lower_case_file_system

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

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

 

lower_case_table_names

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

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

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

同样适用于database名。

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

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

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

SHOW VARIABLES

找到这两个变量的默认值

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

执行该语句
SET lower_case_file_system = "OFF";
SET lower_case_table_names = "1";

报以下错误:

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

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

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

SELECT FIND_IN_SET(binary "A","a") find;-- 区分大小写

SELECT FIND_IN_SET("A","a") find;-- 不区分大小写

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