当前位置: 首页 > news >正文

Closure Table-树形多级关系数据库设计(MySql)

一般树形多级关系数据库设计,比较普遍的就是四种方法:(具体见 SQL Anti-patterns这本书)

Adjacency List:每一条记录存parent_id

Path Enumerations:每一条记录存整个tree path经过的node枚举(适合深度较浅且固定的业务,字段超长互后将无法命中索引)

Nested Sets:每一条记录存 nleft 和 nright

Closure Table:维护一个表,所有的tree path作为记录进行保存。

本文将以Adjacency List作为原始记录,Closure Table存储上下级关系的方式来解决查询困难问题。

本文限定:删除节点时,仅可删除最末节点,不可从中间删除,否则树不能称之为树。

一:通过parent_id方式保存原始记录

1:如图,现有以下关系

以数据库保存关系如下:

 仅以当前表结构,如果仅仅是查询上下级关系,将非常简单,如:
 

-- 查询西湖区的下级select * from t_area where parent_id = 330106;-- 查询西湖区的上级select * from t_area where id = ( select parent_id from t_arem where id = 330106 )

但是,如果需要查询杭州市的所有下级单位,必须使用递归查询:

如果此时需求是查询"西溪街道"与"杭州市"是否是上下级关系? 那么每次查询都需要递归关系吗?

此时,需要引入Closure Table模式创建上下级关系。

二:创建ClosureTable关系表

结构如下:

  数据如下:

此时,如果查询杭州市所有下级单位,SQL如下:

 "西溪街道"与"杭州市"是否是上下级关系?

 

 三 :ClosureTable详细使用方法

1:新增数据

如上图,在下城区新增望江街道。

代码如下:

-- 1,新增父子关系
INSERT INTO t_area(id,name,parent_id) VALUES(330102010,'望江街道',330102);-- 2,新增上下级关系
-- 2.1 插入自身
INSERT INTO t_area_closure(ancestor,descendant,level) VALUES(330102010,330102010,0);-- 2.2 为 330102010 的父级 330102 所有上级(包含330102自身)增加下级330102010血缘关系。
INSERT INTO t_area_closure(ancestor,descendant,level)
select ancestor,330102010 as descendant,(level+1) as level from t_area_closure where descendant =330102;

此时,查询330102010的上级关系:(如果不包含自己,则筛选level>0即可)

2:删除数据

如上图,删除杭州市与江干区的关系。

代码如下:

-- 1,删除父子关系(也可以增加逻辑字段进行软删除)
DELETE FROM t_area where id =330104;-- 2,删除关系表。因为是末端删除,只要删除指定数据的父级关系即可DELETE FROM t_area_closure where descendant = 330104;

此时,关系表中已没有330104数据的关系数据

3:变更父子关系 

如上图,将西湖区转移到望江街道下面。

简单做法:

删除老的关系。将西湖区、北山街道、西溪街道,按删除数据处理,删除所有关系。

新增关系。在望江街道下面新增西湖区、北山街道、西溪街道关系。

复杂做法:

1,删除老关系。

西湖区、北山街道、西溪街道与杭州市、浙江省的关系将会变更,所以先删除西湖区、北山街道、西溪街道与杭州市、浙江省的上级关系。即,西湖区及下属区域删除西湖区父级以上区域的关系。

-- 1.1 查找指定区域的父级及以上关系
SELECT ancestor from t_area_closure where descendant = 330106 and level >0
-- 1.2 查找区域自己和自己的下级
SELECT descendant from t_area_closure where ancestor = 330106
-- 1.3删除自己和自己的下级 与 自己父级以上区域的关系
delete from t_area_closure where ancestor in ( select ancestor from (SELECT ancestor from t_area_closure where descendant = 330106 and level >0) t1 )
and descendant in ( select descendant from (SELECT descendant from t_area_closure where ancestor = 330106) t2 )

此时删除完成数据后,西湖区自己与下属北山街道、西溪街道的关系依然存在。

2,添加新关系。

2.1 查询新父级的上级关系(包含新父级)

 

2.2 查找区域自己和自己的下级

 

2.3 为指定区域自己及下级增加新父级的上级关系。

INSERT INTO t_area_closure(ancestor,descendant,level)
SELECT t1.ancestor,t2.descendant,(t1.level+t2.`level` +1) as level from t_area_closure t1,t_area_closure t2 
where t1.descendant =330102010 and t2.ancestor = 330106;

此时,查询北山街道所有上级:

数据正确。 

相关文章:

Closure Table-树形多级关系数据库设计(MySql)

一般树形多级关系数据库设计,比较普遍的就是四种方法:(具体见 SQL Anti-patterns这本书) Adjacency List:每一条记录存parent_id Path Enumerations:每一条记录存整个tree path经过的node枚举&#xff08…...

【SQL应知应会】表分区(一)• MySQL版

欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享,与更多的人进行学习交流 本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习,有基础也有进阶,有MySQL也有Oracle 分区表 • MySQL版 一、分区表1.非分区表2.分区表2…...

java语法基础-- 变量、标识符、关键字

