MySQL节点级联查询

客官°小女子只卖身不卖艺 2022-05-31 04:51 599阅读 0赞

**需求**

根据根节点id查询其所有子节点id（包含根节点）

**表结构**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70]

CREATE TABLE `sys_dept` (
      `dept_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
      `parent_id` bigint(20) NOT NULL COMMENT '上级部门ID',
      `name` varchar(60) NOT NULL COMMENT '部门/小组名',
      `dept_type` int(3) NOT NULL DEFAULT '0' COMMENT '部门类型（1部门，2小组）',
      `order_num` int(11) NOT NULL COMMENT '排序',
      `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除（-1：已删除，0：正常）',
      PRIMARY KEY (`dept_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='部门表';

**节点级联查询函数**

delimiter // 
    CREATE FUNCTION `getChildList`(rootId bigint)
    RETURNS varchar(1000) 
    
    BEGIN
    	DECLARE temp VARCHAR(1000);
        DECLARE tempChild VARCHAR(1000);
    
        SET temp = '$';
        SET tempChild =cast(rootId as CHAR);
    
        WHILE tempChild is not null DO
        	SET temp = concat(temp,',',tempChild);
            SELECT group_concat(dept_id) INTO tempChild FROM  sys_dept where FIND_IN_SET(parent_id,tempChild) > 0;
       	END WHILE;
        RETURN temp; 
    END
    //

**查询根节点id为4的子节点id**

select dept_id from sys_dept where FIND_IN_SET(dept_id,getChildList(4));

**结果**  
![在这里插入图片描述][20190328154440397.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdGlhbnhpYW5nX2thb2xh_size_16_color_FFFFFF_t_70]: /images/20220531/002d862e58a0400cb2bba05eeff4ae0e.png
[20190328154440397.png]: /images/20220531/af98db1a56c147328e32d4ae292c0dce.png