位置：首页-资讯-数据库

MySQL数据库闭包ClosureTable表实现示例

2023-01-13 12:02

短信预约 -IT技能 免费直播动态提醒

1、数据库闭包表简介

像MySQL这样的关系型数据库，比较适合存储一些类似表格的扁平化数据，但是遇到像树形结构这样有深度的数据，就很难驾驭了。

针对这种场景，闭包表（Closure Table ）是最通用的设计，它要求一张额外的表来存储关系，使用空间换时间的方案减少操作过程中由冗余的计算所造成的消耗。

闭包表，它记录了树中所有节点的关系，不仅仅只是直接父子关系，它需要使用两张表，除了节点表本身之外，还需要使用一张关系表，用来存储祖先节点和后代节点之间的关系（同时增加一行节点指向自身）,并且根据需要，可以增加一个字段，表示深度。

以下图数据举例说明：

在这里插入图片描述

2、创建节点表

drop table if exists node;
CREATE TABLE `node` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='节点表';

3、创建关系表

drop table if exists node_tree_paths;
CREATE TABLE `node_tree_paths` (
  `ancestor` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先节点',
  `descendant` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '后代节点',
  `distance` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先距离后代的距离',
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `descendant` (`descendant`),
  CONSTRAINT `ancestor` FOREIGN KEY (`ancestor`) REFERENCES `node` (`id`),
  CONSTRAINT `descendant` FOREIGN KEY (`descendant`) REFERENCES `node` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='节点关系表';

4、创建存储过程添加数据

drop procedure if exists AddNode;
CREATE PROCEDURE `AddNode`(_parent_name varchar(255), _node_name varchar(255))
BEGIN
    DECLARE _ancestor INT;
    DECLARE _descendant INT;
    DECLARE _parent INT;
    IF NOT EXISTS(SELECT id From node WHERE name = _node_name)
    THEN
				-- 入库 			
        INSERT INTO node (name) VALUES(_node_name);
				-- 入库ID
				SET _descendant = (select @@IDENTITY);
				-- 自己到自己的链信息
        INSERT INTO node_tree_paths (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);
				-- 上级是否存在
        IF EXISTS (SELECT id FROM node WHERE name = _parent_name)
        THEN
            SET _parent = (SELECT id FROM node WHERE name = _parent_name);
            INSERT INTO node_tree_paths (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from node_tree_paths where descendant = _parent;
        END IF;
    END IF;
END

5、插入测试数据

call AddNode('', '中国');
call AddNode('中国', '华东');
call AddNode('中国', '华南');
call AddNode('中国', '华西');
call AddNode('中国', '华北');

call AddNode('华东', '江苏');
call AddNode('华东', '浙江');
call AddNode('华东', '山东');
call AddNode('华东', '安徽');
call AddNode('华东', '江西');

call AddNode('江苏', '南京');
call AddNode('南京', '六合区');

6、查询华东下所有的子节点

SELECT
	n3.name 
FROM
	node n1
	INNER JOIN node_tree_paths n2 ON n1.id = n2.ancestor
	INNER JOIN node n3 ON n2.descendant = n3.id 
WHERE
	n1.name = '华东' 
	AND n2.distance != 0

7、查询华东下直属子节点

SELECT
    n3.name
FROM
    node n1
INNER JOIN node_tree_paths n2 ON n1.id = n2.ancestor
INNER JOIN node n3 ON n2.descendant = n3.id
WHERE
    n1.name = '华东'
AND n2.distance = 1

8、查询六合区所处的层级

SELECT
    n2.*, n3.name
FROM
    node n1
INNER JOIN node_tree_paths n2 ON n1.id = n2.descendant
INNER JOIN node n3 ON n2.ancestor = n3.id
WHERE
    n1.name = '六合区'
ORDER BY
    n2.distance DESC

9、闭包表的优缺点和适用场景

优点：在查询树形结构的任意关系时都很方便。
缺点：需要存储的数据量比较多，索引表需要的空间比较大，增加和删除节点相对麻烦。
适用场合：纵向结构不是很深，增删操作不频繁的场景比较适用。

到此这篇关于MySQL数据库闭包Closure Table表实现示例的文章就介绍到这了,更多相关MySQL数据库闭包内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL数据库闭包 MySQL Closure Table MySQL 闭包表

阅读原文内容投诉

MySQL数据库闭包ClosureTable表实现示例

下载Word文档到电脑，方便收藏和打印～

下载Word文档

猜你喜欢

MySQL数据库闭包ClosureTable表实现示例

本文主要介绍了MySQL数据库闭包ClosureTable表实现示例，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

2023-01-13

MySQL数据库闭包Closure Table表实现示例

目录1、数据库闭包表简介2、创建节点表3、创建关系表4、创建存储过程添加数据5、插入测试数据6、查询华东下所有的子节点7、查询华东下直属子节点8、查询六合区所处的层级9、闭包表的优缺点和适用场景1、数据库闭包表简介像mys

2023-01-13

mysql之数据旧表导新表的实现示例

在 mysql 中，如果两个表的结构完全相同（或者目标表包含源表中所有的列），且不想逐一列出所有字段，可以使用 INSERT INTO ... SELECT * FROM ... 语句来复制数据。这种方式不要求你列出所有的字段名。以下是一

2024-10-17

Python实现MySql数据库交互的示例

目录一、使用mysql进行持久化存储二、安装MySql数据库和python库PyMySQL三、使用pymysql链接mysql数据库四、创建表五、插入数据六、后记一、使用MySql进行持久化存储在任何应用中，都需要持久化存储。一般有 3

2023-01-06

Node.js实现连接mysql数据库功能示例

本文实例讲述了Node.js实现连接mysql数据库功能。分享给大家供大家参考，具体如下： Node.js连接数据库前，需要安装相应的包，如果安装sql server 需要先装包node-sqlserver。我们以mysql为案例来说明no

2022-06-04

mysql 5.6 多实例 mysqld_multi stop 无法关闭数据库

2024-04-02

MySQL多表联合查询允许从多个表中提取数据。语法为SELECT...FROMtable1JOINtable2ON...。示例：获取每个客户的订单总金额：SELECTc.name,SUM(o.total_amount)FROMcustomerscJOINordersoONc.id=o.customer_idGROUPBYc.name;。其他联合类型包括INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLOUTERJOIN。性能提示包括使用索引、优化连接条件、避免模糊查询和考虑使用子查询。