MySQL-根据父级id查询子级信息

场景：我们经常在实际项目中遇到父子级这样的数据结构，那么需求会要求我们根据当前数据id获取对应子级的所有信息。应该怎么做呢，上干货。

/*
 Navicat Premium Data Transfer

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 80029
 Source Host           : 127.0.0.1:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80029
 File Encoding         : 65001

 Date: 27/02/2023 17:30:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

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

-- ----------------------------
-- 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);
INSERT INTO `t_art_comment` VALUES ('1125249334896541699', '1125225083753783298', '1125226668273754114', NULL, '这是三级评论内容', '1125249334896541698', NULL, 0, 0, '这是三级备注信息', '2019-05-06 12:02:02', '2019-05-06 12:02:02', NULL, NULL, 0);

SET FOREIGN_KEY_CHECKS = 1;

根据父级customerCode查询所有子级sql：

select * from t_art_comment;

-- 查询所有的子级
SELECT
	ID.LEVEL,
	DATA.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( code ) FROM t_art_comment WHERE FIND_IN_SET( parentCode, @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.code, ID._ids ) 
ORDER BY
	LEVEL,
	code;

-- 	方案二
SELECT
	LEVEL,
	code ,
	parentCode
FROM
	(
	SELECT
		t1.code,
		t1.parentCode,
	IF
		( FIND_IN_SET( parentCode, @pids ) > 0, @pids := CONCAT( @pids, ',', code ), 0 ) AS ischild,
		@l := @l + 1 AS LEVEL
	FROM
		( SELECT code, parentCode FROM t_art_comment t ORDER BY parentCode, code ) t1,
		( SELECT @pids := '1125246521093177345', @l := 0 ) t2 
	) t3 
WHERE
	ischild != 0 
	OR code = '1125246521093177345';

根据当前customerCode查询所有父级sql：

SELECT
	T2.code,
	T2.parentCode 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parentCode FROM t_art_comment WHERE code = _id ) AS parentode,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := '1125249334896541699', @l := 0 ) vars,
		t_art_comment h 
	WHERE
		@r <> 0 
	) T1
	JOIN t_art_comment T2 ON T1._id = T2.code 
ORDER BY
	T1.lvl DESC

说明：

1. code 编码

2. parentCode 父级编码

3. Level 层级

4. 表：t_art_comment