MySQL數(shù)據(jù)庫閉包Closure Table表實現(xiàn)示例
更新時間：2023年01月13日 11:21:00 作者：漫游游2o12
本文主要介紹了MySQL數(shù)據(jù)庫閉包Closure Table表實現(xiàn)示例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
1、 數(shù)據(jù)庫閉包表簡介
像MySQL這樣的關系型數(shù)據(jù)庫，比較適合存儲一些類似表格的扁平化數(shù)據(jù)，但是遇到像樹形結構這樣有深度的數(shù)據(jù)，就很難駕馭了。
針對這種場景，閉包表（Closure Table ）是最通用的設計，它要求一張額外的表來存儲關系，使用空間換時間的方案減少操作過程中由冗余的計算所造成的消耗。
閉包表，它記錄了樹中所有節(jié)點的關系，不僅僅只是直接父子關系，它需要使用兩張表，除了節(jié)點表本身之外，還需要使用一張關系表，用來存儲祖先節(jié)點和后代節(jié)點之間的關系（同時增加一行節(jié)點指向自身）,并且根據(jù)需要，可以增加一個字段，表示深度。
以下圖數(shù)據(jù)舉例說明：
2、創(chuàng)建節(jié)點表
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='節(jié)點表';
3、創(chuàng)建關系表
drop table if exists node_tree_paths; CREATE TABLE `node_tree_paths` ( `ancestor` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先節(jié)點', `descendant` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '后代節(jié)點', `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='節(jié)點關系表';
4、創(chuàng)建存儲過程添加數(shù)據(jù)
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、插入測試數(shù)據(jù)
call AddNode('', '中國');
call AddNode('中國', '華東');
call AddNode('中國', '華南');
call AddNode('中國', '華西');
call AddNode('中國', '華北');
call AddNode('華東', '江蘇');
call AddNode('華東', '浙江');
call AddNode('華東', '山東');
call AddNode('華東', '安徽');
call AddNode('華東', '江西');
call AddNode('江蘇', '南京');
call AddNode('南京', '六合區(qū)');
6、查詢 華東 下所有的子節(jié)點
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、查詢 華東 下直屬子節(jié)點
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、查詢 六合區(qū) 所處的層級
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 = '六合區(qū)'
ORDER BY
n2.distance DESC
9、閉包表的優(yōu)缺點和適用場景
優(yōu)點：在查詢樹形結構的任意關系時都很方便。
缺點：需要存儲的數(shù)據(jù)量比較多，索引表需要的空間比較大，增加和刪除節(jié)點相對麻煩。
適用場合：縱向結構不是很深，增刪操作不頻繁的場景比較適用。
到此這篇關于MySQL數(shù)據(jù)庫閉包Closure Table表實現(xiàn)示例的文章就介紹到這了,更多相關MySQL數(shù)據(jù)庫閉包內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了kali虛擬機mysql修改綁定ip,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-06-06
這篇文章主要介紹了mysql之數(shù)據(jù)庫常用腳本總結，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
MySql 5.7.14 服務沒有報告任何錯誤的解決方法(推薦)
這篇文章主要介紹了MySql 5.7.14 服務沒有報告任何錯誤解決方法的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-09-09
本文給大家分享的是如何給mysql配置SSL主從復制的具體方法，分別給出了5.6和5.7兩個版本的實例，希望大家能夠喜歡2019-01-01
今天小編就為大家分享一篇關于MySQL可視化工具Navicat的連接方法，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2018-10-10
MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作,結合實例形式詳細分析了MySQL備份數(shù)據(jù)庫基本操作命令與相關注意事項,需要的朋友可以參考下2020-05-05
在Windows環(huán)境下使用MySQL:實現(xiàn)自動定時備份
下面小編就為大家分享一篇在Windows環(huán)境下使用MySQL:實現(xiàn)自動定時備份的方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2017-12-12
MySQL 查找價格最高的圖書經(jīng)銷商的幾種SQL語句
不同的圖書，在不同的經(jīng)銷商的價格不同，我們這里要找到每種圖書最高的經(jīng)銷商是誰？ 找最低的類似了。2009-07-07
這篇文章主要介紹了MySQL 自動清理binlog日志的方法,需要的朋友可以參考下2016-10-10
這篇文章主要給大家介紹了關于MySQL單表查詢的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-09-09