快捷導(dǎo)航

MySQL數(shù)據(jù)庫閉包Closure Table表實(shí)現(xiàn)示例

更新時(shí)間：2023年01月13日 11:21:00 作者：漫游游2o12

本文主要介紹了MySQL數(shù)據(jù)庫閉包Closure Table表實(shí)現(xiàn)示例，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1、數(shù)據(jù)庫閉包表簡介
2、創(chuàng)建節(jié)點(diǎn)表
3、創(chuàng)建關(guān)系表
4、創(chuàng)建存儲過程添加數(shù)據(jù)
5、插入測試數(shù)據(jù)
6、查詢華東下所有的子節(jié)點(diǎn)
7、查詢華東下直屬子節(jié)點(diǎn)
8、查詢六合區(qū) 所處的層級
9、閉包表的優(yōu)缺點(diǎn)和適用場景

1、數(shù)據(jù)庫閉包表簡介

像MySQL這樣的關(guān)系型數(shù)據(jù)庫，比較適合存儲一些類似表格的扁平化數(shù)據(jù)，但是遇到像樹形結(jié)構(gòu)這樣有深度的數(shù)據(jù)，就很難駕馭了。

針對這種場景，閉包表（Closure Table ）是最通用的設(shè)計(jì)，它要求一張額外的表來存儲關(guān)系，使用空間換時(shí)間的方案減少操作過程中由冗余的計(jì)算所造成的消耗。

閉包表，它記錄了樹中所有節(jié)點(diǎn)的關(guān)系，不僅僅只是直接父子關(guān)系，它需要使用兩張表，除了節(jié)點(diǎn)表本身之外，還需要使用一張關(guān)系表，用來存儲祖先節(jié)點(diǎn)和后代節(jié)點(diǎn)之間的關(guān)系（同時(shí)增加一行節(jié)點(diǎn)指向自身）,并且根據(jù)需要，可以增加一個(gè)字段，表示深度。

以下圖數(shù)據(jù)舉例說明：

在這里插入圖片描述

2、創(chuàng)建節(jié)點(diǎn)表

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é)點(diǎn)表';

3、創(chuàng)建關(guān)系表

drop table if exists node_tree_paths;
CREATE TABLE `node_tree_paths` (
  `ancestor` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先節(jié)點(diǎn)',
  `descendant` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '后代節(jié)點(diǎn)',
  `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é)點(diǎn)關(guān)系表';

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é)點(diǎn)

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é)點(diǎn)

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)缺點(diǎn)和適用場景

優(yōu)點(diǎn)：在查詢樹形結(jié)構(gòu)的任意關(guān)系時(shí)都很方便。
缺點(diǎn)：需要存儲的數(shù)據(jù)量比較多，索引表需要的空間比較大，增加和刪除節(jié)點(diǎn)相對麻煩。
適用場合：縱向結(jié)構(gòu)不是很深，增刪操作不頻繁的場景比較適用。

到此這篇關(guān)于MySQL數(shù)據(jù)庫閉包Closure Table表實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫閉包內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: