如何用闭包表优雅地保存树形结构数据

什么是闭包表？

闭包表（Closure Table）是一种解决树形结构存储的分级存储方案，它用一张表记录了所有树节点之间的关系。

如何使用闭包表？

先来棵树

先看看这棵树，根节点是001，之后按每三位来划分，比如最右边的001003002，它可以划为001/003/002，表示根节点的第三个子节点的第二个子节点。

为树设计数据表

这里需要两张表：节点表和关系表。

• 节点表保存节点信息。
• 关系表保存两个节点间的关系。

-- 节点表
create table t_node (
    id int primary key ,
    code varchar(20) comment '节点编码',
    parent_id int comment '父节点id'
);

-- 关系表
create table t_relation (
    ancestor int comment '祖先节点',
    descendant int comment '后代节点',
    distance int comment '节点间的距离',
    primary key (ancestor, descendant)
);

接下来，准备好数据：

-- 节点数据
insert into t_node values (1, '001', null),
                          (2, '001001', 1),
                          (3, '001001001', 2),
                          (4, '001002', 1),
                          (5, '001002001', 4),
                          (6, '001002002', 4),
                          (7, '001003', 1),
                          (8, '001003001', 7),
                          (9, '001003002', 7),
                          (10, '001003003', 7);

这里有特别需要注意的点，那就是节点间的距离：

• 当前节点与当前节点的距离为0。
• 当前节点与其子孙节点的距离大于0。

这需要根据节点间距离，初始化关系表：

insert into t_relation
with recursive n1 as (
    select id as ancestor, id as descendant, 0 as depth from t_node
    union all
    select n1.ancestor, n2.id , n1.depth + 1
    from n1 join t_node n2 on  n1.descendant = n2.parent_id
)
select * from n1;

初始化的结果：

代码示例

这里举几个闭包表的代码示例：

一、找出整棵树的所有节点

select n2.code, n2.id, n2.parent_id
from t_node n1
         left join t_relation r on r.ancestor = n1.id
         left join t_node n2 on n2.id = r.descendant
where n1.code = '001'
order by r.distance;

代码分析：

1. n1表示祖先节点，而where n1.code = '001'就是找出根节点为001的树的所有节点，同样的，如果要找子树数据，如001003子树，那就写成where n1.code = '001003'。
2. n2表示后代节点，以根节点为起点，找到往下找到所有后代节点，故写成select n2.code。

二、找到某节点的路径

尝试找出节点001002002的路径：

select n1.id, n1.code
from t_node n1
    left join t_relation r on r.ancestor = n1.id
    left join t_node n2 on n2.id = r.descendant
where n2.code = '001002002'
order by distance desc;

代码分析：

1. 关系表里记录着祖先节点和后代节点的关系，那么只需要找出后代节点为001002002的关系记录即可。

三、找出表中树的数量

select count(1)
from t_node
where parent_id is null;

代码分析：

1. 根节点的父节点id为空。

四、新增节点

往最左侧的001001001下插入新节点001001001001：

insert into t_node value (11, '001001001001', 3);

插入节点时，只插入节点路径上的关系：

insert into t_relation
with recursive n1 as (
    select id as ancestor, id as descendant, 0 as depth from t_node
    union all
    select n1.ancestor, n2.id , n1.depth + 1
    from n1 join t_node n2 on  n1.descendant = n2.parent_id
)
select * from n1
where descendant = 11;

闭包表的优缺点

闭包表将节点路径上的关系独立出来，并将层级记作为祖先节点和后代节点的距离。

优点：树查询非常方便，不管是查询整棵树的结构，还是查询节点的路径，写起SQL来都十分方便，不再需要递归的写法。

缺点：增加和删除节点相对麻烦；关系表的记录数量会因树的深度而变多。

适用场景：查询较多，纵向结构不深，且增删操作不频繁的场景。

心得体会：闭包表属于典型的空间换时间操作，这种优化手段在计算机领域应用广泛，非常值得学习。