关系型数据库树形结构的设计

        在进行程序设计的时候，树形结构常常被用来表征某些数据的关联关系，如企业上下级部门、商品分类、文件目录等等。这些树状结构需要借助于数据库完成持久化，而关系型数据库并没有对应的原生数据结构去存储查询这种数据结构，因此，设计一种合适的结构进行存储与查询是实现树状结构长久化存储的关键。

        本文会介绍4种关系型数据库树形结构存储设计，并提供使用方法以及各方案的优劣之处，并且以全国行政区划为例，树结构如下图：

---

1 毗邻目录模式

        该方案中，在设计数据表时，给每条记录都加入了指向父数据的记录。

数据表设计如下：{ID,NAME,PARENT}

TABLE_NAME	COLUMN_1	COLUMN_2	COLUMN_3
China	id	name	parent

存储信息如：

id	name	parent
1	四川省	中国
2	武汉市	湖北省
3	青羊区	成都市
...	...	...

这种方案的优点很明显，设计和实现非常直观和方便。

查询SQL语句如下：

①查询某条数据的直接父区域：

例如，查询鄂州市所在的省份名称

SELECT parent FROM "China" WHERE name = '鄂州市'

②查询某数据的上一级父区域：

例如，查询青羊区所在的省份名称

SELECT parent FROM "China" WHERE name
    IN(SELECT parent FROM "China" WHERE name = '青羊区');

③查询某条数据下的所有子数据：

例如，查询成都市下辖的所有区县

SELECT name FROM "China" WHERE parent = '成都市';

④查询某条数据下的所有二级数据：

例如，查询四川省内的所有区县名

SELECT name FROM "China" WHERE parent
	IN(SELECT name FROM "China" WHERE parent = '四川省');

若是要查询多级数据，则需要继续嵌套一层连接语句。

方案的优缺点

• 优点：

• • 查询单个数据所指向的父节点、子节点的难度低；
  • 增加新信息的难度低；

• 缺点：

• • 查询所有子节点和父节点的难度高，若是数据的分级多，则需要写很多层查询语句；
  • 删除操作时，必须写很多额外的代码来进行多次查询，获得后代节点的信息，然后再进行删除；
  • 若是需要修改某分支的信息，也需要进行多层查询来进行修改。

---

2 预排序遍历树算法

2.1 预排序遍历树算法介绍

        预排序遍历树，英文全称为Modified Preorder Tree Traversal，简称MPTT。

        利用这种算法生成的树，在查询时不使用递归查询，有更高的查询效率。

        为了更方便介绍这一算法，以上图为例，在根节点1的左侧写上1，然后沿着这个树继续向下在2的左侧写上2然后继续前进，沿着整个树的边缘给每一个节点都标上左侧和右侧的数组。最后一个数字标在1的右侧的22。至此，一颗预排序算法树就生成了。

2.2 预排序遍历树的生成

        生成代码以Python为例。使用的模块为sqlalchemy_mptt与sqlalchemy。

1.连接数据库（略）

2.定义表：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_mptt.mixins import BaseNestedSets
Base = declarative_base()

class Tree(Base, BaseNestedSets):
    __tablename__ = "type_4"

    id = db.Column(db.Integer,
                   autoincrement=True,
                   primary_key=True,
                   nullable=False
                   )

    name = db.Column(db.Text,
                     nullable=True)

3.生成表，表生成之后，会得到如下表结构：

id

name

lft

rgt

level

tree_id

parent_id

在定义时只定义了主键id和名字name，但是在sqlalchemy_mptt的帮助下，会往数据库里面添加几列：lft,rgt,level,tree_id,parent_id。

• lft:节点左值
• rgt:节点右值
• level:一颗标准树会有高度、深度、层级，根节点的层级是1，子节点的层级是父节点层级加1.
• tree_id:树的id，用来区分数据库中众多树的某一棵树。
• parent_id:父id，节点的父级id。根节点没有父节点，所以值为NULL。

4.生成数据

        预排序算法树的生成主要以逐级生成来构建整棵树，即先在数据库中添加level1的数据，再添加其下的level2的数据，以此类推来完成整棵树的构建，若数据量很大，则构建的耗时也会很长，因此这类树主要用于查询，而不是增删改。

        以生成全国行政区划树为例：

        （其中China表为毗邻目录模式生成的全国行政区划表)

