一、创建表并添加数据：

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