mysql树结构数据处理

作者: 比轩 | 来源:发表于2016-12-31 01:12 被阅读296次

mysql树结构数据处理
MySQL 查询树结构
教你如何使用 MySQL8 递归
MySql_web树结构
索引
数据分析进阶技能——Excel连接数据库
MySQL树结构递归查询处理
【MongoDB】MongoDB如何实现insert tab s
如何使用pymysql连接MySQL数据库
MySQL必知必会系列二

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

节点的等级计算

需求：已知节点id，需要返回节点的level，也就是层级（root算0级，往下一层算1级这样）

实现思路：

函数实现，传入节点ID，返回节点level
由子节点向上查，是单线，所以一个循环就能搞定

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函数不能递归调用，这是重要前提！

实现思路：

将查询到的节点id存储到集合中
循环实现，获取集合中节点的子节点，再加入集合中
知道子节点为NULL停止

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

concat()连接字符串，mybaits中比较常见
group_concat() 将集合并为字符串
find_in_set() 类似于like，见图解释

** 贴上官方文档，功能一看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出来，主键自增，其它两个参数是为了完成项目功能加进去的，不影响整体思路和理解。

说明：

temp_tree_detial 就是建立的临时表
hss_setup_trx_detail_b 是存储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