if __name__ == '__main__':
    # 生成树根
    node = Tree()
    node.name = "中国"   # 当不给parent_id赋值时，相当于新建一个树，tree_id递增
    db.session.add(node)
    db.session.commit()
    
    #  生成根的第一级子节点(省直辖市)
    parent_id1 = db.session.query(Tree.id).filter_by(name="中国").first()[0]	
    #  生成根的第一级子节点(省直辖市)
    level1_node = []
    province_name = []
    provinces = China.query.filter_by(parent="中国").all()
    for item in provinces:
        name = item.name
        province_name.append(name)
    for item in provinces:
        node = Tree()
        node.name = item.name
        node.parent_id = parent_id1
        level1_node.append(node)
    db.session.add_all(level1_node)
    db.session.commit()
    
    #  生成第二级子节点(地级市)
    level2_node = []
    for i in range(len(province_name)):
        cities = China.query.filter_by(parent=province_name[i]).all()
        parent_id2 = db.session.query(Tree.id).filter_by(name=province_name[i]).first()[0]
        for item in cities:
            node = Tree()
            node.name = item.name
            node.parent_id = parent_id2
            level2_node.append(node)
    db.session.add_all(level2_node)
    db.session.commit()
    
    #  生成第三级子节点(区县)
    level3_node = []
    city_name = []
    for i in range(len(province_name)):
        cities = China.query.filter_by(parent=province_name[i]).all()
        for unit in cities:
            name = unit.name
            city_name.append(name)
    for i in range(len(city_name)):
        counties = China.query.filter_by(parent=city_name[i]).all()
        parent_id3 = db.session.query(Tree.id).filter_by(name=city_name[i]).first()[0]
        for item in counties:
            node = Tree()
            node.name = item.name
            node.parent_id = parent_id3
            level3_node.append(node)
    db.session.add_all(level3_node)
    db.session.commit()

5.表内容展示：

2.3 预排序遍历树查询

查询将以下表为示例。

①查询某一节点有多少个子节点：

子节点的数量，子节点数量=(右索引-左索引+1)/2-1

②查询某一节点下的所有子节点：

如查询1节点下的所有子节点：

select name from Tree where lft > 1 and rgt < 22;

若是要限制向下查询的层数，则只需再限制level就可以了。

如 查询1节点的1级子节点

select name from Tree where lft > 1 and rgt <22 and level = 2;

③查询某一节点的父节点：

查询某节点的父节点，只需查找左值小于自身左值并且右值大于自身右值，且level高于自身的节点，符合条件的即是父节点。根据level值的不同，可以找到不同层级的父节点。

select name from Tree where lft < self.lft and rgt > self.rgt and level = self.level-1

方案的优缺点

• • 优点：查询效率非常高
  • 缺点：在节点的增加、删除以及数据插入上效率非常低。

---

3 路径枚举法

该方案中，在每一条数据记录后边添加了一列，用于存储节点到该点的完整路径。

数据表设计如下：

TABLE_NAME	COLUMN_1	COLUMN_2	COLUMN_3
China	id	name	path

存储信息如：

id	name	path
1	上海市	中国/
2	成都市	中国/四川省/
3	武昌区	中国/湖北省/武汉市/

该方案很直接的将各个数据的节点信息记录了下来，在查询时可以很快到达所指定的位置。若是做跨级查询，则只需要进行模糊查询即可。

SQL语句如下：

①查询某一节点下的子数据

例如：查询四川省所有的市：

SELECT name FROM "China" WHERE path LIKE '%四川省/';

这种方案在查询时，只需要直接查询path的内容，就可以得到结点下的子数据。若是需要越级查询子数据，则只需进行模糊查询。

如：查询四川省内的所有区、县：

SELECT name FROM "China" WHERE path LIKE '中国/四川省/%/';

②查询某节点的所有父区域：

父区域可直接通过查询数据的path获得，并由此得到其位于树结构中的层数。

方案的优缺点

• • 优点：查询节点所在树中的位置非常方便，查询所有子节点、父节点难度 也很低；
  • 缺点：删除、增加、变更某一节点的信息，需要更新所有子节点的路径字符串。

---

4 ClosureTable

之前的各种方案，都是在原有记录中添加列，以记录父子节点信息关系。而ClosureTable则是新增一张表，用于记录节点直接的关系（父节点、子节点、深度）。

数据表设计如下：

表1 内容表，用于记录所有内容

TABLE_NAME	COLUMN_1	COLUMN_2
China	id	name

存储信息如下：

1	中国
2	四川省
3	成都市
4	青羊区

表2 关系表，用于记录表1所存储内容之间的关系

TABLE_NAME	COLUMN_1	COLUMN_2	COLUMN_3	COLUMN_4
China_con	id(自增ID)	parent_id	child_id	depth(记录节点深度)

存储信息如下：

id	parent_id	child_id	depth
1	1	2	1
2	2	4	2
3	2	3	1

以该表为例，第一条数据意为父节点为中国，子节点为四川省，深度为1；第二条意为父节点为四川省，子节点为青羊区，深度为2；第三条意为父节点为四川省，子节点为成都市，深度为1。

SQL语句如下：
①查询某节点的子节点

select child_id from China_con where parent_id = X and deepth = x;
deepth赋值，则查询指定层级，若不定义deepth，则会查询所有子节点

②查询某节点的父节点

select parent_id from China_con where child_id = X and deepth = x;
deepth赋值，则查询指定层级，若不定义deepth，则会查询所有的父节点

方案的优缺点

• • 优点：查询所有子节点、父节点难度很低；
  • 缺点：删除、增加、变更某一节点的信息，难度较高，并且该方法需要额外表来存储信息，占用额外空间。

 欢迎关注微信公众号  ： 懒GISer