SQL反模式（二 单纯的树）

反模式

1 最常用来存储树的结构,存储上级节点id 属于一种反模式

采用上级节点id的形式来保存一棵树（邻接表形式），如开发中常用的路由（菜单）表，但是这种使用方法写的sql只能查一定层级的树，而树的特点就是无限的，则一次取出所有数据，在应用程序中自顶向下地重新构造这棵树（数据量不大且有性能问题），作者在书中用了评论回复的案例来讲解树。

2 使用邻接表来维护树

使用这种反模式来维护树，一些操作是非常方便，比如增加叶子节点，修改一个节点和一颗子树的位置，然而从一棵树中删除一个节点会变得比较复杂，比如删除一颗子树，你不得不执行多次查询来找到所有的后代节点，然后逐个从最低级别开始删除这些节点，以满足外键完整性，也可以使用 ON DELETE CASCADE修饰符的外键来自动完成这些操作，使用邻接表时需要多步操作才能完成的查询例子，不得不写很多代码，而数据库涉及本身就可以做到简单和高效。

3 解决方法

1 路径枚举
2 嵌套集
3 闭包
以空间换时间，将存在任何具有 祖先-后代的关系存在一张表（treepaths）中，同时还增加一行节点指向自己。

create table comments(
comment_id serial PRIMARY key,
bug_id BIGINT UNSIGNED NOT NUll,
anthor bigINT UNSIGNED NOT NULL,
comment text not null)

create table treepaths(
ancestor BIGINT UNSIGNED NOT NULL,
descendant BIGINT UNSIGNED NOT NULL,
PRIMARY KEY(ancestor,descendant)
);

查询树
select c.* from comments c
join treepaths t on c.comment_id=t.descendant
where t.ancestor=1
查询父节点
select t.ancestor,t.descendant from Treepaths as t
where t.descendant =5
添加新节点
insert into treepaths(ancestor,descendant)
select t.ancestor,8 from Treepaths as t
where t.descendant =5
union all
select 8,8