闭包表（Closure Table）

本文主要是介绍闭包表（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 elnINNER JOIN lineages_relation elr ON eln.id = elr.ancestorINNER JOIN lineages_node n3 ON elr.descendant = n3.idWHERE eln.id = 1

删除id为4的节点

DELETE FROM NodeInfo WHERE node_id = 4;DELETE FROM NodeRelation AS n1WHERE 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))BEGINDECLARE _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)THENINSERT 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)THENSET _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;

<?phpfunction 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 descendantsforeach ($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];}}// updateif (!empty($insertBranches)) {return LineagesRelation::getQuery()->insert($insertBranches);}return $result;}
}

这篇关于闭包表（Closure Table）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---