背景
今天同事让帮忙写个sql,大致是一张表的两个字段都关联另外一张表的主键,这个完全可以把两个表当作三个表来写,大概是这种:
SELECT
t1.name,
t1.id,
t2.reason,
t3.result
FROM
test1_copy1 AS t1
INNER JOIN test1 AS t2 ON t1.id = t2.reason
INNER JOIN test1 AS t3 ON t1.id = t3.result
这种是可以实现的,但是我看到数据里的另一个点,那就是t2.reason字段,存放的是用逗号隔开的字符串,如下所示,kg_rules表的reason和result对应kg_facts表的id,查询kg_rules表的每一条记录对应的名字(name)和含义(mean)
第一步:先使用find_in_set解决范围查找
SELECT
a.id,
b.name
FROM
kg_rules a
LEFT JOIN kg_facts b ON FIND_IN_SET(b.id, a.reason)
结果为:
这里会看到根据表kg_rules的reason,找到了三条记录。达到了我想要的结果,来分享记录一下Mysql这个方法吧。
1.认识find_in_set()函数
Mysql官方文档手册,如下图:
2.find_in_set()基本操作
select find_in_set (‘b’, ‘a,b,c,d,w’);
结果为2,因为b字符串在strlist集合中,在第二个位置,起始数是从1开始的select find_in_set(‘6’,‘1,2’);
结果为0,因为第一个字符串6,并不在第二个参数中
对于上面写的sql语句,使用find_in_set()进行数据查询的时候,一次可以返回多条记录的情况
上面的语句类似于in()如下的操作:
select id,name from kg_facts where id in(1,2,3)
3.find_in_set()与in()的应用场景区别
刚刚上面的情况也看到了
select * from kg_facts where find_in_set(id,‘1,2,3’)
与
select * from kg_facts where id in(1,2,3)
两个结果是一样的。不一样就在于一个有单引号,一个没有。这里也要针对你药检测的字段是数字类型还说字符串类型。
对于find_in_set()而言,第二个参数无论是字符串还说数字,都必须用单引号括起来,并且列表中用逗号隔开,这个字符串列表是一个整体。
对于in()而言,如果检测的字段是数字类型就不能用单引号,因为会出现偏差。
例:
select * from kg_facts where id in(1,2,3) —正确的
select * from kg_facts where id in(‘1,2,3’) —错误的
你看只有一个结果,这是因为Mysql会自动判断字符串的第一个是否匹配,但是后面的就被截掉了,不进行匹配!!
如果检测的是字符串,那么使用in()的时候,每个字符串都应该被单引号括起来
select * from per where paddr in(‘重庆’,‘北京’,‘上海’);
比较
select * from per where find_in_set(paddr,‘重庆,北京,上海’);
小结
查字段固定的内容时,比如: 字段名 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()的应用场景区别
在数据库查询的时候,需要得到某字段中包含某个值的记录,但是它并不是like能解决的,因为使用like可能会查到我们不想要的就,这个时候find_in_set函数就可以用了
需求:查询用户拥有权限编号为2的
select * from users_test where limits like ‘%2%’;
结果看到了,并不是我们想要的,那么使用find_in_set函数来看看
select * from users_test where find_in_set(2,limits)
小结
1.find_in_set(str, strlist)字符串函数是返回strlist中str所在的位置索引,strlist必须以英文逗号隔开
2.like是广泛的模糊查询,字符串中没有分隔符
3.find_in_set是精确匹配,查询结果比like查询更精确!
第二步:多表关联
了解完find_in_set函数后,我们来直接多表查询,
SELECT
b.id,
a.id,
b.name,
c.name
FROM
kg_rules a
LEFT JOIN kg_facts b ON FIND_IN_SET(b.id, a.reason)
left join kg_facts c on a.result=c.id;
因为用了find_in_set函数,所以有多条查询结果
看这个结果,也不是我想要,我想要同一个id,对应一条记录,并把多条记录放在一个字段展示
第三步:group_concat()函数合并列 — 组内拼接函数
下面的操作就是常见的需求,右边显示一列组名,右边显示组名下所有的成员信息;这里也是可以使用的
** group_concat 必须与group by函数一起使用**
1.group_concat的语法
group_concat([distinct] 要连接的字段 [order by AEC/DESC 排序字段] [separator ‘分隔符’] )
2.使用例子
多种使用的例子,不清楚可以查看
那么最终的需求转化为sql为:
SELECT
a.id,
b.id as beifen,
GROUP_CONCAT(b.name),
c.name
FROM
kg_rules a
LEFT JOIN kg_facts b ON FIND_IN_SET(b.id, a.reason)
left join kg_facts c on a.result=c.id
GROUP BY a.id;
含义为:以表a(kg_rules )的id为基准,将查询的多条记录,对表b(kg_facts)的name进行拼接,采用默认逗号的方式拼接,当然可以使用separator 来自定义拼接符号。
注意:
- 1.int字段的拼接陷阱
使用group_concat的时候,若是连接起来的字段如果是int型,一定要转为char再拼接。否则在你智行后返回的将不是一个逗号隔开的串,而是byte[]
select group_concat(id) from t_ip — 返回byte[]
select group_concat(Convert(id , char)) from t_dep ----返回逗号隔开的串
- 2.长度陷阱
用了group_concat后,select里如果使用了limit是不起作用的
用group_concat连接字段的时候是有长度限制的,不过可以设置
使用group_concat_max_len系统变量,可以设置允许的最大程度,程序中语法如下,其中val是一个无符号的整数
set [SESSION | GLOBAL] group_concat_max_len = val;
默认是1024。
写在最后
其实,我也是边百度边学习,发现百度是万能的,如果使用group_concat的时候,提示:
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘myself.b.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这个是mysql配置问题,网上也有很多的解决办法