MySQL方法GROUP_CONCAT的应用,多对多联表查询,以A表为主表,通过关联表C查询出B表关联A表任意记录的多条记录的某个字段的合并值
开发中遇到这样的一个需求:用户表为A,角色表为B,用户角色关系通过C表多对多关联,我们需要查询出每一个用户所拥有的角色,以下图的格式显示:
用户ID | 用户姓名 | 拥有角色 |
---|---|---|
1 | 小明1 | 角色1,角色4,角色5,角色6... |
2 | 小明2 | 角色1,角色4,角色5,角色6... |
-- 用户表
CREATE TABLE `sys_user` (
`user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(50) NOT NULL COMMENT '用户姓名',
`user_age` varchar(3) DEFAULT NULL COMMENT '用户年龄',
`creater` varchar(255) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`updater` varchar(50) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='用户表';
-- 角色表
CREATE TABLE `sys_role` (
`role_id` int NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`role_name` varchar(50) NOT NULL COMMENT '角色名称',
`creater` varchar(50) NULL COMMENT '创建人',
`create_time` datetime NULL COMMENT '创建时间',
`updater` varchar(50) NULL COMMENT '更新人',
`update_time` datetime NULL COMMENT '更新时间',
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='角色表';
-- 用户角色关系表
CREATE TABLE `sys_user_role` (
`user_role_id` int NOT NULL AUTO_INCREMENT COMMENT '用户角色关系ID',
`role_id` varchar(50) NOT NULL COMMENT '角色ID',
`user_id` varchar(50) NOT NULL COMMENT '用户ID',
PRIMARY KEY (`user_role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='用户角色关系表';
-- 插入模拟数据 start
create procedure insert8()
begin declare i int default 0;
repeat
INSERT INTO sys_role(role_name,creater,create_time,updater,update_time)select role_name, creater, create_time, updater,update_time from sys_role UNION ALL select '角色','1',NOW(),'1', NOW() from dual ;
INSERT INTO sys_user(user_name,user_age,creater,create_time,updater,update_time) select user_name,user_age,creater,create_time, updater,update_time from sys_user UNION ALL select '小明','10','1',NOW(),'1',NOW() from dual ;
set i=i+1;
until i>8 end repeat; end;
call insert8;
drop procedure if exists insert8;
INSERT INTO sys_user_role (role_id, user_id )
select role_id, user_id from sys_role
INNER JOIN sys_user ;
update sys_user set user_name=REPLACE(user_name,user_name,CONCAT( user_name,user_id) );
update sys_role set role_name=REPLACE(role_name,role_name,CONCAT(role_name,role_id) );
-- 插入模拟数据 end
-- 测试完毕删除数据
drop table sys_user;
drop table sys_role;
drop table sys_user_role;
在解决问题中发现了两种方式可实现该功能
方式一
SELECT su.user_id AS userId,su.user_name AS userName,
(
SELECT GROUP_CONCAT(sr.role_name)
FROM sys_user_role sur
LEFT JOIN sys_role sr ON sr.role_id = sur.role_id
WHERE sur.user_id = su.user_id
) AS roleNames
FROM sys_user su
ORDER BY su.user_id
方式一是把查询c表作为主表,并且left join B表的一个子查询,查出每一个用户id拥有的角色名称拼接结果作为拥有角色字段值的,我们看看其查询性能
查询时间:56.088s 共511条
可见查询22条左右数据需要4秒多,这种速度我们显然是不能接受的,而且需要以拥有小区的名称做模糊查询时候也无从下手。于是后来继续想办法优化,就找到了下面的方式二。
方式二
SELECT su.user_id AS userId, su.user_name AS userName,temp.roleNames
FROM sys_user su
LEFT JOIN (
SELECT sur.user_id, GROUP_CONCAT(sur.role_id) AS roleIds,
GROUP_CONCAT(sr.role_name) AS roleNames
FROM sys_user_role sur
LEFT JOIN sys_role sr ON sr.role_id = sur.role_id
GROUP BY sur.user_id
) temp ON temp.user_id = su.user_id ORDER BY su.user_id
方式二依然有一个查询用户拥有小区名称拼接结果的子查询,只是这个子查询不是直接作为结果字段返回,而是根据用户id为group规则查询出来每一个用户的拥有小区结果字符串,然后作为A表的left join的虚拟表,下面看一下测试结果
查询时间:0.657s 共511条
可见同样查询一万条数据一秒钟都不用,查询速度提高了至少20倍,而且因为是虚拟关联表,可以直接用 temp.roleNames like'%角色1%' 而实现模糊查询。
本文由 小马哥 创作,采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为:
2022/08/19 16:35