MYSQL实现Oracle的Start with…Connect By递归树查询

阳光穿透心脏的1/2处 2022-06-04 08:24 251阅读 0赞

因项目迁移，使用的数据库也需要从Oracle迁移到MySQL 其中有个功能使用到了Oracle的Start with…Connect By递归树查询，而MySQL中没有此函数，但可以通过自定义函数的方式来解决这个问题

**创建表**

create table treeList( id varchar(10), -- 节点ID name varchar(10), -- 节点名称 pId varchar(10) -- 父ID )

**插入测试数据**

insert into treeList values(1,'中国',null);
    insert into treeList values(2,'北京',1);
    insert into treeList values(3,'上海',1);
    insert into treeList values(4,'深圳',1);
    insert into treeList values(5,'海淀',2);
    insert into treeList values(6,'朝阳',2);
    insert into treeList values(7,'昌平',2);
    insert into treeList values(8,'丰台',2);

**创建函数getChildList**

CREATE FUNCTION getChildList (rootId VARCHAR(100)) -- rootId为要查询的节点 RETURNS VARCHAR(1000) BEGIN DECLARE pTemp VARCHAR(1000);  
        DECLARE cTemp VARCHAR(1000); -- 定义两个临时变量
    
        SET pTemp = '';  
        SET cTemp = rootId;
    
        WHILE cTemp is not null DO if (pTemp = '') then SET pTemp = cTemp;
             elseif(pTemp <> '') then
             SET pTemp = concat(pTemp,',',cTemp); -- 所有节点连接成字符串
             end if;
             SELECT group_concat(id) INTO cTemp FROM treeList WHERE FIND_IN_SET(pId,cTemp)>0; 
         END WHILE;  
         RETURN pTemp;  
    END

**执行方法 查询节点为“2”下的所有节点**

select getChildList('2') as ids

**运行结果**

![这里写图片描述][SouthEast]

作者：[itmyhome][]

[SouthEast]: /images/20220604/d002313c00344a08ba370666230507a7.png
[itmyhome]: http://blog.csdn.net/itmyhome1990