mysql递归查询所有子节点_MySQL递归查询树结构的节点下所有子节点列表

使用函数的方法查询：

1.创建函数get_all_children_node(targetId)

CREATE DEFINER=`root`@`%` FUNCTION `get_all_children_node`(targetId int) RETURNS varchar(1000) CHARSET utf8

BEGIN

declare resultNode varchar(1000); -- 返回的结果字符串

declare parentNode varchar(1000); -- 每次查询的父节点

declare firstNode int;

set resultNode = "";

set parentNode = targetId;

set firstNode = 1;

while parentNode is not null do -- 当父节点不为空时执行

if firstNode=1 then

set firstNode = 2;

else firstNode>1 then

set resultNode = concat(resultNode, ",", parentNode);

select group_concat(id) into parentNode from node where find_in_set(parent_id, parentNode) > 0;

end if;

end while;

return resultNode;

END

2.调用函数。

select * from node where find_in_set(id,get_all_children_node(2));

3.数据库表语句。

CREATE TABLE `node` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`parent_id` bigint(20) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

4.插入数据。

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (1, 0, 'A');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (2, 1, 'B');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (3, 1, 'C');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (4, 2, 'D');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (5, 2, 'E');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (6, 3, 'F');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (7, 4, 'G');

INSERT INTO `node`(`id`, `parent_id`, `name`) VALUES (8, 4, 'H');