学习目标 教学目标重点难点1.掌握变量的相关概念。2.掌握Java中数据类型的划分。3.掌握8种基本数据类型的使用。4.掌握数据类型的转换方式。5.掌握各个运算符,表达式的作用。6.可以编写简单的Java应用程序。1.对变量的理解。2.基本数据类型的相关信息的记忆。3.数据…...

[STL]stack和queue模拟实现

[STL]stack和queue模拟实现 文章目录 [STL]stack和queue模拟实现stack模拟实现queue模拟实现 stack模拟实现 stack是一种容器适配器,标准容器vector、deque、list都可以作为实现stack的底层数据结构,因为它们都具备以下功能: empty&#xf…...

汽车销售企业消费税,增值税高怎么合理解决?

《税筹顾问》专注于园区招商、企业税务筹划,合理合规助力企业节税! 汽车行业一直处于炙手可热的阶段,这是因为个人或者家庭用车的需求在不断攀升,同时随着新能源的技术进一步应用到汽车领域,一度实现了汽车销量的翻倍。…...

flask数据库操作

本文将详细介绍在Flask Web应用中如何设计数据库模型,并使用Flask-SQLAlchemy等扩展进行数据库操作的最佳实践。内容涵盖数据模型设计,ORM使用,关系映射,查询方法,事务处理等方面。通过本文,您可以掌握Flask数据库应用的基本知识。 Flask作为一个流行的Python Web框架,提供了高…...

【C++】 哈希

一、哈希的概念及其性质 1.哈希概念 在顺序结构以及平衡树中,元素关键码与其存储位置之间没有对应的关系,因此在查找一个元素时,必须要经过关键码的多次比较。比如顺序表需要从第一个元素依次向后进行查找,顺序查找时间复杂度为…...

TCP三次握手和四次挥手以及11种状态(二)

11种状态 1、一开始,建立连接之前服务器和客户端的状态都为CLOSED; 2、服务器创建socket后开始监听,变为LISTEN状态; 3、客户端请求建立连接,向服务器发送SYN报文,客户端的状态变味SYN_SENT; 4、…...

【华为OD】运维日志排序

题目描述: 运维工程师采集到某产品线网运行一天产生的日志n条,现需根据日志时间先后顺序对日志进行排序,日志时间格式为H:M:S.N。 H表示小时(0~23) M表示分钟(0~59) S表示秒(0~59) N表示毫秒(0~999) 时间可能并没有补全,也就是说&…...

Mag-Fluo-4 AM,镁离子荧光探针,是一种有用的细胞内镁离子指示剂

资料编辑|陕西新研博美生物科技有限公司小编MISSwu​ PART1----产品描述: 镁离子荧光探针Mag-Fluo-4 AM,具细胞膜渗透性,对镁离子(Mg2) 和钙离子(Ca2)的 Kd 值分别是 4.7mM 和 22mM&#xff0c…...

与 ChatGPT 进行有效交互的几种策略

在这篇文章中,您将了解即时工程。尤其, 如何在提示中提供对响应影响最大的信息什么是角色、正面和负面提示、零样本提示等如何迭代使用提示来利用 ChatGPT 的对话性质 废话不多说直接开始吧!!! 提示原则 快速工程是有…...

华为云安装MySQL后,本地工具连接MySQL失败

华为云安装MySQL后,本地连接失败 排查问题步骤: 在此之前需要在MySQL创建用户,并赋予权限。 1、能否ping通。 在本地命令行(Windows:winR)通过ping命令,ping服务器地址,看能否ping通。不能则需要检查本地…...

Flink On Yarn模式部署与验证

session运行模式 该模式下分为2步,即使用yarn-session.sh申请资源,然后 flink run提交任务。 1、申请资源yarn-session.sh #在server1执行命令 /usr/local/flink-1.13.5/bin/yarn-session.sh -tm 1024 -n 2 -s 1 -d #申请2个CPU、2g内存 # -tm 表示每个…...

[数据库]对数据库事务进行总结

文章目录 1、什么是事务2、事务的特性(ACID)3、并发事务带来的问题4、四个隔离级别: 1、什么是事务 事务是逻辑上的一组操作,要么都执行,要么都不执行。 事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红…...

【Lua学习笔记】Lua进阶——Table(2)

