【MySQL疑难杂症】如何将树形结构存储在数据库中（方案三 Closure Table）

　　一个表用来存储关系：

<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> emp_relations(
root_id </span><span style="color: #0000ff">INT</span><span style="color: #000000">,
depth </span><span style="color: #0000ff">INT</span><span style="color: #000000">,
is_leaf </span><span style="color: #0000ff">TINYINT</span>(<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
node_id </span><span style="color: #0000ff">INT</span><span style="color: #000000">
)</span>

　　这里的root_id用来存放以其为根节点的路径，node_id表示节点处的eid，depth表示根节点到该节点的深度，is_leaf表示该节点是否为叶子节点。

　　接下来插入数据：

　　可以看出，这个关系表有点大，我们先来看看查询效果如何：

　　1.查询小天的直接上司。

　　这里只需要在关系表中找到node_id为小天id，depth为1的根节点id即可。

<span style="color: #0000ff">SELECT</span> e2.ename BOSS <span style="color: #0000ff">FROM</span><span style="color: #000000"> employees3 e1,employees3 e2,emp_relations rel 
</span><span style="color: #0000ff">WHERE</span> e1.ename<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">小天</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> rel.node_id<span style="color: #808080">=</span>e1.eid <span style="color: #808080">AND</span> rel.depth<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">AND</span> e2.eid<span style="color: #808080">=</span>rel.root_id

　　查询结果如下：

　　2.查询老宋管理下的直属员工。

　　思路差不多，只要查询root_id为老宋eid且深度为1的node_id即为其直接下属员工id

<span style="color: #0000ff">SELECT</span> e1.eid,e1.ename 直接下属 <span style="color: #0000ff">FROM</span><span style="color: #000000"> employees3 e1,employees3 e2,emp_relations rel 
</span><span style="color: #0000ff">WHERE</span> e2.ename<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">老宋</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> rel.root_id<span style="color: #808080">=</span>e2.eid <span style="color: #808080">AND</span> rel.depth<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">AND</span> e1.eid<span style="color: #808080">=</span>rel.node_id

　　查询结果如下：

　　3.查询小天的所有上司。

　　只要在关系表中找到node_id为小天eid且depth大于0的root_id即可

<span style="color: #0000ff">SELECT</span> e2.eid,e2.ename 上司 <span style="color: #0000ff">FROM</span><span style="color: #000000"> employees3 e1,employees3 e2,emp_relations rel 
</span><span style="color: #0000ff">WHERE</span> e1.ename<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">小天</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> rel.node_id<span style="color: #808080">=</span>e1.eid <span style="color: #808080">AND</span> rel.depth<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">0</span> <span style="color: #808080">AND</span> e2.eid<span style="color: #808080">=</span>rel.root_id

　　查询结果如下：

　　4.查询老王管理的所有员工。

　　只要在关系表中查找root_id为老王eid，depth大于0的node_id即可

<span style="color: #0000ff">SELECT</span> e1.eid,e1.ename 下属 <span style="color: #0000ff">FROM</span><span style="color: #000000"> employees3 e1,employees3 e2,emp_relations rel 
</span><span style="color: #0000ff">WHERE</span> e2.ename<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">老王</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> rel.root_id<span style="color: #808080">=</span>e2.eid <span style="color: #808080">AND</span> rel.depth<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">0</span> <span style="color: #808080">AND</span> e1.eid<span style="color: #808080">=</span>rel.node_id

　　查询结果如下：

　　我们可以发现，这四个查询的复杂程度是一样的，这就是这种存储方式的优点，而且可以让另一张表只存储跟节点紧密相关的信息，看起来更简洁。但缺点也显而易见，关系表会很庞大，当层次很深，结构很庞大的时候，关系表数据的增长会越来越快，相当于用空间效率来换取了查找上的时间效率。

　　至此，树形结构在数据库中存储的三种方式就介绍完了，接下来对比一下三种方法：

　　方案一：Adjacency List

　　优点：只存储上级id，存储数据少，结构类似于单链表，在查询相邻节点的时候很方便。添加删除节点都比较简单。

　　缺点：查询多级结构的时候会显得力不从心。

　　适用场合：对多级查询需求不大的场景比较适用。

　　方案二：Path Enumeration

　　优点：查询多级结构的时候比较方便。查询相邻节点时也比较ok。增加或者删除节点的时候比较简单。

　　缺点：需要存储的path值可能会很大，甚至超过设置的最大值范围，理论上无法无限扩张。

　　适用场合：结构相对简单的场景比较适合。

　　方案三：Closure Table

　　优点：在查询树形结构的任意关系时都很方便。

　　缺点：需要存储的数据量比较多，索引表需要的空间比较大，增加和删除节点相对麻烦。

　　适用场合：纵向结构不是很深，增删操作不频繁的场景比较适用。

　　当然，也可以再自己创新出其他更好的存储方案，如果有更好的想法，欢迎提出交流。

　　至此三种方案全部介绍完毕，欢迎大家继续关注。

【MySQL疑难杂症】如何将树形结构存储在数据库中（方案三 Closure Table）

标签：插入数据   employee   信息   交流   对比   疑难   src   sql   log