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

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

内容简介：将 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

原文出处：标点符

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

查看所有标签

猜你喜欢:

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

码农书籍

PHP经典实例

（美）斯克拉、（美）切贝特伯格 / 李松峰、秦绪文、李丽 / 中国电力出版社 / 2009-10 / 98.00元

PHP经典实例（第2版）能够为您节省宝贵的Web开发时间。有了这些针对真实问题的解决方案放在手边，大多数编程难题都会迎刃而解。《PHP经典实例(第2版)》将PHP的特性与经典实例丛书的独特形式组合到一起，足以帮您成功地构建跨浏览器的Web应用程序。在这个修订版中，您可以更加方便地找到各种编程问题的解决方案，《PHP经典实例(第2版)》中内容涵盖了：表单处理；Session管理；数据库交互；使用We......一起来看看《PHP经典实例》这本书的介绍吧!

码农工具

CSS 压缩/解压工具

在线压缩/解压 CSS 代码

在线进制转换器

各进制数互转换器