mysql数据库程序员

mysql树结构数据处理

2016-12-31  本文已影响296人  比轩

mysql处理树比较麻烦,不能像oracle那样直接start_with(rootnode)这样,所以只能利用存储过程或者函数来实现比较复杂的逻辑操作了,比如树的等级计算,子节点查询,树的复制等等。

节点的等级计算

需求:已知节点id,需要返回节点的level,也就是层级(root算0级,往下一层算1级这样)

实现思路:

CREATE DEFINER = `root`@`localhost` FUNCTION `getLevel`(`id` bigint)
 RETURNS int(11)
BEGIN
 -- level 待返回的等级变量
 -- pId 用来存储父ID
    declare level bigint;
    declare pId bigint;
    
  -- 变量初始化
    set level = 0;

    select PARENT_DETAIL_ID into pId from hss_setup_trx_detail_b where TRX_DETAIL_ID = id;

    while pId is not null do

    set level = level + 1;

    select PARENT_DETAIL_ID into pId from hss_setup_trx_detail_b where TRX_DETAIL_ID = pId;
    
    end while;

    RETURN level;
END;

树的子节点查询

这个问题其实我自己想了一段时间,也实现了,但是过程比较麻烦,就不贴了。后来参考了前辈们的思路,使用FIND_IN_SET()基本可以完美的解决节点的存储问题。感觉还是sql太菜了,所以实现的思路也一直向java或者c++靠,导致很多问题不知道怎么解决,继续努力学习吧。

需求:废话不多说了,回到正题。需求基本就是,已知树中一点节点id(root或者任意子节点都OK),获取所有子节点的集合。

mysql函数不能递归调用,这是重要前提!

实现思路:

实现此功能需要用到的三个mysql内置函数:

** 贴上官方文档,功能一看demo便知**

FIND_IN_SET
CREATE DEFINER = `root`@`localhost` FUNCTION `getTreeNodes`(`id` bigint)
 RETURNS varchar(1000)
BEGIN
    DECLARE nodes VARCHAR(1000);
    DECLARE tempNodes VARCHAR(1000);
    
  -- nodes 必须初始化,否则null并任意str都返回null
    SET nodes = '$';
    SET tempNodes =cast(id as CHAR);

    WHILE tempNodes is not null DO
    SET nodes = concat(nodes ,',',sTempChd);
    SELECT group_concat(TRX_DETAIL_ID) INTO tempNodes FROM hss_setup_trx_detail_b where FIND_IN_SET(PARENT_DETAIL_ID,tempNodes )>0;

    end while;
    RETURN nodes ;
END;

复制树

基本思路还是按照查询树的思路,但是需要使用到临时表来存储和更新父节点数据。因为游标的特性(单向,不能更新,只能循环一遍),有点类似于java的迭代器。
所以采用临时表才对数中的父ID进行更新,然后插入表中,循环往复这样子。

直接贴代码,逻辑并不复杂,整体类似上述的树的查询。传入的参数ID就是root节点,函数实现的功能就是原表里复制一个tree出来,主键自增,其它两个参数是为了完成项目功能加进去的,不影响整体思路和理解。

说明:

思路:首先获取原树的所有节点,然后查询并建立临时表来存储数据。接着,插入root节点,获取root节点的新id,更新临时表里root节点的pId,之后就是重复此过程,知道临时表里面的数据全部插入原表结束。最后返回了新插入数据的节点集合。

CREATE DEFINER = `root`@`localhost` FUNCTION `copyTree`(`id` bigint ,`trxId` bigint, `createBy` bigint)
 RETURNS varchar(1000)
