搜索

@@mysql 父子关系无限查询

https://www.jianshu.com/p/8e6a6804170a/

MySQL中单句实现无限层次父子关系查询

参考链接https://blog.csdn.net/zjcxc/article/details/78819656

一、创建表并添加数据：

DROP TABLE IF EXISTS `t_art_comment`;
CREATE TABLE `t_art_comment` (
  `id` varchar(50) NOT NULL COMMENT 'id',
  `member_id` varchar(50) DEFAULT '0' COMMENT '会员ID',
  `art_id` varchar(50) DEFAULT '0' COMMENT '帖子主键',
  `nick` varchar(50) DEFAULT NULL COMMENT '用户昵称（暂不使用）',
  `content` text COMMENT '内容',
  `pid` varchar(50) DEFAULT '0' COMMENT '父级评论（如果是0，表示该评论为一级评论）',
  `ding` int(4) DEFAULT NULL COMMENT '优质 1.普通、2.优质',
  `is_read` tinyint(4) DEFAULT '0' COMMENT '是否已读，0未读，1已读',
  `like_num` int(10) DEFAULT '0' COMMENT '点赞量',
  `remark` varchar(150) DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `operator_type` tinyint(4) DEFAULT NULL COMMENT '操作人类型（0、系统自动操作，1、平台人员操作，2、商家人员操作，3、会员操作）',
  `operator_id` varchar(50) DEFAULT NULL COMMENT '操作人id（根据操作人类型会对应不同的表记录）',
  `is_deleted` tinyint(4) DEFAULT '0' COMMENT '是否已删除（逻辑删除，1、已删除，0、未删除，默认0）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='社区评论回复表';

-- ----------------------------
-- Records of t_art_comment
-- ----------------------------
INSERT INTO `t_art_comment` VALUES ('1125246521093177345', '1125225083753783298', '1125226668273754114', null, '这是一级评论内容', '0', null, '0', '0', '这是一级备注信息', '2019-05-06 11:50:51', '2019-05-06 11:50:51', null, null, '0');
INSERT INTO `t_art_comment` VALUES ('1125246613237841921', '1125225083753783298', '1125226668273754114', null, '这是一级评论内容', '0', null, '0', '0', '这是一级备注信息', '2019-05-06 11:51:13', '2019-05-06 11:51:13', null, null, '0');
INSERT INTO `t_art_comment` VALUES ('1125246617801244673', '1125225083753783298', '1125226668273754114', null, '这是一级评论内容', '0', null, '0', '0', '这是一级备注信息', '2019-05-06 11:51:14', '2019-05-06 11:51:14', null, null, '0');
INSERT INTO `t_art_comment` VALUES ('1125246990465155073', '1125225083753783298', '1125226668273754114', null, '这是二级评论内容', '1125246521093177345', null, '0', '0', '这是二级备注信息', '2019-05-06 11:52:43', '2019-05-06 11:52:43', null, null, '0');
INSERT INTO `t_art_comment` VALUES ('1125249334896541698', '1125225083753783298', '1125226668273754114', null, '这是二级评论内容', '1125246521093177345', null, '0', '0', '这是二级备注信息', '2019-05-06 12:02:02', '2019-05-06 12:02:02', null, null, '0');

查询 id = 1125246613237841921 的所有父级

SELECT 
@id as _id, 
( SELECT @id := pid 
FROM t_art_comment 
WHERE id = @id 
) as _pid, 
@l := @l+1 as level 
FROM t_art_comment, 
(SELECT @id := '1125246613237841921', @l := 0 ) b 
WHERE @id > 0 
) ID, t_art_comment DATA 
WHERE ID._id = DATA.id 
ORDER BY level;

查询 id=1125246521093177345 的所有子级

SELECT ID.level, DATA.* FROM( 
SELECT 
@ids as _ids, 
( SELECT @ids := GROUP_CONCAT(id) 
FROM t_art_comment 
WHERE FIND_IN_SET(pid, @ids) 
) as cids, 
@l := @l+1 as level 
FROM t_art_comment, 
(SELECT @ids :='1125246521093177345', @l := 0 ) b 
WHERE @ids IS NOT NULL 
) id, t_art_comment DATA 
WHERE FIND_IN_SET(DATA.id, ID._ids) 
ORDER BY level, id