mysql树状数据的数据库设计 - HelloWorld开发者社区

0 树状数据的分类

我们在mysql数据库设计的时候,会遇到一种树状的数据.如公司下面分开数个部门,部门下面又各自分开数个科室,以此形成树状的数据.关于树状的数据,按层级数大致可分为一下两类:

分类

特点

固定数量层级

层级数量固定,每一层级都有各自的意义,如集团-分公司-部门-科室,省-市-区等

可变数量层级

层级数量不固定,前几层级可能会有特殊含义,但整体在相当大的范围内是浮动的

前者的优点在于,由于每一层级均有各自含义,数据库的整体设计更为方便,可将某一子节点的不同上级节点均存储在数据库中,同样以某集团为例:

节点code

节点名称

节点层级

父级节点code

1级祖先code

2级祖先cdoe

010000

公司1

000000

null

020000

公司2

000000

null

010300

制造部

010000

null

010400

品质部

010000

null

010301

前工程制造

010300

010000

010300

010303

组装制造

010300

010000

010300

这样设计的表格冗余较多,但在各种类型查询的时候效率较高.在插入,更新(含子机构,由于业务逻辑特点,机构之间的更新一般是平行转移),删除(含子机构)的时候,由于冗余信息较多,数据操作时所需进行的查询获得也较简单.根据情况,部分冗余信息也考虑删去,如父级节点code,删去一些设计必然会导致部分查询的效率或复杂度提升,这个就需要根据实际情况来取舍平衡了.
缺点有两个:

一个是当层级数量较多的时候,需要存储大量的冗余信息.当然也可以考虑节约方案:1)不存储像n级祖先code这样的字段,但这样就无法利用固定层级设计带来的高效查询特性,是不建议这么做的;2)n级存储不使用code而改用id,这样做主要是在数据迁移或者他表利用的时候不方便.
另一个缺点是,当需求方给出要求,需要对当前机构重新洗牌,变更层级数的时候,你会非常头疼.

后者的优缺点则与前者的优缺点恰好相反,非固定的层级限制非常灵活,而缺点就是查询及数据操作上两方面的不便,这也是本文所要讲述的重点,即如何设计非固定层级的树状数据.

1 非固定层级树状数据的设计方式--祖先路径

树状数据最简单的一种设计方式是,只增加父级id.但这种设计方式给查询后代节点带来了极大的不便,据我所知,尚没有一种不通过函数/存储过程这样循环遍历的查询方式,来一次获取某个节点的所有后代节点或是祖先节点.(此前找到过一个较复杂的查询后代节点的sql,利用的也是祖先节点的id大于后代节点id的特性,但有可能存在通过更新节点使后代节点id大于祖先节点id,所以也不严谨,在此不进行详述)
对于非固定层级树状数据的一种设计方式是:增加祖先路径(ancestor_path),具体可参考下表:
id | 节点名称 | 父id | 祖先路径
--- | --- | --- | ---
1 | node1 | 0 | 0,
2 | node2 | 0 | 0,
3 | node1.1 | 1 | 0,1,
4 | node1.2 | 1 | 0,1,
5 | node2.1 | 2 | 0,2,
6 | node1.1.1 | 3 | 0,1,3,
7 | node1.1.2 | 3 | 0,1,3,
8 | node1.2.1 | 4 | 0,1,4,
9 | node2.1.1 | 5 | 0,2,5,
实际设计时,还可考虑加入层级这个冗余字段,但我在实际使用的过程中很少用到这个字段.
这样,在加了这个字段之后,任意节点的所有祖先节点信息就都可通过这样一条数据全部获取.
祖先路径的设定具有以下特点:

没有父节点的根节点,父id默认为'0',祖先路径默认为'0,';
每增加的一个子节点,祖先路径都是在要增加的子节点的父节点的祖先路径上增加父id和','; 参考的表结构如下:

