树形结构的数据在项目开发中比较常见，比如比较典型的是论坛主题留言。
每一个主题（节点）可以有n个留言（子节点）。这些留言又可以有自己的留言。因此这种结构就是一颗树。本文讨论的是数据库中如何存储这种树形结构。
假设有如下一棵树：
方法一
注意：本例中的数据库是SQLite，因此SQL语句只对SQLite有效，其他数据库可以参考该写法。
要存储于数据库中，最简单直接的方法，就是存储每个元素的父节点ID。
暂且把这种方法命名依赖父节点法，因此表结构设计如下：
存储的数据如下格式：
这种结构下，如果查询某一个节点的直接子节点，十分容易，比如要查询D节点的子节点。
|
1
|
select
*
from
tree1
where
parentid=4
|
如果要插入某个节点，比如在D节点下，再次插入一个M节点。
只需要如下SQL：
|
1
|
INSERT
INTO
tree1 (value,parentid)
VALUES
(
'M'
,4);
|
这种结构在查找某个节点的所有子节点，就稍显复杂，无论是SELECT还是DELETE都可能涉及到获取所有子节点的问题。比如要删除一个节点并且该节点的子节点也要全部删除，那么首先要获得所有子节点的ID，因为子节点并不只是直接子节点，还可能包含子节点的子节点。比如删除D节点及其子节点，必须先查出D节点下的所有子节点，然后再做删除，SQL如下：
|
1
2
3
4
|
select
nodeid
from
tree1
where
parentid=4
--返回8,9
select
nodeid
from
tree1
where
parentid
in
(8,9)
--返回10,11,12
select
nodeid
from
tree1
where
parentid
in
(10,11,12)
--返回空
delete
from
tree1
where
nodeid
in
(4,8,9,10,11,12)
|
如果是只删除D节点，对于其它节点不做删除而是做提升，那么必须先修改子节点的parentid，然后才能删除D节点。
正如上面演示的，对于这种依赖父节点法，最大的缺点就是无法直接获得某个节点的所有子节点。因此如果要select所有的子节点，需要繁琐的步骤，这不利于做聚合操作。
对于某些数据库产品，支持递归查询语句的，比如微软的SQL Server，可以使用CTE技术实现递归查询。比如，要查询D节点的所有子节点。只需要如下语句：
|
1
2
3
4
5
6
|
WITH
tmp
AS
(
SELECT
*
FROM
Tree1
WHERE
nodeid = 4
UNION
ALL
SELECT
a.*
FROM
Tree1
AS
a,tmp
AS
b
WHERE
a.parentid = b. nodeid
)
SELECT
*
FROM
tmp
|
但是对于那些不支持递归查询的数据库来说，实现起来就比较复杂了。
方法二
还有一种比较土的方法，就是存储路径。暂且命名为路径枚举法。
这种方法，将存储根结点到每个节点的路径。
这种数据结构，可以一眼就看出子节点的深度。
如果要查询某个节点下的子节点，只需要根据path的路径去匹配，比如要查询D节点下的所有子节点。
|
1
|
select
*
from
tree2
where
path
like
'%/4/%'
|
或者出于效率考虑，直接写成
|
1
|
select
*
from
tree2
where
path
like
'1/4/%'
|
如果要做聚合操作，也很容易，比如查询D节点下一共有多少个节点。
select count(*) from tree2 where path like '1/4/%';
要插入一个节点，则稍微麻烦点。要插入自己，然后查出父节点的Path，并且把自己生成的ID更新到path中去。比如，要在L节点后面插入M节点。
首先插入自己M，然后得到一个nodeid比如nodeid=13，然后M要插入到L后面，因此，查出L的path为1/4/8/12/,因此update M的path为1/4/8/12/13
|
1
2
3
4
5
|
update
tree2
set
path=(
select
path
from
tree2
where
nodeid=12)
--此处开始拼接
||last_insert_rowid()||
'/'
where
nodeid= last_insert_rowid();
|
这种方法有一个明显的缺点就是path字段的长度是有限的，这意味着，不能无限制的增加节点深度。因此这种方法适用于存储小型的树结构。
方法三
下面介绍一种方法，称之为闭包表。
该方法记录了树中所有节点的关系，不仅仅只是直接父子关系，它需要使用2张表，除了节点表本身之外，还需要使用1张表来存储节祖先点和后代节点之间的关系（同时增加一行节点指向自身）,并且根据需要，可以增加一个字段，表示深度。因此这种方法数据量很多。设计的表结构如下：
Tree3表：
NodeRelation表：
如例子中的树，插入的数据如下：
Tree3表的数据
NodeRelation表的数据
可以看到，NodeRelation表的数据量很多。但是查询非常方便。比如，要查询D节点的子元素
只需要
|
1
|
select
*
from
NodeRelation
where
ancestor=4;
|
要查询节点D的直接子节点，则加上depth=1
|
1
|
select
*
from
NodeRelation
where
ancestor=4
and
depth=1;
|
要查询节点J的所有父节点，SQL：
|
1
|
select
*
from
NodeRelation
where
descendant=10;
|
如果是插入一个新的节点，比如在L节点后添加子节点M,则插入的节点除了M自身外，还有对应的节点关系。即还有哪些节点和新插入的M节点有后代关系。这个其实很简单，只要和L节点有后代关系的，和M节点必定会有后代关系，并且和L节点深度为X的和M节点的深度必定为X+1。因此，在插入M节点后，找出L节点为后代的那些节点作为和M节点之间有后代关系，插入到数据表。
|
1
2
3
4
5
6
7
|
INSERT
INTO
tree3 (value)
VALUES
(
'M'
);
--插入节点
INSERT
INTO
NodeRelation(ancestor,descendant,depth)
select
n.ancestor,last_insert_rowid(),n.depth+1
--此处深度+1作为和M节点的深度
from
NodeRelation n
where
n.descendant=12
Union
ALL
select
last_insert_rowid() ,last_insert_rowid(),0
--加上自身
|
在某些并不需要使用深度的情况下，甚至可以不需要depth字段。
如果要删除某个节点也很容易，比如，要删除节点D，这种情况下，除了删除tree3表中的D节点外，还需要删除NodeRelation表中的关系。
首先以D节点为后代的关系要删除，同时以D节点的后代为后代的这些关系也要删除：
|
1
2
|
delete
from
NodeRelation
where
descendant
in
(
select
descendant
from
NodeRelation
where
ancestor=4 )；
--查询以D节点为祖先的那些节点，即D节点的后代。
|
这种删除方法，虽然彻底，但是它也删除了D节点和它原本的子节点的关系。
如果只是想割裂D节点和A节点的关系，而对于它原有的子节点的关系予以保留，则需要加入限定条件。
限制要删除的关系的祖先不以D为祖先，即如果这个关系以D为祖先的，则不用删除。因此把上面的SQL加上条件。
|
1
2
3
|
delete
from
NodeRelation
where
descendant
in
(
select
descendant
from
NodeRelation
where
ancestor=4 )；
--查询以D节点为祖先的那些节点，即D节点的后代。
and
ancestor
not
in
(
select
descendant
from
NodeRelation
where
ancestor =4 )
|
上面的SQL用文字描述就是，查询出D节点的后代，如果一个关系的祖先不属于D节点的后代，并且这个关系的后代属于D节点的后代，就删除它。
这样的删除，保留了D节点自身子节点的关系，如上面的例子，实际上删除的节点关系为：
如果要删除节点H，则为
总结：
上面主要讲了3种方式，各有优点缺点。可以根据实际需要，选择合适的数据模型。
---------------------------------
参考资料 《SQL Antipatterns》
本文转自cnn23711151CTO博客，原文链接：http://blog.51cto.com/cnn237111/1226911 ，如需转载请自行联系原作者
