MySQL数据库闭包Closure Table表实现示例
作者:漫游游2o12 发布时间:2024-01-21 23:29:24
标签:MySQL,数据库闭包
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、闭包表的优缺点和适用场景
优点:在查询树形结构的任意关系时都很方便。
缺点:需要存储的数据量比较多,索引表需要的空间比较大,增加和删除节点相对麻烦。
适用场合:纵向结构不是很深,增删操作不频繁的场景比较适用。
来源:https://blog.csdn.net/ithesytem/article/details/128454337


猜你喜欢
- Python计算的位数在电脑上做了一个实验,看看python能计算到多少位,一下是结果。x = math.sqrt((3))print (&
- PDOStatement::debugDumpParamsPDOStatement::debugDumpParams — 打印一条 SQL
- 楼主在做公司项目的时候遇到url重定向的问题,因此上网简单查找,作出如下结果由于使用的是语言是python所以以下是python的简单解决方
- 听说安全地断开Connection连接的记录集可以提高ASP的运行速度,请问如何实现?很多人会将一个Connection对象存储在Appli
- 为什页面刷新会出现404因为vue项目中路由hash模式改为了history模式,由于hash模式时url带的#号后面是哈希值不会作为url
- 最好也是最简单的办法就是利用Cookie,而不必用到数据库。当然,你愿意用数据库也可以。下面就是利用Cookie来实现的:< 
- Vue是一个轻量级的渐进式框架,对于它的一些特性和优点在此就不做赘述。下面通过本文给大家分享Vue中父组件向子组件通信的方法,具体内容详情如
- 开发环境:Pycharm 2018.3 + Anaconda3(5.3.0) + Python 3.7.1 + Numpy 1.15.4在此
- 如下所示:import jsonimport http.clientconnection = http.client.HTTPSConnec
- 这是支持的下载版本,去官网下载2020.3及以上(2021-03-18测试破解有效)官网下载地址:https://www.jetbrains
- 熬了半个通宵,写出了自己的Google SiteMap文件,在这里给出详细编写教程,愿对大家有所帮助。Google SiteMap的作用及协
- 目录jiaba库的使用1、jieba库的安装2、统计荷塘月色词频总结jiaba库的使用jieba库是一款优秀的 Python 第三方中文分词
- 我们的每期话题,团队在内部都会通过邮件进行一番讨论,随着讨论的激烈,往往能碰撞出很多有意义的观点,因此,将讨论内容分享出来,有兴趣的朋友可以
- 这篇文章主要介绍了基于python traceback实现异常的获取与处理,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参
- Q: I am working with Oracle database 8.1.7 and I have written a JAVA c
- 引文之前将PHP反序列化的基础知识讲了一遍,不知道大家学习的怎么样了,今天给大家带来PHP反序列化的进阶知识:PHAR反序列化,也是之前本人
- 一. MovingAverage权值滑动平均更新1.1 示例代码:def create_target_q_network(self,stat
- 这里首先要介绍官方文档,对python有了进一步深度的学习的大家们应该会发现,网上不管csdn或者简书上还是什么地方,教程来源基本就是官方文
- 反馈说在选择时间时会出现遮挡选择器的情况,阻碍操作 如下图1,需要修改xadmin 文件 ,在widgets.py --->第28行添
- 今天给大家讲的是ASP给图片加水印的知识ASP给图片加水印是需要组件的…常用的有aspjpeg和中国人自己开发的wsImage…前者有30天