CREATE TABLE t_node ( node_id int(11) NOT NULL AUTO_INCREMENT, node_name varchar(50) NOT NULL, p_id int(11) NOT NULL, ancestor_path varchar(100) NOT NULL, PRIMARY KEY (node_id) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2 祖先路径的查询

设计的树节点的查询,主要有两种,一种是查询某个节点的所有后代节点(与查询祖先节点为某个已知节点的所有节点集合是一个意思),这种也是最常用的一种查询;一种是查询某个节点的所有祖先节点,这种不太常用.

查询某个节点的所有后代节点参考示例如下:

SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')

以上sql即是对id为?的某个节点的所有后代节点的查询方式一,还可使用以下方式:

SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT('%,',?,',%')

查询方式二的方式更加简洁.但考虑到查询方式一只用到了右模糊查询,可以使用索引,所以还是建议使用方式一进行查询.
需要注意的是以上两种方式查到的节点集合都不包含子节点,如果需要包含该节点的信息,还需要加上

... OR node_id=?

查询某个节点的所有祖先节点

SELECT * FROM t_node WHERE node_id REGEXP CONCAT('^(', REPLACE((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',','|'), '0)$')

以上方式查询祖先节点的效率确实不是很高,但考虑到该查询本身并不用,便姑且用之了.

3 祖先路径的插入,更新和删除

分别分插入,更新和删除来讲:

插入

INSERT INTO t_node (node_name,p_id,ancestor_path) VALUE('node?',?, CONCAT((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt),?,','))

sql中的3个?均为要加入父节点的id.

更新(含子节点)
如果更新的时候,父节点的位置没有变化,则不必考虑太多;
如果需要更新所在父节点,相比于最简单的树节点设计模式,增加祖先路径的方式除了在更新当前节点本身的父id外,还需要修改对应的祖先路径,这个步骤通过存储过程实现,是一种比较简单的方式,在此不再详述.仅对不使用存储过程的方式进行描述.

UPDATE t_node SET p_id=?_p WHERE node_id=?_n; UPDATE t_node SET ancestor_path=CONCAT((SELECT * FROM(SELECT ancestor_path FROM t_node WHERE node_id=?_p)wt2),?_p,',',SUBSTR(ancestor_path,LENGTH(@PPath)+1)) WHERE ancestor_path LIKE CONCAT((SELECT * FROM (SELECT @ppath:=ancestor_path FROM t_node WHERE node_id=?_n)wt),?_n,',%') OR node_id=?_n ;

其中?_n表示要修改的节点的id,?_p表示要修改的节点的新父节点的id.
注:使用该sql一定要先更新子节点的祖先路径,再更新本节点的祖先路径,如果是使用存储过程的话就可以无视这一点了.

删除(含子节点)

DELETE FROM t_node WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')

删除的核心在于where,和获取所有后代节点的where可以说是完全一样的.
同样要主要先删除所有后代节点,再删除本节点;

4 祖先路径的重置

有可能你此前的某个数据库表格没有使用过祖先路径,但已经积累了一定量的数据,或者之前使用了祖先路径,但由于某种原因导致祖先路径的一些数据更新错误.因为祖先路径本质上是一个冗余字段,所以还是可以通过父id的方式将之还原重置.
以下为机构表的一个重置存储过程,供以参考:

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_reset_organ_path`(OUT resultMark varchar(50))
BEGIN 
    /*
    使用前的说明:
    1.本存储过程非客户使用,且自己人使用频率同样较低,故过程更方便调试,但效率不是很高;
    2.如果执行SELECT * FROM t_organ WHERE organ_id<parent_organ_id(即父机构产生于子机构之后)后的数据为空,则可以考虑使用分段模式(速度会快一些).
    3.如果2中所述数据不为空,使用分段会使该id对应的机构及其子机构的ancestor_path不正确.结果为partfail.
    */
    DECLARE intACount INT(11) DEFAULT 0;

    DECLARE intPCount INT(11) DEFAULT 0;
    DECLARE intPIndex INT(11) DEFAULT 0;
    DECLARE intPOrganId INT(11) DEFAULT 0;
    DECLARE strPPath VARCHAR(100) DEFAULT '';
    DECLARE intLoopDone INT(11) DEFAULT 0;

    DECLARE intRCount INT(11) DEFAULT 0;
    DECLARE intRIndex INT(11) DEFAULT 0;
    DECLARE intROrganId INT(11) DEFAULT 0;

    DROP TABLE IF EXISTS tmp_aOrganIdList;
    CREATE TEMPORARY TABLE tmp_aOrganIdList(
        rowid INT(11) auto_increment PRIMARY KEY,
        organ_id INT(11),
        p_organ_id INT(11)
    );

    DROP TABLE IF EXISTS tmp_pOrganIdList;
    CREATE TEMPORARY TABLE tmp_pOrganIdList(
        rowid INT(11) auto_increment PRIMARY KEY,
        organ_id INT(11)
    );
/**/
    DROP TABLE IF EXISTS tmp_cOrganIdList;
    CREATE TEMPORARY TABLE tmp_cOrganIdList(
        rowid INT(11) auto_increment PRIMARY KEY,
        organ_id INT(11)
    );

    DROP TABLE IF EXISTS tmp_rOrganIdList;
    CREATE TEMPORARY TABLE tmp_rOrganIdList(
        rowid INT(11) auto_increment PRIMARY KEY,
        organ_id INT(11),
        p_organ_id INT(11),
        ancestor_path VARCHAR(100)
    );

    INSERT INTO tmp_aOrganIdList (organ_id,p_organ_id)
    (SELECT organ_id,parent_organ_id FROM t_organ);-- 测试的时候limit: LIMIT 0,100

    INSERT INTO tmp_pOrganIdList (organ_id) VALUES (0);
    INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path) VALUES (0,-1,'');

    WHILE ((SELECT COUNT(1) FROM tmp_aOrganIdList)>0 AND intLoopDone=0) DO -- 持续循环,当没有organId数据为止(如果中间机构中断,则可能陷入死循环)
        SELECT COUNT(1) FROM tmp_pOrganIdList INTO intPCount;-- 当前父机构id的缓存区
        SET intPIndex=0;
        WHILE intPIndex<=intPCount DO -- 对每个当前查询到的父id进行对应操作
            
            SELECT organ_id FROM tmp_pOrganIdList LIMIT intPIndex,1 INTO intPOrganId;
            SELECT ancestor_path FROM tmp_rOrganIdList WHERE organ_id=intPOrganId INTO strPPath;

            INSERT INTO tmp_cOrganIdList (organ_id) (SELECT organ_id FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);-- 次级机构id的缓存区
            -- SELECT COUNT(1) FROM tmp_pOrganIdList INTO intDelCount;
            INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path)
            (SELECT organ_id,intPOrganId,CONCAT(strPPath,intPOrganId,',') FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);
            DELETE FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId;

            SET intPIndex=intPIndex+1;
        END WHILE;
        
        DELETE FROM tmp_pOrganIdList;
        IF (SELECT COUNT(1) FROM tmp_cOrganIdList)>0 THEN
            INSERT INTO tmp_pOrganIdList (organ_id) (SELECT organ_id FROM tmp_cOrganIdList);
            DELETE FROM tmp_cOrganIdList;
        ELSE
            SET intLoopDone=1;
        END IF;
        -- SELECT * FROM tmp_pOrganIdList;
        -- SELECT COUNT(1) FROM tmp_aOrganIdList;
        -- SELECT intLoopDone;
    END WHILE;

    -- SELECT * FROM tmp_rOrganIdList;-- 想要查看测试的结果,请看此表
    SELECT COUNT(1) FROM tmp_rOrganIdList INTO intRCount;
    WHILE intRIndex<=intRCount DO
        SELECT organ_id,ancestor_path FROM tmp_rOrganIdList LIMIT intRIndex,1 INTO intROrganId,strPPath;
        UPDATE t_organ SET ancestor_path=strPPath WHERE organ_id=intROrganId;
        SET intRIndex=intRIndex+1;
    END WHILE;

    IF (SELECT COUNT(1) FROM tmp_aOrganIdList)=0 THEN
        SET resultMark='perfect';
    ELSE
        SET resultMark='partfail';
    END IF;

END