闭包表（Closure Table）

设计血缘关系（data-lineage）时，想到要使用的表模型。

表设计 

节点记录表 - node

CREATE TABLE `lineages_node` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '节点名称',
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='节点';

节点关系表 - relation

CREATE TABLE `lineages_relation` (
  `ancestor` bigint(20) unsigned DEFAULT NULL COMMENT '祖先节点',
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `descendant` bigint(20) unsigned DEFAULT NULL COMMENT '后代节点',
  `depth` int(10) unsigned DEFAULT NULL COMMENT '相隔层级',
  PRIMARY KEY (`id`),
  KEY `lineages_relation_descendant_IDX` (`descendant`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据血缘-关系';

祖先 和 后代 数据表中的 约束 和 外键，还有表中其他字段，看自己业务需求。

数据结构获取

获取祖先为id 1的节点的关系

SELECT elr.relation, elr.ancestor, elr.descendant FROM lineages_node eln


INNER JOIN lineages_relation elr ON eln.id = elr.ancestor


INNER JOIN lineages_node n3 ON elr.descendant = n3.id


WHERE eln.id = 1

删除id为4的节点

DELETE FROM NodeInfo WHERE node_id = 4;

DELETE FROM NodeRelation AS n1
 WHERE n1.descendant IN 
 (SELECT a.descendant FROM 
    (SELECT n2.descendant FROM NodeRelation AS n2 WHERE n2.ancestor = 4)
 AS a);

添加节点

CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255))

BEGIN

  DECLARE _ancestor INT(10) UNSIGNED;

  DECLARE _descendant INT(10) UNSIGNED;

  DECLARE _parent INT(10) UNSIGNED;

  IF NOT EXISTS(SELECT node_id From NodeInfo WHERE node_name = _node_name)

  THEN

    INSERT INTO NodeInfo (node_name) VALUES(_node_name);

    SET _descendant = (SELECT node_id FROM NodeInfo WHERE node_name = _node_name);

    INSERT INTO NodeRelation (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);

    

    IF EXISTS (SELECT node_id FROM NodeInfo WHERE node_name = _parent_name)

    THEN

      SET _parent = (SELECT node_id FROM NodeInfo WHERE node_name = _parent_name);

      INSERT INTO NodeRelation (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 FROM NodeRelation WHERE descendant = _parent;

    END IF;

  END IF;

END;

<?php

function addNode($nodeEntity, $parentNodeEntity) {

if (LineagesNode::getQuery()->where([['id', '=', $nodeEntity->id]])->exists()) {
            if (!empty($parentNodeEntity)) {
                /** @var LineagesNode $node */
                $node = LineagesNode::getQuery()->where([['id', '=', $nodeEntity->id]])->first();
                $nodeId = $node->id;
                // all descendants update
                $descendants = LineagesRelation::getQuery()->where([['ancestor', '=', $nodeId]])->get();
                // add new ancestor to descendants
                foreach ($descendants as $descendant) {
                    $insertBranches[] = [
                        'ancestor' => $parentNodeEntity->id,
                        'descendant' => $descendant->descendant,
                        'depth' => $descendant->depth + 1,
                    ];
                }
            }
            return true;
        } else {
            // create node and get new node id
            $nodeId = LineagesNode::getQuery()->insertGetId([
                'name' => $nodeEntity->name
            ]);
            // relationship
            $result = LineagesRelation::getQuery()->insert([
                'ancestor' => $nodeId,
                'descendant' => $nodeId,
                'depth' => 0
            ]);
            if (!empty($parentNodeEntity)) {
                // all ancestors from parent node
                $ancestors = LineagesRelation::getQuery()->where([['descendant', '=', $parentNodeEntity->id], ['deleted_at', '=', null]])->get();
                LineagesRelation::getQuery()->insert([
                    'ancestor' => $parentNodeEntity->id,
                    'descendant' => $nodeId,
                    'depth' => 1
                ]);
                LineagesRelation::getQuery()->insert([
                    'ancestor' => $parentNodeEntity->id,
                    'descendant' => $nodeId,
                    'depth' => 1
                ]);
                foreach ($ancestors as $ancestor) {
                    $insertBranches[] = [
                        'ancestor' => $ancestor->id,
                        'descendant' => $nodeId,
                        'depth' => $ancestor->depth + 1
                    ];
                }
            }
            // update
            if (!empty($insertBranches)) {
                return LineagesRelation::getQuery()->insert($insertBranches);
            }
            return $result;
        }
}