mysql id pid 查询指定节点所有子级或父级

通常，在mysql数据表中建立id和pid字段保存树形结构数据。有时，我们需要获取某一节点的所有子级或所有父级。

本文使用WITH RECURSIVE AS递归查询实现。

1.建测试表

CREATE TABLE `tree` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2.插入测试数据

INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (1, 0, '节点A');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (2, 1, '节点A-1');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (3, 1, '节点A-2');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (4, 2, '节点A-1-1');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (5, 2, '节点A-1-2');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (6, 4, '节点A-1-1-1');
INSERT INTO `cms5`.`tree`(`id`, `pid`, `name`) VALUES (7, 3, '节点A-2-1');

3.查询指定节点所有子级

with recursive children as (
select * from tree  where id = 2 -- 指定的节点ID
union all 
select t.* from tree t join children on t.pid=children.id
) 
select * from children    -- 包含指定节点
where children.id <>2  -- 不包含指定节点

查询结果

4.查询指定节点所有父级

with recursive parent as (
select * from tree  where id = 5 -- 指定的节点ID
union all 
select t.* from tree t join parent on t.id=parent.pid
)
select * from parent    -- 包含指定节点
where parent.id <>5  -- 不包含指定节点

查询结果