如何使用MySQL关系型数据库存储树结构

袁新栋-jeff.yuan

发布于 2020-12-16 17:28:53

2.7K0

发布于 2020-12-16 17:28:53

文章被收录于专栏：用户4352451的专栏用户4352451的专栏

背景

需求存储一个组织结构或者档案仓库，看到这个需求我们的第一个反应肯定就是树状结构，并且是一个多层多节点无限级树状机构。
我们目前使用的是mysql关系型数据库。那我们应该如何来实现这个结构关系呢？

有3种存储的方式：到目前为止我在实战中曾使用过三种方式来实现这种hierarchical-data:
- Adjacency list (邻接表)
- Closure table (闭包表)
- Path enumeration (路径枚举)

基于个人需要这里主要了解闭包表。

Closure table (闭包表)

什么是闭包表

个人理解：通过一个表来存储树节点中任何两个节点之间的关系。
核心字段有三个值:
- ancestor 父节点的id
- descendant子节点的ID
- depth 子节点与父节点之间的深度关系
根据真实数据模型理解一下

闭包表的SQL结构

CREATE TABLE `comment_path` (

  `ancestor` INT NOT NULL,
  `descendant` INT NOT NULL,
  `depth` TINYINT(5) NOT NULL,
  PRIMARY KEY (`ancestor`, `descendant`),
  INDEX `fk_comment_path_descendant_idx` (`descendant` ASC),
  INDEX `fk_comment_path_ancestor_idx` (`ancestor` ASC),
  CONSTRAINT `fk_comment_path_comment1`
    FOREIGN KEY (`ancestor`)
    REFERENCES `comment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_comment_path_comment2`
    FOREIGN KEY (`descendant`)
    REFERENCES `comment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

存放节点信息表

<-可以不要这个表->
CREATE TABLE `topic` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

<-用户信息，可以是用户中心的表，也可以和票据信息表放一块->
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

<-票据信息表->
CREATE TABLE `comment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` VARCHAR(2048) NULL,
  `topic_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_comment_topic_idx` (`topic_id` ASC),
  INDEX `fk_comment_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_comment_topic2`
    FOREIGN KEY (`topic_id`)
    REFERENCES `topic` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_comment_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

根据闭包表的关系我们怎么来表示库1的位置呢？

从图中可知我们库1的位置会在闭包表中存储18条数据。因为库1有一个父节点还有16个子节点还有自己与自己的关系。
在闭包表插入库1与柜1的关系。根据前序遍历规则，库1被标记为2 柜1被标记为3 两者的深度关系是1，所以插入的SQL是：

INSERT INTO `comment_path` (`ancestor`, `descendant`, `depth`) VALUES (2, 3, 1);

现在我们需要查询这个库1的所有子节点信息

你把这些数据肯定都是以库1多为父节点的所以直接查库1的前序遍历的序号等于2就可以了。
还有就是想查看库1中有多少个册，那我门加上depth =1就可以了。或者说子子节点有多少个凭证信息的数据那就是depth = 2

select c.* from comment c join comment_path cp on (c.id = cp.descendant) where cp.ancestor = 1 and depth = 1;

在这个树上添加一个票据1
- 先在详情信息表进行插入信息（这个value其实也不用遵循前序遍历的的规则是自增的也OK的）
- 然后在闭包表进行创建所有的关联关系。也就是说创建票据1我们得插入6条数据 5条父节点1条自己对自己。
- 我们知道是从哪里插入的，也就是他的父节点是已知的。也就是他的父节点我们得动态查出来，得到这个父节点我们可以找到他的同一个子树下的几点，因为他两拥有的父节点都是一样的，或者找到他的上一层级的所有父节点包括他自己都属于新加的节点的父节点。他自己是insert后的值是10

     insert into comment(value, topic_id, user_id) values('(10)我以gin食阼啦', 1, 2);
     
      insert into comment_path (ancestor, descendant, depth) select cp.ancestor, 10, cp.depth+1 from comment_path as cp where cp.descendant=6 union all select 10, 10, 0;

删除柜1下的所有数据
- 那意思就是将以柜1为父节点的数据都删除掉。
- 柜1的value为3

delete a from comment_path a join comment_path b on (a.descendant = b.descendant) where b.ancestor=3;

将册1移动到柜2底下
- 将凭证1凭证2册1的父节点除了和自身相关的都进行删除。
- 然后再插入除和他们自己相关的path之外的k节点再进行插入。

delete a from comment_path as a join comment_path as d on a.descendant = d.descendant left join comment_path as x on x.ancestor = d.ancestor and x.descendant = a.ancestor where d.ancestor = 册1 and x.ancestor is null;

insert into comment_path (ancestor, descendant, depth) select supertree.ancestor, subtree.descendant, supertree.depth+subtree.depth+1 from comment_path as supertree join comment_path as subtree where subtree.ancestor = 柜2 and supertree.descendant = 册1;

参考

https://www.jianshu.com/p/951b742fd137 https://time.geekbang.org/column/article/67856 https://github.com/Agileaq/Hierarchical_Design/blob/master/Closure.sql https://juejin.cn/post/6844903906112176141 https://zh.wikipedia.org/wiki/%E5%B9%B3%E8%A1%A1%E6%A0%91

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-12-13 ，如有侵权请联系 cloudcommunity@tencent.com 删除

https