Mysql查询父、子节点

一、概述

相信大家在实际的开发工程中，都会遇到需要依据当前节点，查询出其上级节点或下级节点的需求。下面就我在工作过程中的处理方式记录如下，如有片面之处，欢迎批评指正。

二、示例表结构

初始表数据如图：

查看表结构和初始数据脚本

DROP TABLE IF EXISTS `t_cfg_region`;
CREATE TABLE `t_cfg_region` (
  `region_id` bigint NOT NULL AUTO_INCREMENT COMMENT '空间编号',
  `region_name` varchar(100) NOT NULL COMMENT '空间名称',
  `up_region_id` bigint NOT NULL COMMENT '父空间编号',
  `region_level` tinyint NOT NULL COMMENT '级别',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `remark` varchar(512) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`region_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24764 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_cfg_region
-- ----------------------------
INSERT INTO `t_cfg_region` VALUES ('24741', '江苏省', '0', '1', '2023-04-25 09:30:42', '2023-04-25 09:30:42', null);
INSERT INTO `t_cfg_region` VALUES ('24742', '南京', '24741', '2', '2023-04-25 09:34:54', '2023-04-25 09:34:54', null);
INSERT INTO `t_cfg_region` VALUES ('24743', '苏州', '24741', '2', '2023-04-25 09:35:16', '2023-04-25 09:35:16', null);
INSERT INTO `t_cfg_region` VALUES ('24744', '秦淮区', '24742', '3', '2023-04-25 09:37:13', '2023-04-25 09:37:13', null);
INSERT INTO `t_cfg_region` VALUES ('24745', '吴中区', '24743', '3', '2023-04-25 09:38:00', '2023-04-25 09:38:00', null);

三、查父节点

通过秦淮区查询父节点

select
tmpId as region_id
from
(
    SELECT t.* from (
	select
	@r as tmpId,
	(select @r := up_region_id from t_cfg_region where region_id = tmpId) as parentId
	from
	(select @r := 24744,@l := 0) tp,t_cfg_region cg
	where @r != 0
    ) t where t.parentId is not NULL
) tb;

四、查子节点

查询南京的下级节点

select r.region_id,r.region_name,r.up_region_id,r.region_level
from
t_cfg_region as r
inner join
(
		SELECT region_id FROM
		(
		SELECT  t1.region_id,t1.up_region_id,
		IF (find_in_set(up_region_id, @pids) > 0,@pids := concat(@pids, ',', region_id),0) AS ischild
		FROM
		(
		SELECT region_id,up_region_id FROM t_cfg_region t ORDER BY  up_region_id,region_id
		) t1,
		(
		SELECT
		@pids := '24741'
		) t2
		) t3
		WHERE
		ischild != 0
		union all
		select region_id from t_cfg_region where region_id = '24741'
) as tp on r.region_id = tp.region_id