MySQL基于左右值编码的树形数据库表结构设计

一、树形结构概念介绍在关系型数据库中设计树形的数据结构不是那么简单。 最常用的方案有：

1. 主从表方案；

	1. 最大缺点是树形结构的深度扩展困难，一般来说都是固定的，适合深度固定的需求。
2. 继承关系(parent_id)方案。

	1. 设计和实现自然而然，非常直观和方便；
	2. 缺点当然也是非常的突出：

		1. 由于直接地记录了节点之间的继承关系，因此对Tree的任何 CRUD操作都将是低效的，这主要归根于频繁的“递归”操作，递归过程不断地访问数据库，每次数据库IO都会有时间开销。
		2. 因此这种方案适合Tree规模相对较小的情况，我们可以借助于缓存机制来做优化，将Tree的信息载入内存进行处理，避免直接对数据库IO操作的性能开销。

二、理想中树形结构

1. 检索遍历过程简单高效；
2. 节点增删改查CRUD操作高效；
3. 数据存储冗余度小、直观性强。

三、图示

四、求某个节点的所有子孙节点

     子孙总数 = (右值 – 左值– 1) / 2

五、求某个节点在树中所处的层次

     通过左、右值的SQL查询即可实现， 以B为例 :  SELECT COUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=13    

六、定义计算指定节点所在层的函数CountLayer

     CREATE DEFINER=`root`@`localhost` FUNCTION `CountLayer`(p_node_id int) RETURNS int(11)
            BEGIN
            declare p_result,p_lft,p_rgt int default 0;
            if exists (select 1 from tree where node_id=p_node_id) then
            begin
            select lft, rgt into p_lft, p_rgt from tree where node_id=p_node_id;
            select count(*) into p_result from tree where lft <= p_lft and rgt >= p_rgt;
            end;
            return p_result;
            end if;
            RETURN 0;
            END

七、定义层次试图Tree_View

     基于刚刚定义的层计算函数来创建一个包含层次字段的试图。
            CREATE
            ALGORITHM = UNDEFINED
            DEFINER = `root`@`localhost`
            SQL SECURITY DEFINER
            VIEW `tree_view` AS
            SELECT
            `tree`.`node_id` AS `node_id`,
            `tree`.`name` AS `name`,
            `tree`.`lft` AS `lft`,
            `tree`.`rgt` AS `rgt`,
            COUNTLAYER(`tree`.`node_id`) AS `layer`
            FROM
            `tree`
            ORDER BY `tree`.`lft`

八、 获取所有子孙节点的存储过程GetChildrenNodeList

     CREATE DEFINER=`root`@`localhost` PROCEDURE `GetChildrenNodeList`(in p_node_id int)
            BEGIN
            declare p_lft,p_rgt int default 0;
            if exists (select node_id from tree where node_id=p_node_id) then
            begin
            select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
            select * from Tree_View where lft between p_lft and p_rgt order by layer, lft;
            end;
            end if;
            END

九、 查询B的所有子孙节点

        call GetChildrenNodeList(2);

十、 获取所有父节点的存储过程GetParentNodePath

     CREATE DEFINER=`root`@`localhost` PROCEDURE `GetParentNodePath`(in p_node_id int)
            BEGIN
            declare p_lft,p_rgt int default 0;
            if exists (select node_id from tree where node_id=p_node_id) then
            begin
            select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
            select * from Tree_View where lft<p_lft and rgt>p_rgt order by layer,lft asc;
            end;
            end if;
            END

    以E节点为例：
       call GetParentNodePath(5);

十一、 在某个节点下插入一个子节点

 仔细观察图一，我们以在H下添加一个节点K为例。K节点的左值为H节点的右值，K节点的右值为其左值+1，其他所有右值大于等于K的左值的节点的右值须+2，所有左值大于等于K的左值的节点的左值须+2。图示如下：

     添加节点的存储过程AddSubNode
           CREATE DEFINER=`root`@`localhost` PROCEDURE `AddSubNode`(in p_node_id int,in p_node_name varchar(50))
                BEGIN
                declare p_rgt int default 0;
                if exists(select node_id from tree where node_id=p_node_id) then
                begin
                SET AUTOCOMMIT=0;
                START TRANSACTION;
                select rgt into p_rgt from tree where node_id=p_node_id;
                update tree set rgt=rgt+2 where rgt>=p_rgt;
                update tree set lft=lft+2 where lft>=p_rgt;
                insert into tree(name,lft,rgt) values(p_node_name,p_rgt,p_rgt+1);
                COMMIT;
                end;
                end if;
                END
            
            调用AddSubNode存储过程
           call AddSubNode(8,'K');

十二、 删除节点

    删除节点是新增节点的逆向过程。
          创建删除节点存储过程DelNode。
            CREATE DEFINER=`root`@`localhost` PROCEDURE `DelNode`(in p_node_id int)
            BEGIN
            declare p_lft,p_rgt int default 0;
            if exists(select p_node_id from tree where node_id =p_node_id) then
            START TRANSACTION;
            select lft,rgt into p_lft,p_rgt from tree where node_id=p_node_id;
            delete from tree where lft>=p_lft and rgt<=p_rgt;
            update tree set lft=lft-(p_rgt - p_lft + 1) where lft > p_lft;
            update tree set rgt=rgt-(p_rgt - p_lft + 1) where rgt > p_rgt;
            COMMIT;
            end if;
            END
        
           以删除C节点为例
             call DelNode(3);

十三、 该方案的不足之处

     节点的添加、删除及修改代价较大，将会涉及到表中多方面数据的改动。但是，在消除了递归操作的前提下实现了无限分组，而且查询条件是基于整形数字的比较，效率很高。所以，该方案比较实用与查询较多，变更不大的场景。