文章目录 Table的一万种用法二维数组类和结构体Table操作insert & removesortconcat 接上文【Lua学习笔记】Lua进阶——Table,迭代器 Table的一万种用法 二维数组 a {{ 1, 2, 3 },{ 4, 5, 6 }, } print(#a) -->2 for i1,#a dob a[i]for j1,#b doprint(b[…...

如何进行软件回归测试

什么是软件回归测试,如何进行回归测试,进行回归测试时有哪些常用的方法? 回归测试是指修改了旧代码后,重新进行测试以确认修改没有引入新的错误或导致其他代码产生错误的一种测试方法。回归测试是指重复以前的全部或部分的相同功能…...

php://filter绕过死亡exit

文章目录 php://filter绕过死亡exit前言[EIS 2019]EzPOP绕过exit 参考 php://filter绕过死亡exit 前言 最近写了一道反序列化的题,其中有一个需要通过php://filter去绕过死亡exit()的小trick,这里通过一道题目来讲解 [EIS 2019]EzPOP 题目源码&#…...

RS485/RS232自由转ETHERNET/IP网关profinet和ethernet区别

你是否曾经遇到过这样的问题:如何将ETHERNET/IP网络和RS485/RS232总线连接起来呢?捷米的JM-EIP-RS485/232通讯网关,自主研发的ETHERNET/IP从站功能,完美解决了这个难题。这款网关不仅可以将ETHERNET/IP网络和RS485/RS232总线连接起…...

Hadoop_HDFS_常见的文件组织格式与压缩格式

参考资料 1. HDFS中的常用压缩算法及区别_大数据_王知无_InfoQ写作社区 2. orc格式和parquet格式对比-阿里云开发者社区 3.Hadoop 压缩格式 gzip/snappy/lzo/bzip2 比较与总结 | 海牛部落 高品质的 大数据技术社区 4. Hive中的文件存储格式TEXTFILE、SEQUENCEFILE、RCFILE…...

算法与数据结构(四)--排序算法

一.冒泡排序 原理图: 实现代码: /* 冒泡排序或者是沉底排序 *//* int arr[]: 排序目标数组,这里元素类型以整型为例; int len: 元素个数 */ void bubbleSort (elemType arr[], int len) {//为什么外循环小于len-1次?//考虑临界情况&#xf…...

Unity性能优化终极利器:MeshFusion Pro

在现代游戏开发中,性能优化始终是一个核心问题。尤其是在大型场景或高复杂度模型的项目中,Draw Call 过多、顶点数量庞大以及实时生成对象都会严重拖慢游戏帧率,影响用户体验。为了应对这些挑战,Unity 开发者社区中出现了大量优化…...

如何用Lingui.js在SSG项目中实现完美国际化:终极指南

如何用Lingui.js在SSG项目中实现完美国际化:终极指南 【免费下载链接】js-lingui 🌍 📖 A readable, automated, and optimized (2 kb) internationalization for JavaScript 项目地址: https://gitcode.com/gh_mirrors/js/js-lingui …...

何为多态?

多态的概念多态是面向对象编程的三大特性之一(封装、继承、多态),指同一操作作用于不同对象时会产生不同的行为。具体表现为父类引用指向子类对象,并在运行时根据实际对象类型调用相应的方法。多态的好处提高代码扩展性通过多态&a…...

基于Matlab的多自由度轴承静刚度计算之旅

基于Matlab的多自由度轴承静刚度计算 因分析静态下刚度结果,仅考虑重力作用,未考虑离心力的作用 深沟球轴承和圆锥轴承基本参数包括滚珠数量、滚珠直径、中称直径、曲率和材料参数 程序已调通,可直接运行在机械工程领域,深入了解轴…...

Go Channel 缓冲区机制分析

Go Channel 缓冲区机制分析 在Go语言的并发编程中,Channel是协程间通信的核心机制之一。其缓冲区机制的设计直接影响程序的性能和稳定性。理解缓冲区的运作原理,能够帮助开发者更高效地利用Channel,避免死锁或资源浪费。本文将从缓冲区的本质…...

C++的std--filesystem文件系统库与跨平台路径处理的标准化

C的std::filesystem文件系统库与跨平台路径处理的标准化 在现代软件开发中,跨平台文件系统操作一直是一个复杂且容易出错的挑战。不同操作系统(如Windows、Linux和macOS)使用不同的路径分隔符和文件系统语义,开发者往往需要编写大…...

2024年实测:火狐浏览器上这3款广告过滤插件,谁才是真正的网页加速器?

2024年火狐浏览器广告过滤插件终极对决:谁才是网页加速王者? 在数字时代,网页浏览速度直接影响着我们的工作效率和上网体验。对于火狐浏览器用户来说,选择一款高效的广告过滤插件不仅能屏蔽恼人的广告,更能显著提升页面…...

时序数据库选型避坑指南:从写入性能到查询优化的5个关键指标对比(含IoTDB实测数据)

时序数据库选型实战:5个关键指标与IoTDB性能深度评测 当工业互联网平台每秒需要处理百万级传感器数据时,传统数据库的写入瓶颈往往成为系统崩溃的导火索。某汽车制造厂的案例颇具代表性——他们在初期选型时过度关注查询功能,结果系统上线后频…...

告别网络调试焦虑:用STM32CubeMX+FreeRTOS,给LAN8720A和LWIP做个“健康检查”与性能小优化

STM32网络子系统深度优化:从连通性测试到工业级稳定性实战 当你熬夜调试的嵌入式设备终于能Ping通时,那种喜悦感堪比程序员第一次写出"Hello World"。但很快你会发现,真正的挑战才刚刚开始——那些在演示视频里永远不会出现的诡异断…...

终极EdgeRemover指南:专业卸载Windows Edge浏览器的完整解决方案

终极EdgeRemover指南:专业卸载Windows Edge浏览器的完整解决方案 【免费下载链接】EdgeRemover PowerShell script to remove Microsoft Edge in a non-forceful manner. 项目地址: https://gitcode.com/gh_mirrors/ed/EdgeRemover EdgeRemover是一款专业的P…...