mysql树状数据的数据库设计

Wesley13
• 阅读 523

0 树状数据的分类

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

分类

特点

固定数量层级

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

可变数量层级

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

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

节点code

节点名称

节点层级

父级节点code

1级祖先code

2级祖先cdoe

010000

公司1

1

000000

null

null

020000

公司2

1

000000

null

null

010300

制造部

2

010000

010000

null

010400

品质部

2

010000

010000

null

010301

前工程制造

3

010300

010000

010300

010303

组装制造

3

010300

010000

010300

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

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

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

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,
实际设计时,还可考虑加入层级这个冗余字段,但我在实际使用的过程中很少用到这个字段.
这样,在加了这个字段之后,任意节点的所有祖先节点信息就都可通过这样一条数据全部获取.
祖先路径的设定具有以下特点:

  1. 没有父节点的根节点,父id默认为'0',祖先路径默认为'0,';

  2. 每增加的一个子节点,祖先路径都是在要增加的子节点的父节点的祖先路径上增加父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 祖先路径的查询

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

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

    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=?
  1. 查询某个节点的所有祖先节点

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

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

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

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

  1. 插入

    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.

  1. 更新(含子节点)
    如果更新的时候,父节点的位置没有变化,则不必考虑太多;
    如果需要更新所在父节点,相比于最简单的树节点设计模式,增加祖先路径的方式除了在更新当前节点本身的父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一定要先更新子节点的祖先路径,再更新本节点的祖先路径,如果是使用存储过程的话就可以无视这一点了.

  1. 删除(含子节点)

    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
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
京东云开发者 京东云开发者
7个月前
Vitess全局唯一ID生成的实现方案 | 京东云技术团队
为了标识一段数据,通常我们会为其指定一个唯一id,比如利用MySQL数据库中的自增主键。但是当数据量非常大时,仅靠数据库的自增主键是远远不够的,并且对于分布式数据库只依赖MySQL的自增id无法满足全局唯一的需求。因此,产生了多种解决方案,如UUID,Sn
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这