MySQL闭包表

测试数据来自于博客：https://www.cnblogs.com/linjiqin/p/9523033.html

闭包表是为了解决在数据库中存储树形数据而引出的解决方案。通常闭包表会有两个表，一个记录节点信息，一个记录关系。

举例如下：

# 示例树
# root(0)
#   |
#   |____a(1)
#   |    |____a1(2)
#   |    |____a2(3)
#   |
#   |____b(4)
#        |____b1(5)
#        |____b2(6)
#        |____c(7)
#             |____c1(8)
#             |____d(9)
#                  |____e(10)

create table node
(
    id   int         not null primary key,
    name varchar(50) null
)

create table node_relation
(
    ancestor   int     not null comment '祖先节点',
    descendant int     not null comment '子孙节点',
    distance   tinyint null comment 'distance, 也就是从祖先到子孙节点的距离',
    constraint unique_index
        unique (ancestor, descendant)
)

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 node_relation (ancestor, descendant, distance) VALUES (0, 1, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 2, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 3, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 4, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 5, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 6, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 7, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 8, 3);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 9, 3);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (0, 10, 4);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (1, 1, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (1, 2, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (1, 3, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (2, 2, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (3, 3, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 4, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 5, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 6, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 7, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 8, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 9, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (4, 10, 3);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (5, 5, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (6, 6, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (7, 7, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (7, 8, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (7, 9, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (7, 10, 2);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (8, 8, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (9, 9, 0);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (9, 10, 1);
INSERT INTO node_relation (ancestor, descendant, distance) VALUES (10, 10, 0);

1，如果想要获取某个节点以及节点下的所有节点信息：

select a.id, a.name
from node a
         join node_relation b on a.id = b.descendant
where b.ancestor = 4;

输出：
id      name
4	b
5	b1
6	b2
7	c
8	c1
9	d
10	e 

2，获取所有的内部节点，也就是非叶子节点：

select distinctrow b.ancestor as id, a.name
from node_relation b
         right join node a on a.id = b.ancestor
where distance != 0;
输出：
id      name
0	root
1	a
4	b
7	c
9	d

3，查询所有叶子节点：

select *
from node
where id not in (select distinct ancestor from node_relation where distance != 0);
输出：
id    name
2	a1
3	a2
5	b1
6	b2
8	c1
10	e

4，整个树的结构为：

select *
from node_relation
where distance = 1;
输出：
ancestor descendant distance
0	1	1
0	4	1
1	2	1
1	3	1
4	5	1
4	6	1
4	7	1
7	8	1
7	9	1
9	10	1

操作起来的确是很方便。为了在表中存储树，其实还有别的做法，比如路径枚举（知道用这样的设计，但是不知道叫这个名字）这是在项目中用到的。

这里推荐一篇博客，很好的解释了这两种的区别：https://www.cnblogs.com/boboxing/p/7055251.html