MySQL 递归查询 —— 树形数据

1、创建表：

create table `tree` (
  `id` int(11) not null auto_increment comment '主键',
  `parent_id` int(11) not null,
  `name` varchar(10) not null,
  primary key (`id`)
) engine=innodb  default charset=utf8;

2、数据：

insert into `tree` (`id`, `parent_id`, `name`) values('1','0','根节点');
insert into `tree` (`id`, `parent_id`, `name`) values('2','1','一级节点a');
insert into `tree` (`id`, `parent_id`, `name`) values('3','1','一级节点b');
insert into `tree` (`id`, `parent_id`, `name`) values('4','1','一级节点c');
insert into `tree` (`id`, `parent_id`, `name`) values('5','3','二级节点aa');
insert into `tree` (`id`, `parent_id`, `name`) values('6','4','二级节点bb');
insert into `tree` (`id`, `parent_id`, `name`) values('7','2','二级节点cc');
insert into `tree` (`id`, `parent_id`, `name`) values('8','5','三级节点aaa');
insert into `tree` (`id`, `parent_id`, `name`) values('9','5','三级节点bbb');
insert into `tree` (`id`, `parent_id`, `name`) values('10','5','三级节点ccc');
insert into `tree` (`id`, `parent_id`, `name`) values('11','6','三级节点ddd');
insert into `tree` (`id`, `parent_id`, `name`) values('12','11','四级节点aaaa');
insert into `tree` (`id`, `parent_id`, `name`) values('13','10','四级节点bbbb');

3、递归的函数：

drop function if exists getChildList;

delimiter // 
create function `getChildList`(rootId int) returns varchar(1000)
begin 
	declare result varchar(1000);
	declare tempChild varchar(1000);
	set result = '';
	set tempChild =cast(rootId as char);
	while tempChild is not null do
		set result = concat(result,',',tempChild);
		select group_concat(id) into tempChild from tree where find_in_set(parent_id,tempChild)>0;
	end while;
	return result;
end  //

delimiter ;

4、查询

tree 表所有的数据，如下：

4.1、 查询1

select getChildList(1);

结果： ,1,2,3,4,5,6,7,8,9,10,11,12,13

4.2、 查询1

select getChildList(2);

结果：,2,7

4.3、 查询2

select * from tree where find_in_set(id, getChildList(2));

4.4、 查询3

select * from tree where find_in_set(id, getChildList(3));

4.5、 查询4

select * from tree where find_in_set(id, getChildList(4));