Mysql中in和find_in_set的使用说明

最新推荐文章于 2024-08-05 09:00:00 发布

NeverGiveUp7

最新推荐文章于 2024-08-05 09:00:00 发布

阅读量7.3k

点赞数

分类专栏： Mysql 文章标签： mysql find_in_set in

本文链接：https://blog.csdn.net/gengxiaoming7/article/details/48313175

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

在使用in函数的时候，发现查询的数据少了两条，查过资料才找到原因，跟大家分享一下！

原来以为mysql可以进行这样的查询

1】t_bdp_indices_user_info的表结构：
indices_user_id| create_time|update_time|report_kind
report_kind里面的数据形式：
eg:1,2,3/2,3/3,1/1,2,3,4,5,6/

2】错误：表里面目前有的数据：
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1,2,3 |
| 15948 | 2015-06-30 10:44:01 | NULL | 2,3 |
| 15949 | 2015-06-30 10:56:37 | NULL | 2,4 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2,1 |
+-----------------+---------------------+---------------------+-------------+

3】错误：第一种查询ＳＱＬ和结果：
select * from t_bdp_indices_user_info where 1 in (report_kind)
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1,2,3 |
+-----------------+---------------------+---------------------+-------------+
很明显上面的结果集不是我们想要的！
实际上这样是不行的，这样只有当'daodao'是list中的第一个元素(我测试的时候貌似是第一个也是不行的，只有当list字段的值等于daodao时才是对的)时，
查询才有效，否则都的不到结果，即使'daodao'真的再list中，也查不出来！

4】错误：下面的结果仍然不是我们想要的！
select * from t_bdp_indices_user_info where '1' in (report_kind);
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
+-----------------+---------------------+---------------------+-------------+

5】正确：
select * from t_bdp_indices_user_info where find_in_set(1,report_kind);
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1,2,3 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2,1 |
+-----------------+---------------------+---------------------+-------------+

6】正确：
select * from t_bdp_indices_user_info where find_in_set('1',report_kind);
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1,2,3 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2,1 |
+-----------------+---------------------+---------------------+-------------+

【总结】

因为list是变量，所以如果想让SQL正常工作，需要用find_in_set()函数：
select * from t_bdp_indices_user_info where find_in_set('1',report_kind);

所以如果list是常量，则可以直接用IN，否则要用FIND_IN_SET()函数【这句话有点儿怪！】

函数介绍：
FIND_IN_SET(str,strlist)
1.假如字符串str 在字符串列表strlist中，则返回值的范围在 1 到 N 之间【如下面++之间的例子】。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
第一次出现的位置索引：
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
+----------------------------+
| FIND_IN_SET('b','a,b,c,d') |
+----------------------------+
| 2 |
+----------------------------+

mysql> SELECT FIND_IN_SET('b','a,c,b,d');
+----------------------------+
| FIND_IN_SET('b','a,c,b,d') |
+----------------------------+
| 3 |
+----------------------------+

mysql> SELECT FIND_IN_SET('b','a,b,c,b,d');
+------------------------------+
| FIND_IN_SET('b','a,b,c,b,d') |
+------------------------------+
| 2 |
+------------------------------+
mysql> SELECT FIND_IN_SET('b','a,b,b,b');
+----------------------------+
| FIND_IN_SET('b','a,b,b,b') |
+----------------------------+
| 2 |
+----------------------------+
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2.如果str不在strlist 或strlist 为空字符串，则返回值为 0 。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> SELECT FIND_IN_SET('E','a,b,b,b');
+----------------------------+
| FIND_IN_SET('E','a,b,b,b') |
+----------------------------+
| 0 |
+----------------------------+
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3.字符串列表strlist就是一个由一些被‘,’符号分的字符串组成,用其他分割是不行的。
4.如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。
5.如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。
mysql> select * from t_bdp_indices_user_info where find_in_set(1,2,report_kind);
1582 - Incorrect parameter count in the call to native function 'find_in_set'
mysql> select * from t_bdp_indices_user_info where find_in_set('1,2',report_kind);
Empty set

上面的SQL一个有错误，另外一个的查询结果不对！

【延伸】
1.查询多个字符串！
mysql> select * from t_bdp_indices_user_info where find_in_set(1,report_kind) or find_in_set(2,report_kind);
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1,2,3 |
| 15948 | 2015-06-30 10:44:01 | NULL | 2,3 |
| 15949 | 2015-06-30 10:56:37 | NULL | 2,4 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2,1 |
+-----------------+---------------------+---------------------+-------------+

2.利用FIND_IN_set排序，此时的goods_id里面不能应该是"1,3,2,4,5,6"这种数据！
eg:
$ids = '9,3,45,1,8,2,6';

$sql = "... WHERE goods_id IN('{$ids}') ORDER BY FIND_IN_SET(goods_id, '{$ids}')";

数据库中数据的实际顺序：
mysql> select * from t_bdp_indices_user_info where report_kind in (1,2,3,4,5);
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1 |
| 15948 | 2015-06-30 10:44:01 | NULL | 4 |
| 15949 | 2015-06-30 10:56:37 | NULL | 3 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2 |
| 15954 | 2015-06-30 11:19:16 | NULL | 5 |
+-----------------+---------------------+---------------------+-------------+

从下面的两个结果集可以看出是按照find_in_set(report_kind,'1,2,3,4,5');里面的第二个参数进行排序的！
mysql> select * from t_bdp_indices_user_info where report_kind in (1,2,3,4,5) order by find_in_set(report_kind,'1,2,3,4,5');
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2 |
| 15949 | 2015-06-30 10:56:37 | NULL | 3 |
| 15948 | 2015-06-30 10:44:01 | NULL | 4 |
| 15954 | 2015-06-30 11:19:16 | NULL | 5 |
+-----------------+---------------------+---------------------+-------------+

mysql> select * from t_bdp_indices_user_info where report_kind in (1,2,3,4,5) order by find_in_set(report_kind,'5,3,4,2,1');
+-----------------+---------------------+---------------------+-------------+
| indices_user_id | create_time | update_time | report_kind |
+-----------------+---------------------+---------------------+-------------+
| 15954 | 2015-06-30 11:19:16 | NULL | 5 |
| 15949 | 2015-06-30 10:56:37 | NULL | 3 |
| 15948 | 2015-06-30 10:44:01 | NULL | 4 |
| 15951 | 2015-06-30 11:06:37 | NULL | 2 |
| 1 | 2015-07-14 14:14:43 | 2015-07-15 15:00:25 | 1 |
| 15871 | 2015-07-07 14:21:25 | NULL | 1 |

+-----------------+---------------------+---------------------+-------------+

不足之处希望大家补充！