MySQL方法GROUP_CONCAT的应用

IT小马哥

发布于 2022-09-23 11:26:00

6780

发布于 2022-09-23 11:26:00

文章被收录于专栏：Java Tale

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的虚拟表，下面看一下测试结果