中国领先的IT技术网站
|
|

MySQL树状数据的数据库设计

我们在mysql数据库设计的时候,会遇到一种树状的数据。如公司下面分开数个部门,部门下面又各自分开数个科室,以此形成树状的数据。

作者:杨意社来源:杨意社的博客|2017-09-26 13:35

开发者大赛路演 | 12月16日,技术创新,北京不见不散


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. 一个是当层级数量较多的时候,需要存储大量的冗余信息.当然也可以考虑节约方案:1)不存储像n级祖先code这样的字段,但这样就无法利用固定层级设计带来的高效查询特性,是不建议这么做的;2)n级存储不使用code而改用id,这样做主要是在数据迁移或者他表利用的时候不方便。
  2. 另一个缺点是,当需求方给出要求,需要对当前机构重新洗牌,变更层级数的时候,你会非常头疼。

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

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

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

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

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

  1. --- | --- | --- | --- 
  2. 1 | node1 | 0 | 0, 
  3. 2 | node2 | 0 | 0, 
  4. 3 | node1.1 | 1 | 0,1, 
  5. 4 | node1.2 | 1 | 0,1, 
  6. 5 | node2.1 | 2 | 0,2, 
  7. 6 | node1.1.1 | 3 | 0,1,3, 
  8. 7 | node1.1.2 | 3 | 0,1,3, 
  9. 8 | node1.2.1 | 4 | 0,1,4, 
  10. 9 | node2.1.1 | 5 | 0,2,5, 

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

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

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

  1. 没有父节点的根节点,父id默认为'0',祖先路径默认为'0,';
  2. 每增加的一个子节点,祖先路径都是在要增加的子节点的父节点的祖先路径上增加父id和',';参考的表结构如下:
  1. CREATE TABLE `t_node` ( 
  2.   `node_id` int(11) NOT NULL AUTO_INCREMENT, 
  3.   `node_name` varchar(50) NOT NULL
  4.   `p_id` int(11) NOT NULL
  5.   `ancestor_path` varchar(100) NOT NULL
  6.   PRIMARY KEY (`node_id`) 
  7. ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 

2 祖先路径的查询

设计的树节点的查询,主要有两种,一种是查询某个节点的所有后代节点(与查询祖先节点为某个已知节点的所有节点集合是一个意思),这种也是最常用的一种查询;一种是查询某个节点的所有祖先节点,这种不太常用。

   1. 查询某个节点的所有后代节点 参考示例如下:

  1. SELECT * FROM t_node  
  2. WHERE ancestor_path LIKE CONCAT( 
  3. (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), 
  4. ?,',%'

以上sql即是对id为?的某个节点的所有后代节点的查询方式一,还可使用以下方式:

  1. SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT('%,',?,',%'

查询方式二的方式更加简洁。但考虑到查询方式一只用到了右模糊查询,可以使用索引,所以还是建议使用方式一进行查询。

需要注意的是以上两种方式查到的节点集合都不包含子节点,如果需要包含该节点的信息,还需要加上

  1. ... OR node_id=? 

    2. 查询某个节点的所有祖先节点

  1. SELECT * FROM t_node WHERE node_id REGEXP  
  2. CONCAT('^('
  3. REPLACE((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',','|'), 
  4. '0)$'

以上方式查询祖先节点的效率确实不是很高,但考虑到该查询本身并不用,便姑且用之了。

3 祖先路径的插入,更新和删除

分别分插入,更新和删除来讲:

   1. 插入

  1. INSERT INTO t_node (node_name,p_id,ancestor_path) 
  2. VALUE('node?',?, 
  3. CONCAT((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt),?,',')) 

sql中的3个?均为要加入父节点的id。

   2. 更新(含子节点)

如果更新的时候,父节点的位置没有变化,则不必考虑太多;

如果需要更新所在父节点,相比于最简单的树节点设计模式,增加祖先路径的方式除了在更新当前节点本身的父id外,还需要修改对应的祖先路径,这个步骤通过存储过程实现,是一种比较简单的方式,在此不再详述。仅对不使用存储过程的方式进行描述。

  1. UPDATE t_node SET p_id=?_p WHERE node_id=?_n; 
  2. UPDATE t_node SET ancestor_path=CONCAT((SELECT * FROM(SELECT ancestor_path FROM t_node WHERE node_id=?_p)wt2),?_p,',',SUBSTR(ancestor_path,LENGTH(@PPath)+1)) 
  3. WHERE ancestor_path LIKE CONCAT((SELECT * FROM (SELECT @ppath:=ancestor_path FROM t_node WHERE node_id=?_n)wt),?_n,',%'
  4. OR node_id=?_n ; 

其中?_n表示要修改的节点的id,?_p表示要修改的节点的新父节点的id。

注:使用该sql一定要先更新子节点的祖先路径,再更新本节点的祖先路径,如果是使用存储过程的话就可以无视这一点了。

   3. 删除(含子节点)

  1. DELETE FROM t_node  
  2. WHERE ancestor_path LIKE CONCAT( 
  3. (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), 
  4. ?,',%'

删除的核心在于where,和获取所有后代节点的where可以说是完全一样的。

同样要主要先删除所有后代节点,再删除本节点;

4 祖先路径的重置

有可能你此前的某个数据库表格没有使用过祖先路径,但已经积累了一定量的数据,或者之前使用了祖先路径,但由于某种原因导致祖先路径的一些数据更新错误。因为祖先路径本质上是一个冗余字段,所以还是可以通过父id的方式将之还原重置。

以下为机构表的一个重置存储过程,供以参考:

  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `p_reset_organ_path`(OUT resultMark varchar(50)) 
  2. BEGIN  
  3.     /* 
  4.     使用前的说明: 
  5.     1.本存储过程非客户使用,且自己人使用频率同样较低,故过程更方便调试,但效率不是很高; 
  6.     2.如果执行SELECT * FROM t_organ WHERE organ_id<parent_organ_id(即父机构产生于子机构之后)后的数据为空,则可以考虑使用分段模式(速度会快一些). 
  7.     3.如果2中所述数据不为空,使用分段会使该id对应的机构及其子机构的ancestor_path不正确.结果为partfail. 
  8.     */ 
  9.     DECLARE intACount INT(11) DEFAULT 0; 
  10.  
  11.     DECLARE intPCount INT(11) DEFAULT 0; 
  12.     DECLARE intPIndex INT(11) DEFAULT 0; 
  13.     DECLARE intPOrganId INT(11) DEFAULT 0; 
  14.     DECLARE strPPath VARCHAR(100) DEFAULT ''
  15.     DECLARE intLoopDone INT(11) DEFAULT 0; 
  16.  
  17.     DECLARE intRCount INT(11) DEFAULT 0; 
  18.     DECLARE intRIndex INT(11) DEFAULT 0; 
  19.     DECLARE intROrganId INT(11) DEFAULT 0; 
  20.  
  21.     DROP TABLE IF EXISTS tmp_aOrganIdList; 
  22.     CREATE TEMPORARY TABLE tmp_aOrganIdList( 
  23.         rowid INT(11) auto_increment PRIMARY KEY
  24.         organ_id INT(11), 
  25.         p_organ_id INT(11) 
  26.     ); 
  27.  
  28.     DROP TABLE IF EXISTS tmp_pOrganIdList; 
  29.     CREATE TEMPORARY TABLE tmp_pOrganIdList( 
  30.         rowid INT(11) auto_increment PRIMARY KEY
  31.         organ_id INT(11) 
  32.     ); 
  33. /**/ 
  34.     DROP TABLE IF EXISTS tmp_cOrganIdList; 
  35.     CREATE TEMPORARY TABLE tmp_cOrganIdList( 
  36.         rowid INT(11) auto_increment PRIMARY KEY
  37.         organ_id INT(11) 
  38.     ); 
  39.  
  40.     DROP TABLE IF EXISTS tmp_rOrganIdList; 
  41.     CREATE TEMPORARY TABLE tmp_rOrganIdList( 
  42.         rowid INT(11) auto_increment PRIMARY KEY
  43.         organ_id INT(11), 
  44.         p_organ_id INT(11), 
  45.         ancestor_path VARCHAR(100) 
  46.     ); 
  47.  
  48.     INSERT INTO tmp_aOrganIdList (organ_id,p_organ_id) 
  49.     (SELECT organ_id,parent_organ_id FROM t_organ);-- 测试的时候limit: LIMIT 0,100 
  50.  
  51.     INSERT INTO tmp_pOrganIdList (organ_id) VALUES (0); 
  52.     INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path) VALUES (0,-1,''); 
  53.  
  54.     WHILE ((SELECT COUNT(1) FROM tmp_aOrganIdList)>0 AND intLoopDone=0) DO -- 持续循环,当没有organId数据为止(如果中间机构中断,则可能陷入死循环) 
  55.         SELECT COUNT(1) FROM tmp_pOrganIdList INTO intPCount;-- 当前父机构id的缓存区 
  56.         SET intPIndex=0; 
  57.         WHILE intPIndex<=intPCount DO -- 对每个当前查询到的父id进行对应操作 
  58.              
  59.             SELECT organ_id FROM tmp_pOrganIdList LIMIT intPIndex,1 INTO intPOrganId; 
  60.             SELECT ancestor_path FROM tmp_rOrganIdList WHERE organ_id=intPOrganId INTO strPPath; 
  61.  
  62.             INSERT INTO tmp_cOrganIdList (organ_id) (SELECT organ_id FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);-- 次级机构id的缓存区 
  63.             -- SELECT COUNT(1) FROM tmp_pOrganIdList INTO intDelCount; 
  64.             INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path) 
  65.             (SELECT organ_id,intPOrganId,CONCAT(strPPath,intPOrganId,','FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId); 
  66.             DELETE FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId; 
  67.  
  68.             SET intPIndex=intPIndex+1; 
  69.         END WHILE; 
  70.          
  71.         DELETE FROM tmp_pOrganIdList; 
  72.         IF (SELECT COUNT(1) FROM tmp_cOrganIdList)>0 THEN 
  73.             INSERT INTO tmp_pOrganIdList (organ_id) (SELECT organ_id FROM tmp_cOrganIdList); 
  74.             DELETE FROM tmp_cOrganIdList; 
  75.         ELSE 
  76.             SET intLoopDone=1; 
  77.         END IF; 
  78.         -- SELECT * FROM tmp_pOrganIdList; 
  79.         -- SELECT COUNT(1) FROM tmp_aOrganIdList; 
  80.         -- SELECT intLoopDone; 
  81.     END WHILE; 
  82.  
  83.     -- SELECT * FROM tmp_rOrganIdList;-- 想要查看测试的结果,请看此表 
  84.     SELECT COUNT(1) FROM tmp_rOrganIdList INTO intRCount; 
  85.     WHILE intRIndex<=intRCount DO 
  86.         SELECT organ_id,ancestor_path FROM tmp_rOrganIdList LIMIT intRIndex,1 INTO intROrganId,strPPath; 
  87.         UPDATE t_organ SET ancestor_path=strPPath WHERE organ_id=intROrganId; 
  88.         SET intRIndex=intRIndex+1; 
  89.     END WHILE; 
  90.  
  91.     IF (SELECT COUNT(1) FROM tmp_aOrganIdList)=0 THEN 
  92.         SET resultMark='perfect'
  93.     ELSE 
  94.         SET resultMark='partfail'
  95.     END IF; 
  96.  
  97. END 

【编辑推荐】

  1. MySQL 8.0.3 RC版即将发布,让我们来看看有哪些变化吧!
  2. 浅析开源数据库MySQL架构
  3. MySQL慢查询日志相关的配置和使用
  4. MySQL组复制技术实现与数据库性能测试工具
  5. 记一次MySQL找回用户数据的过程
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

网络渗透测试——保护网络安全的技术、工具和过程

网络和计算机安全问题已经成为政府、企业必须面对的现实问题。应对安全威胁的途径之一就是采用渗透测试的方法模拟黑客的攻击,找出网络和计...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