-- 参数: name:id  type:bigint, name:trxId  type:bigint  name:createBy  type:bigint
-- 返回新增的tree节点集合: varchar(1000)
-- 功能: 根据传入的root节点的id,在原表里复制一个新的tree
BEGIN
    -- 用来存储之前tree的id集合
    declare sTemp VARCHAR(1000);
    declare sTempChd VARCHAR(1000);
    
    -- 用来存储新的tree的id集合
    DECLARE sTempNew VARCHAR(1000);
    declare sTempChdNew VARCHAR(1000);
    
    -- 存储上次插入的数据的新id
    declare last_insert_detail_id bigint default 0;

    -- 上次插入数据的原ID
    declare temp_id bigint;

    -- 计数器,存储临时表中剩余的数据,控制循环
    declare count_num bigint;
    
    -- 临时存储插入数据的变量
    declare TEMP_TRX_ID bigint;
    declare TEMP_DETAIL_TRX_TYPE varchar(240);
    declare TEMP_DETAIL_TRX_TYPE_NAME varchar(240);
    declare TEMP_CATEGORY_CODE varchar(30);
    declare TEMP_PARENT_DETAIL_ID bigint;
    declare TEMP_IS_DISPLAY_FLAG varchar(1);
    declare TEMP_CREATED_BY bigint;
    declare TEMP_CREATION_DATE bigint;
    
    -- 初始化变量
    SET sTemp = '$';
    SET sTempNew = '$';
    
    DROP TEMPORARY TABLE IF EXISTS temp_tree_detial;

    CREATE TEMPORARY TABLE temp_tree_detial  SELECT *  FROM hss_setup_trx_detail_b where FIND_IN_SET(TRX_DETAIL_ID,getTreeNodes(id))>0;
    
    -- 拿到root节点的记录,并直接插入原表
    insert into hss_setup_trx_detail_b (TRX_ID,DETAIL_TRX_TYPE,DETAIL_TRX_TYPE_NAME,
        CATEGORY_CODE,PARENT_DETAIL_ID,IS_DISPLAY_FLAG
        ,CREATED_BY,CREATION_DATE)
    select trxId,DETAIL_TRX_TYPE,DETAIL_TRX_TYPE_NAME,
        CATEGORY_CODE,PARENT_DETAIL_ID,IS_DISPLAY_FLAG
        ,createBy,now() from temp_tree_detial where TRX_DETAIL_ID = id;

    -- 获取刚刚插入的数据的id
    SELECT LAST_INSERT_ID() into last_insert_detail_id;

    -- temp_id 用来存储插入前记录的detail_id
    set temp_id = id;

    SET sTempChd =cast(temp_id as CHAR);
    SET sTemp = concat(sTemp,',',sTempChd);

    SET sTempChdNew =cast(last_insert_detail_id as CHAR);
    SET sTempNew = concat(sTempNew,',',sTempChdNew);

    delete from temp_tree_detial where FIND_IN_SET(TRX_DETAIL_ID,sTemp)>0;

    -- 获取已经插入的节点的子节点数量,大于零0进入循环
    select count(1)  into count_num from temp_tree_detial where FIND_IN_SET(PARENT_DETAIL_ID,sTemp)>0;
    
    while count_num > 0  do

        
    -- 根据刚刚插入的节点,更新子节点的pid
    update temp_tree_detial set PARENT_DETAIL_ID = last_insert_detail_id where PARENT_DETAIL_ID = temp_id;

        
    -- 从已经插入了的节点的子节点集合中获取一条数据
    select TRX_DETAIL_ID,TRX_ID,DETAIL_TRX_TYPE,DETAIL_TRX_TYPE_NAME, CATEGORY_CODE,PARENT_DETAIL_ID,IS_DISPLAY_FLAG ,CREATED_BY,CREATION_DATE
    into temp_id,TEMP_TRX_ID,TEMP_DETAIL_TRX_TYPE,TEMP_DETAIL_TRX_TYPE_NAME,TEMP_CATEGORY_CODE,TEMP_PARENT_DETAIL_ID,TEMP_IS_DISPLAY_FLAG,TEMP_CREATED_BY,TEMP_CREATION_DATE
    from temp_tree_detial where FIND_IN_SET(PARENT_DETAIL_ID,sTempNew)>0 limit 1;
    

    -- 插入刚刚获取到的数据
    insert into hss_setup_trx_detail_b (TRX_ID,DETAIL_TRX_TYPE,DETAIL_TRX_TYPE_NAME, CATEGORY_CODE,PARENT_DETAIL_ID,IS_DISPLAY_FLAG ,CREATED_BY,CREATION_DATE)
    values (trxId,TEMP_DETAIL_TRX_TYPE,TEMP_DETAIL_TRX_TYPE_NAME,TEMP_CATEGORY_CODE,TEMP_PARENT_DETAIL_ID,TEMP_IS_DISPLAY_FLAG,createBy,now());

    SELECT LAST_INSERT_ID() into last_insert_detail_id;

    -- 更新数据集
    SET sTempChdNew =cast(last_insert_detail_id as CHAR);
    SET sTempNew = concat(sTempNew,',',sTempChdNew);
    SET sTempChd =cast(temp_id as CHAR);
    SET sTemp = concat(sTemp,',',sTempChd);

    -- 从临时表里面删除已经插入原表的记录
    delete from temp_tree_detial where FIND_IN_SET(TRX_DETAIL_ID,sTemp)>0;

    select count(1)  into count_num from temp_tree_detial;

    end while;

    RETURN sTempNew;
END

上一篇 下一篇

猜你喜欢

热点阅读