树形结构与关系数据库之闭包表详解数据库

闭包表记录了树中所有节点的关系，不仅仅只是直接父子关系，它需要使用2张表，除了节点表本身之外，还需要使用1张表来存储节祖先点和后代节点之间的关系（同时增加一行节点指向自身）,
并且根据需要，可以增加一个字段，表示深度。因此这种方法数据量很多。设计的表结构如下：

create table `node` ( 
`id` int(10) not null, 
`name` varchar(50) default null, 
primary key (`id`) 
) engine=innodb default charset=utf8; 
create table `tree_path` ( 
`anc` int(10) not null comment '祖先节点', 
`des` int(10) not null comment '子孙节点', 
`pc` int(1) default null comment '是否为父子节点 1/0', 
primary key (`anc`,`des`) 
) engine=innodb default charset=utf8; 
-- 示例树 
-- root(0) 
--     | 
--     a(1) 
--     |___a1(2) 
--     |___a2(3) 
--     b(4) 
--     |___b1(5) 
--     |___b2(6) 
--     |___c(7) 
--         |___c1(8) 
--         |___d(9) 
--             |___e(10) 
insert into node(id,name) values (0,'root'); 
insert into node(id,name) values (1,'a'); 
insert into node(id,name) values (2,'a1'); 
insert into node(id,name) values (3,'a2'); 
insert into node(id,name) values (4,'b'); 
insert into node(id,name) values (5,'b1'); 
insert into node(id,name) values (6,'b2'); 
insert into node(id,name) values (7,'c'); 
insert into node(id,name) values (8,'c1'); 
insert into node(id,name) values (9,'d'); 
insert into node(id,name) values (10,'e'); 
insert into tree_path(anc,des,pc) values (0,1,1); 
insert into tree_path(anc,des,pc) values (0,2,0); 
insert into tree_path(anc,des,pc) values (0,3,0); 
insert into tree_path(anc,des,pc) values (0,4,1); 
insert into tree_path(anc,des,pc) values (0,5,0); 
insert into tree_path(anc,des,pc) values (0,6,0); 
insert into tree_path(anc,des,pc) values (0,7,0); 
insert into tree_path(anc,des,pc) values (0,8,0); 
insert into tree_path(anc,des,pc) values (0,9,0); 
insert into tree_path(anc,des,pc) values (0,10,0); 
insert into tree_path(anc,des,pc) values (1,1,0); 
insert into tree_path(anc,des,pc) values (2,2,0); 
insert into tree_path(anc,des,pc) values (3,3,0); 
insert into tree_path(anc,des,pc) values (4,4,0); 
insert into tree_path(anc,des,pc) values (5,5,0); 
insert into tree_path(anc,des,pc) values (6,6,0); 
insert into tree_path(anc,des,pc) values (7,7,0); 
insert into tree_path(anc,des,pc) values (8,8,0); 
insert into tree_path(anc,des,pc) values (9,9,0); 
insert into tree_path(anc,des,pc) values (10,10,0); 
insert into tree_path(anc,des,pc) values (1,2,1); 
insert into tree_path(anc,des,pc) values (1,3,1); 
insert into tree_path(anc,des,pc) values (4,5,1); 
insert into tree_path(anc,des,pc) values (4,6,1); 
insert into tree_path(anc,des,pc) values (4,7,1); 
insert into tree_path(anc,des,pc) values (4,8,0); 
insert into tree_path(anc,des,pc) values (4,9,0); 
insert into tree_path(anc,des,pc) values (4,10,0); 
insert into tree_path(anc,des,pc) values (7,8,1); 
insert into tree_path(anc,des,pc) values (7,9,1); 
insert into tree_path(anc,des,pc) values (7,10,0); 
insert into tree_path(anc,des,pc) values (9,10,1); 
删除子树 
假设要删除子树#7 
delete from tree_path 
where tree_path.des in (select t.des from tree_path t where t.anc=7) 
移动子树 
假设我们要把子树#7从节点#4移动到节点#1 
1.分离子树,删除子树节点与其祖先的关系 
delete from tree_path where tree_path.anc=7 
2.将上一步分离出的子树用笛卡尔积嫁接到#1下 
select 
super.anc, 
sub.des, 
case 
when 
super.anc=1 and sub.des=7 then 1 
else 0 
end pc 
from 
tree_path super 
cross join 
tree_path sub 
where 
super.des=1 and sub.anc=7 
查询树形结构 
xxxxxx