MySQL树状数据的数据库设计

时间:2017-10-20 08:57

沙龙活动 | 去哪儿、陌陌、ThoughtWorks在自动化运维中的实践!10.28不见不散!

Mysql树状数据的数据库设计

0 树状数据的分类

我们在mysql数据库设计的时候,会遇到一种树状的数据。如公司下面分开数个部门,部门下面又各自分开数个科室,以此形成树状的数据。关于树状的数据,按层级数大致可分为一下两类:

分类 特点
固定数量层级   层级数量固定,每一层级都有各自的意义,如集团-分公司-部门-科室,省-市-区等  
可变数量层级   层级数量不固定,前几层级可能会有特殊含义,但整体在相当大的范围内是浮动的  

前者的优点在于,由于每一层级均有各自含义,数据库的整体设计更为方便,可将某一子节点的不同上级节点均存储在数据库中,同样以某集团为例:

节点code 节点名称 节点层级 父级节点code 1级祖先code 2级祖先cdoe
010000   公司1   1   000000   null   null  
020000   公司2   1   000000   null   null  
010300   制造部   2   010000   010000   null  
010400   品质部   2   010000   010000   null  
010301   前工程制造   3   010300   010000   010300  
010303   组装制造   3   010300   010000   010300  

这样设计的表格冗余较多,但在各种类型查询的时候效率较高.在插入,更新(含子机构,由于业务逻辑特点,机构之间的更新一般是平行转移),删除(含子机构)的时候,由于冗余信息较多,数据操作时所需进行的查询获得也较简单。根据情况,部分冗余信息也考虑删去,如父级节点code,删去一些设计必然会导致部分查询的效率或复杂度提升,这个就需要根据实际情况来取舍平衡了。

缺点有两个:

一个是当层级数量较多的时候,需要存储大量的冗余信息.当然也可以考虑节约方案:1)不存储像n级祖先code这样的字段,但这样就无法利用固定层级设计带来的高效查询特性,是不建议这么做的;2)n级存储不使用code而改用id,这样做主要是在数据迁移或者他表利用的时候不方便。

另一个缺点是,当需求方给出要求,需要对当前机构重新洗牌,变更层级数的时候,你会非常头疼。

后者的优缺点则与前者的优缺点恰好相反,非固定的层级限制非常灵活,而缺点就是查询及数据操作上两方面的不便,这也是本文所要讲述的重点,即如何设计非固定层级的树状数据。

1 非固定层级树状数据的设计方式--祖先路径

树状数据最简单的一种设计方式是,只增加父级id。但这种设计方式给查询后代节点带来了极大的不便,据我所知,尚没有一种不通过函数/存储过程这样循环遍历的查询方式,来一次获取某个节点的所有后代节点或是祖先节点。(此前找到过一个较复杂的查询后代节点的sql,利用的也是祖先节点的id大于后代节点id的特性,但有可能存在通过更新节点使后代节点id大于祖先节点id,所以也不严谨,在此不进行详述)

对于非固定层级树状数据的一种设计方式是:增加祖先路径(ancestor_path),具体可参考下表:

id | 节点名称 | 父id | 祖先路径

--- | --- | --- | --- 

1 | node1 | 0 | 0, 

2 | node2 | 0 | 0, 

3 | node1.1 | 1 | 0,1, 

4 | node1.2 | 1 | 0,1, 

5 | node2.1 | 2 | 0,2, 

6 | node1.1.1 | 3 | 0,1,3, 

7 | node1.1.2 | 3 | 0,1,3, 

8 | node1.2.1 | 4 | 0,1,4, 

9 | node2.1.1 | 5 | 0,2,5, 

实际设计时,还可考虑加入层级这个冗余字段,但我在实际使用的过程中很少用到这个字段。

这样,在加了这个字段之后,任意节点的所有祖先节点信息就都可通过这样一条数据全部获取。

祖先路径的设定具有以下特点:

没有父节点的根节点,父id默认为'0',祖先路径默认为'0,';

每增加的一个子节点,祖先路径都是在要增加的子节点的父节点的祖先路径上增加父id和',';参考的表结构如下:

CREATE TABLE `t_node` ( 

  `node_id` int(11) NOT NULL AUTO_INCREMENT, 

  `node_name` varchar(50) NOT NULL

  `p_id` int(11) NOT NULL

  `ancestor_path` varchar(100) NOT NULL

  PRIMARY KEY (`node_id`) 

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 

2 祖先路径的查询