树形结构数据存储方案（三）：闭包表

栏目: 数据库 · 发布时间: 7年前

内容简介：将 Closure Table 翻译成闭包表不知道是否合适，闭包表的思路和物化路径差不多，都是空间换时间，Closure Table，一种更为彻底的全路径结构，分别记录路径上相关结点的全展开形式。

将Closure Table翻译成闭包表不知道是否合适，闭包表的思路和物化路径差不多，都是空间换时间，Closure Table，一种更为彻底的全路径结构，分别记录路径上相关结点的全展开形式。能明晰任意两结点关系而无须多余查询，级联删除和结点移动也很方便。但是它的存储开销会大一些，除了表示结点的Meta信息，还需要一张专用的关系表。

以下图举例数据举例：

树形结构数据存储方案（三）：闭包表

创建主表：

CREATE TABLE nodeInfo (
	node_id INT NOT NULL AUTO_INCREMENT,
	node_name VARCHAR (255),
	PRIMARY KEY (`node_id`)
) DEFAULT CHARSET = utf8;

CREATE TABLE nodeInfo (

node_id INT NOT NULL AUTO_INCREMENT,

node_name VARCHAR (255),

PRIMARY KEY (`node_id`)

) DEFAULT CHARSET = utf8;

创建关系表：

CREATE TABLE nodeRelationship (
	ancestor INT NOT NULL,
	descendant INT NOT NULL,
	distance INT NOT NULL,
	PRIMARY KEY (ancestor, descendant)
) DEFAULT CHARSET = utf8;

CREATE TABLE nodeRelationship (

ancestor INT NOT NULL,

descendant INT NOT NULL,

distance INT NOT NULL,

PRIMARY KEY (ancestor, descendant)

) DEFAULT CHARSET = utf8;

其中

Ancestor代表祖先节点
Descendant代表后代节点
Distance 祖先距离后代的距离

添加数据（创建存储过程）

CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255))
BEGIN
	DECLARE _ancestor INT;
	DECLARE _descendant INT;
	DECLARE _parent INT;
	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 noderelationship (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 noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from noderelationship where descendant = _parent;
		END IF;
	END IF;
END;

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

BEGIN

DECLARE _ancestor INT;

DECLARE _descendant INT;

DECLARE _parent INT;

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 noderelationship (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 noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from noderelationship where descendant = _parent;

END IF;

END;

完成后2张表的数据大致是这样的：（注意：每个节点都有一条到其本身的记录。）

树形结构数据存储方案（三）：闭包表

查询Fruit下所有的子节点：

SELECT
	n3.node_name
FROM
	nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
	n1.node_name = 'Fruit'
AND n2.distance != 0

SELECT

n3.node_name

FROM

nodeinfo n1

INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor

INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id

WHERE

n1.node_name = 'Fruit'

AND n2.distance != 0

查询Fruit下直属子节点：

SELECT
	n3.node_name
FROM
	nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
	n1.node_name = 'Fruit'
AND n2.distance = 1

SELECT

n3.node_name

FROM

nodeinfo n1

INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor

INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id

WHERE

n1.node_name = 'Fruit'

AND n2.distance = 1

查询Fruit所处的层级：

SELECT
	n2.*, n3.node_name
FROM
	nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.descendant
INNER JOIN nodeinfo n3 ON n2.ancestor = n3.node_id
WHERE
	n1.node_name = 'Fruit'
ORDER BY
	n2.distance DESC

SELECT

n2.*, n3.node_name

FROM

nodeinfo n1

INNER JOIN noderelationship n2 ON n1.node_id = n2.descendant

INNER JOIN nodeinfo n3 ON n2.ancestor = n3.node_id

WHERE

n1.node_name = 'Fruit'

ORDER BY

n2.distance DESC

另外要删除节点也非常的简单，这里就不再做过多的阐述。

参考链接：

https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql

原文出处：标点符

以上所述就是小编给大家介绍的《树形结构数据存储方案（三）：闭包表》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对码农网的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

Impractical Python Projects

Lee Vaughan / No Starch Press / 2018-11 / USD 29.95

Impractical Python Projects picks up where the complete beginner books leave off, expanding on existing concepts and introducing new tools that you’ll use every day. And to keep things interesting, ea......一起来看看《Impractical Python Projects》这本书的介绍吧!

码农工具

HTML 压缩/解压工具

在线压缩/解压 HTML 代码

CSS 压缩/解压工具

在线压缩/解压 CSS 代码