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






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