MySQL--多表连接查询

前几天在项目中遇到一个关于MySQL多表连接查询的问题,由于项目表结构设计已定,所以只能用多表连接查询完成。


一、业务场景是这样的:

1、角色: creater、owner、author

2、权限:assign owner、assign author

3、对应关系:  creater  -->  assign owner / assign author  ;   owner  --> assign owner / assign author  ;   author  --> assign author

4、主题业务:  creater 上传一份protocol(主文档),主文档下面有多个section(子文档),每个子文档有一个owner、多个author,在点击主文档连接的时候,就要去加载所有的子文档,以及子文档上对应的owner、author、以及当前登录者对这些子文档的操作权限。


二、技术: node js  + jquery + bootstrap ,  单页面显示。


三、具体实现:

1、数据库设计:

  user(id, name, email) -- 用户表

  role (id, name)       -- 角色表

  activity(id, name, desc)    -- 权限表

  role_activity(roldId, activityId) -- 角色-权限关系表

  document(id, type, name, status) -- 文档表,type区分是protocol还是section

  assets(protocolId, sectionId, sequance, sectionName) -- protocol和section关联表

  assignment(docId, roleId, userId) -- 权限分配表

2、简要思路:

2.1、初始化页面的时候,加载所有的protocol,同时加载当前登录用户对每个protocol的权限。

2.2、点击每个protocol的时候,根据protocolID去加载protocol下所有的section、section上的owner和authors、当前登录用户对每个section的权限列表


3、sql语句,因为主要描述SQL语句,所以其它功能不加描述。

        3.1、初始化protocol列表:

select
	d.id, ...
	group_concat(ac.name) as activityName
	group_concat(ac.desc) as activityDesc
from document as d 
left join assignment as ag on d.id = ag.docId and d.type=protocol and ag.userId={login_user_id} and ag.roleId={creater_id}
left join role as r on ag.roleId = r.id 
left join role_activity as ra on ur.id=ra.roleId 
left join activity as ac on ra.activityId=as.id 
group by d.id


这里使用left join , 并且进行了聚合查询,当使用聚合查询的时候,select中的字段要么出现在group by中,要么是聚合字段。但是当group by后面的字段时候主键、 或是唯一能确定一条记录的,比如ID,那么这个ID所在的表的其它字段如果出现在select中,也可以不出现在group by后面。

在select中的字段要么出现在group by中,要么是掘和字段,这个问题在5.7版本以后,在window版本中已经不做限制了,在Mac上默认安装的@@sql_mode中包括了 full_group_by, 所以必须按照严格的聚合操作才不会报错。

另外在MySQL中,如果有多个join操作,是按照顺序来执行的,每个步骤都会生成虚拟表,所以紧跟在on后面的条件,仅对当前的虚拟表有效。


3.2、加载protocol下所有的section、section的owner和authors、以及当前登录这对每个section的权限

select
	a.protocolId,
	a.sectionId, 
	a.sectionName,
	a.sequence,
	uowner.id
	uowner.name as owner,
	group_concat(distinct uauthor.name) as authors
	group_concat(distinct ac.name) as activityName
	group_concat(distinct ac.desc) as activityDesc
from assets as a 
left join assignment as ag on a.protocolId={protocol_id} and a.sectionId = ag.docId and ag.userId={login_user_id}
left join role as r on ag.roleId = r.id and r.id={login_user_id}
left join role_activity as ra on ur.id=ra.roleId 
left join activity as ac on ra.activityId=as.id 
left join assignment as agowner on a.protocolId={protocol_id} and a.sectionId = agowner.docId and agowner.roleId={owner_id}
left join user as uowner on uowner.id = agowner.userId
left join assignment as agauthor on a.protocolId={protocol_id} and a.sectionId = agauthor.docId and agowner.roleId={author_id}
left join user as uauthor on uauthor.id = agauthor.userId
group a.id, uowner.id

这里要注意的事情是, group_concat(discinct  {colunm_name}) 加了 distinct 关键字,因为这里assignment做了三次关联,如果不对去重,多个section给了同一个人权限,就会出现重复的权限。


3.3、连接查询的限制: MySQL默认支持链接表的数量上限是60,但是会严重影响性能,粗略实验统计,一个SQL语句中连接表数量不超过16张为佳。

 

3.4、这里用到了较多的连接查询,每次请求都去数据库编译、执行一次SQL,也会有影响,所以把上面两句SQL写成storage_procedure



后续如发现问题或优化,会更新这里的内容。各位同仁,如发现问题,也可以一起探讨交流。新手上路,请多指教。




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值