mysql中in函数和find_in_set函数的区别详解

前段时间项目中使用到Mysql的FIND_IN_SET函数，感觉挺好用的。过一段时间，老大找到我说，这个需要改为IN，哈哈，只能改了，原因会在下面分析到！

mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | name1,nam2  |
|  2 | name1       |
|  3 | name3       |
|  4 | name2,name4 |
|  5 | name3,name5 |
+----+-------------+
5 rows in set (0.00 sec)

现在我们就需要从这些数据中查询出 name 字段中包含有name1的数据。

我们先使用 in 来查询一下：

mysql> select * from test where name in ('name1');
+----+-------+
| id | name  |
+----+-------+
|  2 | name1 |
+----+-------+
1 row in set (0.00 sec)

可以看到只查询出了一条数据，显然不是我们想要的结果。

使用 find_in_set:

mysql> select * from test where find_in_set('name1',name);
+----+------------+
| id | name       |
+----+------------+
|  1 | name1,nam2 |
|  2 | name1      |
+----+------------+
2 rows in set (0.02 sec)

结果是正确的，是我们想要的。

下面解释下find_in_set 函数：

find_in_set(str1,str2) 函数：返回str2中str1所在的位置索引，其中str2必须以","分割开。

参考mysql文档中的解释：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

FIND_IN_SET(str,strlist)
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

其中有点需要再次提醒的是：strlist必须以逗号(,)分隔

我们可以来做个试验，把test表中name字段的链接字符“,”改为"-"

mysql> select * from test;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | name1-nam2  |
|  2 | name1       |
|  3 | name3       |
|  4 | name2-name4 |
|  5 | name3-name5 |
+----+-------------+
5 rows in set (0.00 sec)

再次使用find_in_set查询name字段中包含有name1的数据

mysql> select * from test where find_in_set('name1',name);
+----+-------+
| id | name  |
+----+-------+
|  2 | name1 |
+----+-------+
1 row in set (0.00 sec)

可以看到结果只有一条了。

mysql中FIND_IN_SET函数用来比较是不是包含，不管‘list'字段是变量或给定的字符串常量都能很好的工作。MySQL中原型为：FIND_IN_SET(str,strlist)。 假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 

一个字符串列表就是一个由一些被‘,'符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则   FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,')时将无法正常运行。str也可以是变量，比如表中的一个字段。

当然，这不是我们项目中需要将FIND_IN_SET替换为IN的原因，因为在我们项目中两者都可以实现功能。只是IN比FIND_IN_SET性能高。我们要查询的字段是主键，使用IN时会使用索引，只会查询表中部分数据。FIND_IN_SET则会查询表中全部数据，由于数据量比较大，性能肯定不高，所以替换为IN。想看查询部分还是全部，可以使用EXPLAIN即解释功能查看，如果是部分则type为range(范围)，全部则type为ALL(全部)，还有个type是const，常量级的，呵呵。。。

最佳实践：

1、如果待查询的条件是常量那就使用IN，是变量则使用FIND_IN_SET，可以使用索引的，貌似，哈哈。

2、如果使用IN和FIND_IN_SET都能满足条件，则最好使用IN，理由同上，特别是查询字段为主键时或有索引时。

3、如果使用IN不能满足功能需求，那只能使用FIND_IN_SET了，哈哈，有时候说不定IN中条件加个%号也可以解决问题，加个%号IN就不只是比较是否相等了！