【揭秘】MySQL的find_in_set()函数使用技巧与应用场景心得总结!

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 技巧心得总结!

MySQL中的find_in_set()函数使用技巧心得与应用场景总结

🚀 个人主页 极客小俊
✍🏻 作者简介:web开发者、设计师、技术分享博主
🐋 希望大家多多支持一下, 我们一起进步!😄
🏅 如果文章对你有帮助的话,欢迎评论 💬点赞👍🏻 收藏 📂加关注

带你玩转MySQL数据库.png

前言

今天很多小伙伴来问我find_in_set这个函数在MySQL中到底有什么用处 还有与这个函数相关的应用场景会有哪些? 今天我就来给大家讲解一下这个函数从基本的使用到实际应用! 让大家不再迷茫!

1.首先认识一下find_in_set()函数

首先很多小伙伴一定会去查阅MySQL的官方手册, 但可能有些新手朋友查阅出来可能看不明白,那好吧我也先来查下手册帮助新手朋友如何来看手册中的解释: 例如下图:

1.png

官方文档解释的语法是: 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.png

==案例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

如下图:

3.png

通过案例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');

那么结果也就可能是这样,如下图

4.png

其实上面的SQL语句也等同于下面的in

select * from per where id in(2,3,4,5,6);

5.png

小提示: 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');

6.png

==对于in而言==

如果是检测的字段如果是数字类型的话就不能用单引号,因为这样会让结果出现偏差

案例

select * from per where id in('2,3,4,5,6');  #--错误的

select * from per where id in(2,3,4,5,6);  #--正确的

如下图:你会看到只有一个结果为什么呢? 这是因为MySQL会自动的判断字符串的第一个是否匹配,但是后面的就截掉了 没有进行匹配!

7.png

正确的结果应该是如下

8.png

如果是检测的字符 那么每一个字符都需要用到单引号括起来 才可以, 就像下面的SQL语句, in与find_in_set()的比较 结果是一样的,

但就是要注意一下以上我所讲的注意一下数据类型细节区分问题就OK 了!

select * from per where paddr in('重庆','北京','上海'); 
#--比较
select * from per where find_in_set(paddr,'重庆,北京,上海');

9.png

首先我们新建一个表 并插入一些数据来测试! 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字段里都存在, 但就是查询不出来的原因, 就是除了''篮球' '还有其他的字符!

10.png

所以只有当list字段里面的内容完全只等于 in() 前面的字符串时 才能完全匹配,查询才有效!

如下代码

SELECT * from test1 WHERE '射箭' IN(list);

如下图:

11.png

在使用find_in_set()的时候

再比如说 我们想查询list字段中只包含篮球的数据行

SELECT id,name,list from test1 WHERE '篮球' IN('篮球','足球','羽毛球');
#-- 这里结果是全部都查询出来了 而不是我们想要对应的数据 不能正常的查询出我们想要的结果! 为什么呢?

因为这句话的意思是 查询 ''篮球'' 这个字符 在不在 这个in()所包含的字符常量里面,仅仅是如此, 然后如果成立, 有就把数据全部显示出来, select后面跟的就是 这三个字段, 前面的条件成立了 所以这三个字段的所有数据也显示出来了! 这个应该不难理解!

所以从需求语法的角度这句SQL就是不能正确给出结果的语句! 那么这样查询就显然不合理!

12.png

但我们的需求 却不是这样,我们想的是查询list字段中 只包含 '篮球',的数据行!

所以如果要让以上的SQL能正确工作,需要用find_in_set()函数

代码如下

SELECT id,name,list from test1 WHERE FIND_IN_SET('篮球',list);

13.png

小结

查字段固定的内容时候, 比如: 字段 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字段内容中包含了篮球的数据

14.png

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关键字的话,则查询结果如下:

15.png

这样第二条数据结果是不具有权限'2'的用户也查出来了,这种是完全不符合预期的结果!

那么现在我们利用find_in_set()函数来解决这个问题 SQL语句如下:

#-- 查询用户拥有权限编号为:2 的用户
SELECT * FROM users WHERE FIND_IN_SET(2,limits);

这样就能达到我们预期的效果,问题就解决了!

需求案例2

人有时会身兼数职,需要查找出其中担任某一职务的都有哪些人,如下面position字段,不同的职务用数字表示,多个职务以逗号隔开。其实跟上面的案例是一样的,

