1、数据库闭包表简介

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

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

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

以下图数据举例说明:

tree.jpg

2、创建节点表

drop table if exists nodeinfo;
CREATE TABLE nodeinfo (
    node_id INT NOT NULL AUTO_INCREMENT,
    node_name VARCHAR (255),
    PRIMARY KEY (`node_id`)
) DEFAULT CHARSET = utf8;

3、创建关系表

drop table if exists noderelationship;
CREATE TABLE noderelationship (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    distance INT NOT NULL,
    PRIMARY KEY (ancestor, descendant)
) DEFAULT CHARSET = utf8;

其中:
Ancestor代表祖先节点
Descendant代表后代节点
Distance 祖先距离后代的距离

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 node_id From nodeinfo WHERE node_name = _node_name)
    THEN
        INSERT INTO nodeinfo (node_name) VALUES(_node_name);
        SET _descendant = (SELECT node_id FROM nodeinfo WHERE node_name = _node_name);
        INSERT INTO noderelationship (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);
        IF EXISTS (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name)
        THEN
            SET _parent = (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name);
            INSERT INTO noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from noderelationship where descendant = _parent;
        END IF;
    END IF;
END;

5、插入测试数据

call AddNode(null, 'Food');

call AddNode('Food','Fruit');
call AddNode('Fruit','Red');
call AddNode('Red','Cherry');
call AddNode('Fruit','Yellow');
call AddNode('Yellow','Banana');

call AddNode('Food','Meat');

call AddNode('Meat','Beef');
call AddNode('Meat','Pork');

完成后两张表的数据大致是这样的:(注意:每个节点都有一条到其本身的记录。)

t1.png

t2.png

6、查询Fruit下所有的子节点

SELECT
    n3.node_name
FROM
    nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
    n1.node_name = 'Fruit'
AND n2.distance != 0

7、查询Fruit下直属子节点

SELECT
    n3.node_name
FROM
    nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
    n1.node_name = 'Fruit'
AND n2.distance = 1

8、查询Fruit所处的层级

SELECT
    n2.*, n3.node_name
FROM
    nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.descendant
INNER JOIN nodeinfo n3 ON n2.ancestor = n3.node_id
WHERE
    n1.node_name = 'Fruit'
ORDER BY
    n2.distance DESC

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

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

标签: none

入群须知:

凡是加入我群者,皆要严守群规,每周六、日是群规反思日。群规的要义有三点∶

(1)坚持系统化的学习方式,由量变到质变。仅仅解决工作中的问题,并不叫系统化的学习。

(2)坚持以价值为导向的学习方式,扔掉低价值知识[配置、调参、安装],聚焦高价值知识[结构、算法、优化],推动量变到质变的进程。

仅有一条评论

  1. 王先生 王先生

    文章中的图有一个title,没有感觉难受吗?

添加新评论