MySQL中的find_in_set()函数使用技巧心得与应用场景总结
🚀 个人主页 极客小俊
✍🏻 作者简介：web开发者、设计师、技术分享博主
🐋 希望大家多多支持一下, 我们一起进步！😄
🏅 如果文章对你有帮助的话，欢迎评论 💬点赞👍🏻 收藏 📂加关注
前言
今天很多小伙伴来问我find_in_set这个函数在MySQL中到底有什么用处 还有与这个函数相关的应用场景会有哪些? 今天我就来给大家讲解一下这个函数从基本的使用到实际应用! 让大家不再迷茫!
1.首先认识一下find_in_set()函数
首先很多小伙伴一定会去查阅MySQL的官方手册, 但可能有些新手朋友查阅出来可能看不明白，那好吧我也先来查下手册帮助新手朋友如何来看手册中的解释: 例如下图:
官方文档解释的语法是: FIND_IN_SET(str,strlist) ;
文档解释:
(假如字符串str在由N个子链组成的字符串列表strlist 中, 则返回值的范围在 1 到 N 之间)
这句话的意思就是看str这个字符在不在或者可以说成是否包含这个strlist字符列表中, 如果有存在或包含在strlist这个字符列表中就返回一个位置的数字, 并且这个数字一定是大于0 的数字,
这里解释一下strlist字符串列表就是一个由一些被 逗号‘,’ 分开的单一字符串
注意:如果str不在strlist 或strlist 为空字符串，则返回值为 0 , 并且如任意一个参数为空，则返回值为 0 也可以说返回NULL
2.find_in_set()函数的实际基本操作
==案例1==
好了上面的解释如果还没有听明白 我们就来看看实际的操作案例吧! 打开cmd进入MySQL命令行界面, 执行如下SQL语句
SELECT FIND_IN_SET('b', 'a,b,c,d');
#--结果为2 , 因为b 在strlist集合中放在2的位置 并且起始数是从1开始计算起的!
如下图: 这个案例应该很简单看出这个函数的作用了吧!
不难看出只要存在于第二个字符串列中的话就返回一个位置信息!
==案例2==
select FIND_IN_SET('重庆','重庆');
#-- 这里返回的就是位置下标1
select FIND_IN_SET('重庆','北京');
#-- 这里返回的就是0 因为第一个字符串 重庆 不存在于第二个字符串当中
select FIND_IN_SET('2', '1，2');
#-- 这里返回的就是位置下标2
select FIND_IN_SET('6', '1');
#-- 这里返回的就是0 因为第一个字符串 6 不存在于第二个字符串当中
所以注意: 这时候的第二个参数的集合就有点特殊了 只有一个字符串的情况下, 如果前一个字符串 存在于在后一个字符串集合中才返回大于0的数 , 但如果不存在就会返回0
如下图:
通过案例2 小伙伴们是不是更加对find_in_set()这个函数有所了解了呢？
注意 使用find_in_set函数进行查询整张表的时候, 一次返回多条记录的情况
比如说: id是某一个表的主键字段，然后每条记录分别是id等于1，2，3，4，5的时候, 就有点类似in() 的操作了
案例:
select * from per where find_in_set(id,'2,3,4,5,6');
那么结果也就可能是这样，如下图
其实上面的SQL语句也等同于下面的in
select * from per where id in(2,3,4,5,6);
小提示: find_in_set() 一般都写在 where关键字的后面!
3.find_in_set()与in()的应用场景区别
刚才我们也看到了
select * from per where find_in_set(id,'2,3,4,5,6');
与
select * from per where id in(2,3,4,5,6);
上面两句SQL结果是一样的 ，但细心的小伙伴们不知道看出来有哪里不一样了没有呢？
对答案就在于 一个有单引号而另一个没有单引号这里也要针对你所检测的字段是数字类型 还是字符串类型的字段!
==对于find_in_set()而言==
第二个参数无论是字符串还是数字都必须用单引号括起来, 并且字符串列表是用逗号隔开,是一个字符串列表整体要是不这样写 会报语法错误!
案例
select * from per where find_in_set(id,'2,3,4,5,6');
==对于in而言==
如果是检测的字段如果是数字类型的话就不能用单引号，因为这样会让结果出现偏差
案例
select * from per where id in('2,3,4,5,6'); #--错误的
select * from per where id in(2,3,4,5,6); #--正确的
如下图:你会看到只有一个结果为什么呢？ 这是因为MySQL会自动的判断字符串的第一个是否匹配，但是后面的就截掉了 没有进行匹配!
正确的结果应该是如下
如果是检测的字符 那么每一个字符都需要用到单引号括起来 才可以, 就像下面的SQL语句, in与find_in_set()的比较 结果是一样的,
但就是要注意一下以上我所讲的注意一下数据类型细节区分问题就OK 了!
select * from per where paddr in('重庆','北京','上海');
#--比较
select * from per where find_in_set(paddr,'重庆,北京,上海');
首先我们新建一个表 并插入一些数据来测试! SQL代码语句如下:
CREATE TABLE `test1` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`list` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `test1`(name,list) VALUES ('张三', '篮球,足球,羽毛球');
INSERT INTO `test1`(name,list) VALUES ('李四', '射箭,跳远,足球');
INSERT INTO `test1`(name,list) VALUES ('王武', '跳远,篮球');
INSERT INTO `test1`(name,list) VALUES ('小王', '射箭');
在使用in()的时候
比如说如下SQL语句
SELECT id,name,list from test1 WHERE '篮球' IN(list);
很多人原以为可以进行这样的查询,但结果确实返回的Empty 为什么呢?
原因:
因为只有当list字段的内容完全只等于 '篮球'这个值时（和IN前面的字符串才能完全匹配），查询才有效果!
否则都得不到结果，即使'篮球'真的在list中 也不会有结果出来，这也体现了 in()对数据的精确性把控的不是很好!
也就是说 ''篮球'' 在张三、王武对应的list字段里都存在, 但就是查询不出来的原因, 就是除了''篮球' '还有其他的字符!
所以只有当list字段里面的内容完全只等于 in() 前面的字符串时 才能完全匹配，查询才有效!
如下代码
SELECT * from test1 WHERE '射箭' IN(list);
如下图:
在使用find_in_set()的时候
再比如说 我们想查询list字段中只包含篮球的数据行
SELECT id,name,list from test1 WHERE '篮球' IN('篮球','足球','羽毛球');
#-- 这里结果是全部都查询出来了 而不是我们想要对应的数据 不能正常的查询出我们想要的结果! 为什么呢？
因为这句话的意思是 查询 ''篮球'' 这个字符 在不在 这个in()所包含的字符常量里面，仅仅是如此， 然后如果成立, 有就把数据全部显示出来, select后面跟的就是 这三个字段, 前面的条件成立了 所以这三个字段的所有数据也显示出来了! 这个应该不难理解!
所以从需求语法的角度这句SQL就是不能正确给出结果的语句! 那么这样查询就显然不合理!
但我们的需求 却不是这样，我们想的是查询list字段中 只包含 '篮球',的数据行!
所以如果要让以上的SQL能正确工作，需要用find_in_set()函数
代码如下
SELECT id,name,list from test1 WHERE FIND_IN_SET('篮球',list);
小结
查字段固定的内容时候, 比如: 字段 in('篮球', '足球', '羽毛球'), 查是否有包含in中独立存在的信息 就用in
查字段的内容是否包含其中一个指定的关键内容时 ,就要用find_in_set()函数
SELECT id,name,list from test1 WHERE list in('篮球','足球'); #-- 返回list字段内容只是篮球 或 足球的数据
SELECT id,name,list from test1 WHERE find_in_set('篮球',list); #-- 返回list字段内容中包含了篮球的数据
4.find_in_set()和like的应用场景区别
在mysql中，有时我们在做数据库查询时，需要得到某字段中包含某个值的记录，但是它也不是用like能解决的，使用like可能查到我们不想要的记录,这时候mysql的FIND_IN_SET函数就派上用场了
需求案例1
我们来看下面的应用场景例子: 权限
代码案例如下
#-- 创建表并插入语句
CREATE TABLE users(
id int(6) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
limits VARCHAR(50) NOT NULL, -- 权限字段
PRIMARY KEY (id)
);
INSERT INTO users(name, limits) VALUES('张三','1,2,12');
INSERT INTO users(name, limits) VALUES('李四','11,22,32');
INSERT INTO users(name, limits) VALUES('王武','1,2,32');
其中limits表示用户所拥有的权限（以逗号分隔）!
需求
现在想查询拥有权限编号为2的用户，则SQL查询结果如下：
SELECT * FROM users WHERE limits LIKE '%2%';
如果用like关键字的话，则查询结果如下：
这样第二条数据结果是不具有权限'2'的用户也查出来了，这种是完全不符合预期的结果!
那么现在我们利用find_in_set()函数来解决这个问题 SQL语句如下:
#-- 查询用户拥有权限编号为:2 的用户
SELECT * FROM users WHERE FIND_IN_SET(2,limits);
这样就能达到我们预期的效果，问题就解决了！
需求案例2
人有时会身兼数职，需要查找出其中担任某一职务的都有哪些人，如下面position字段，不同的职务用数字表示，多个职务以逗号隔开。其实跟上面的案例是一样的,
如下表: User信息表
需求
查找出担任1职务的人员:
如果是用模糊查询的方式 如下:
select * from 表名称 where position like '%1%'
很显然跟上面我说的也是一个道理，虽然把没有包含1的过滤掉了，仔细观察你会发现position为: '10' 的也被查出来了，但这个不符合业务要求 如下:
所以这种需求的解决方案跟上面完全一样:采用MySQL的原生函数find_in_set(str,array)来查询即可
select * from user where find_in_set(1,position);
结果如下图:
小提示 FIND_IN_SET(str,strlist)函数，其中strlist字符串列表参数只识别英文逗号, 所以小伙伴们在使用PHP 或者JAVA插入数据的时候一定要记得使用逗号把字符串分割好在保存到字段中去哦!
小结:
- find_in_set(str1,strlist)字符串函数是返回strlist中str1所在的位置索引, strlist必须以","分割开。
like是广泛的模糊匹配，字符串中没有分隔符，
find_in_set是精确匹配，字段值以英文”,”分隔，find_in_set查询的结果要比like查询更加精确!
总结
关于find_in_set()函数我就先分享到这里! 大家有问题的话可以私信 或 关注我的博客 我会尽量给大家提供解答意见!