如下表: User信息表

16.png

需求

查找出担任1职务的人员:

如果是用模糊查询的方式 如下:

select * from 表名称 where position like '%1%'

很显然跟上面我说的也是一个道理,虽然把没有包含1的过滤掉了,仔细观察你会发现position为: '10' 的也被查出来了,但这个不符合业务要求 如下:

17.png

所以这种需求的解决方案跟上面完全一样:采用MySQL的原生函数find_in_set(str,array)来查询即可

select * from user where find_in_set(1,position);

结果如下图:

18.png

小提示 FIND_IN_SET(str,strlist)函数,其中strlist字符串列表参数只识别英文逗号, 所以小伙伴们在使用PHP 或者JAVA插入数据的时候一定要记得使用逗号把字符串分割好在保存到字段中去哦!

小结:

  1. find_in_set(str1,strlist)字符串函数是返回strlist中str1所在的位置索引, strlist必须以","分割开。

19.png

  1. like是广泛的模糊匹配,字符串中没有分隔符,

  2. find_in_set是精确匹配,字段值以英文”,”分隔,find_in_set查询的结果要比like查询更加精确!

总结

关于find_in_set()函数我就先分享到这里! 大家有问题的话可以私信 或 关注我的博客 我会尽量给大家提供解答意见!

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
JSON 关系型数据库 MySQL
Mysql(5)—函数
MySQL提供了许多内置的函数以帮助用户进行数据操作和分析。这些函数可以分为几类,包括聚合函数、字符串函数、数值函数、日期和时间函数、控制流函数等。
79 1
Mysql(5)—函数
|
2月前
|
存储 关系型数据库 MySQL
MySQL在企业内部应用场景有哪些
【10月更文挑战第17天】MySQL在企业内部应用场景有哪些
52 0
|
2月前
|
存储 关系型数据库 MySQL
介绍一下MySQL的一些应用场景
【10月更文挑战第17天】介绍一下MySQL的一些应用场景
201 0
|
29天前
|
关系型数据库 MySQL Serverless
MySQL函数
最常用的MySQL函数,包括聚合函数,字符串函数,日期时间函数,控制流函数等
35 1
|
1月前
|
Java Python
gc模块的set_threshold函数
gc模块的set_threshold函数
|
2月前
|
存储 算法 Java
作为Collection接口的子接口,Set不支持重复元素,也不保证元素顺序,适用于需要唯一性约束的场景。
【10月更文挑战第16天】Java的Set接口因其独特的“不重复性”而备受关注。作为Collection接口的子接口,Set不支持重复元素,也不保证元素顺序,适用于需要唯一性约束的场景。其背后的实现机制依赖于哈希表或红黑树等数据结构,通过哈希算法和equals()方法确保元素的唯一性。例如,使用HashSet可以轻松过滤重复的字符串。这种设计使Set在处理唯一数据时高效便捷。
27 3
|
2月前
|
架构师 关系型数据库 MySQL
MySQL最左前缀优化原则:深入解析与实战应用
【10月更文挑战第12天】在数据库架构设计与优化中,索引的使用是提升查询性能的关键手段之一。其中,MySQL的最左前缀优化原则(Leftmost Prefix Principle)是复合索引(Composite Index)应用中的核心策略。作为资深架构师,深入理解并掌握这一原则,对于平衡数据库性能与维护成本至关重要。本文将详细解读最左前缀优化原则的功能特点、业务场景、优缺点、底层原理,并通过Java示例展示其实现方式。
96 1
|
2月前
|
关系型数据库 MySQL 数据库
MySQL数据库:基础概念、应用与最佳实践
一、引言随着互联网技术的快速发展,数据库管理系统在现代信息系统中扮演着核心角色。在众多数据库管理系统中,MySQL以其开源、稳定、可靠以及跨平台的特性受到了广泛的关注和应用。本文将详细介绍MySQL数据库的基本概念、特性、应用领域以及最佳实践,帮助读者更好地理解和应用MySQL数据库。二、MySQL
126 5
|
2月前
|
存储 SQL 关系型数据库
MySQL 存储函数及调用
MySQL 存储函数及调用
118 3
|
1月前
|
SQL NoSQL 关系型数据库