【mysql】GROUP_CONCAT列转行更新FIND_IN_SET多表查询数据

 

前两天遇到这么一种情况，线上有两张表，一张xt_role(角色表)，一张xt_menu(菜单表)。显而易见，一个角色可以有多个菜单，一个菜单也可以给多个角色，多对多关系。正常应该是这样：

但实际情况：

角色表：

菜单表:

ER图:

冗余存储，数据用逗号隔开。页面查询角色（xt_role）展示角色拥有的菜单，数据取的角色表菜单名称(xt_role.menu_nams)，没去关联查询菜单表（xt_menu）

一切都在有条不紊的进行，直到某一天有人打破了这种平衡。修改了菜单名称，角色里面的名称怎么没有变，好家伙，这么多年的项目现在角色里面的名称全错了。。。

方法有很多，下点心思重构，权限模型很简答套用就行，功能也不复杂。再简单些，查出来角色，再去匹配查询菜单，快速解决问题，也不会有人发现。

那我们这个数据已经出现了，虽然数据意义不大，但你发现了这事仿佛就看见它开始在那偷偷闪光。行吧，好机会写写sql 盘了。

三步走，开冰箱，放大象，关冰箱。 

步骤一:  

先将两个表进行关联查询。

 FIND_IN_SET：MySQL 中的函数，‌用于在字符串列表中查找一个字符串的位置

SELECT
	xm.id 菜单表id ,
	xm.`name` 菜单表名称,
	xr.id 角色表 ,
	xr.NAME 角色表名称,
	xr.menu_ids 角色表菜单id ,
	xr.menu_names 角色表菜单名称
FROM
	xt_role xr
	LEFT JOIN xt_menu xm ON FIND_IN_SET( xm.id, xr.menu_ids ) > 0 order by xr.id

结果很清晰，角色2001(管理员)有四个菜单(1001,1003,1003,1004)，角色2002(工单审核员)有两个菜单（1001,1002）

步骤二：

要更新xt_role.menu_names(角色表菜单名称)，[工单审核]为[工单审核名称变了]，那就要先把数据变成一行（工单审核名称变了,已完结工单,工单流程设置,字典配置）才能更新。

GROUP_CONCAT 是 MySQL 中的一个聚合函数，‌用于将多个列的字段值连接为一个字符串结果。‌

SELECT
	xr.id 角色表id,
	GROUP_CONCAT( xm.NAME ORDER BY xm.id ASC SEPARATOR ',' ) 菜单表名称
FROM
	xt_role xr
	LEFT JOIN xt_menu xm ON FIND_IN_SET( xm.id, xr.menu_ids ) > 0 
GROUP BY
	xr.id

好了，现在我们要的结果已经出来了。可以用菜单表名称去刷新角色表了。

步骤三：

第三步就是update关联更新，将步骤二作为一个结果集就可以了。

UPDATE xt_role a 
SET a.menu_names = (
	SELECT
		mname 
	FROM
		(
		SELECT
			xr.id mid,
			GROUP_CONCAT( xm.NAME ORDER BY xm.id ASC SEPARATOR ',' ) mname 
		FROM
			xt_role xr
			LEFT JOIN xt_menu xm ON FIND_IN_SET( xm.id, xr.menu_ids ) > 0 
		GROUP BY
			xr.id 
		) mview 
	WHERE
		mview.mid = a.id 
	)

 更新成功！

啰嗦了。。会玩的就是直接一个sql，这里分开写，像这种多表关联更新、GROUP_CONCAT、FIND_IN_SET函数应该就能掌握了。

附数据库：

DROP TABLE IF EXISTS `xt_menu`;
CREATE TABLE `xt_menu`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'uuid',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  `deleted` bit(1) NULL DEFAULT NULL COMMENT '是否删除',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态：禁用 启用',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `UQ_menu_id`(`id`) USING BTREE,
  INDEX `isdeleted`(`deleted`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色信息表 说明：该表属于字典表，用于存储 角色类型 的信息' ROW_FORMAT = DYNAMIC;

-- ----------------------------
INSERT INTO `xt_menu` VALUES ('1001', '工单审核名称变了', b'0', NULL);
INSERT INTO `xt_menu` VALUES ('1002', '已完结工单', b'0', NULL);
INSERT INTO `xt_menu` VALUES ('1003', '工单流程设置', b'0', NULL);
INSERT INTO `xt_menu` VALUES ('1004', '字典配置', b'0', NULL);
-- ----------------------------
DROP TABLE IF EXISTS `xt_role`;
CREATE TABLE `xt_role`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'uuid',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组名',
  `menu_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '包含菜单的id集合',
  `menu_names` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '包含菜单名称的集合',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态：禁用 启用',
  `deleted` bit(1) NULL DEFAULT NULL COMMENT '是否已删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `UQ_role_id`(`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户组信息表 说明：该表存储用户所属哪一 角色组 的基本信息' ROW_FORMAT = DYNAMIC;
-- ----------------------------
INSERT INTO `xt_role` VALUES ('2001', '管理员', '1001,1002,1003,1004', '工单审核,已完结工单,工单流程设置,字典配置', NULL, b'0');
INSERT INTO `xt_role` VALUES ('2002', '工单审核员', '1001,1002', '工单审核,已完结工单', NULL, b'0');