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

原创已于 2023-04-04 17:53:31 修改 · 873 阅读

本内容遵循CC 4.0 BY-SA版权协议

于 2023-04-04 17:06:59 首次发布

文章介绍了在从Oracle迁移到MySQL的项目中，如何处理Oracle特有的Startwith…ConnectBy递归树查询功能。通过创建名为getChildList的自定义函数，利用FIND_IN_SET和GROUP_CONCAT函数实现了类似的功能，能够在MySQL中查询树形结构的数据。并提供了创建表、插入数据及调用函数查询的示例。

-因项目迁移，使用的数据库也需要从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

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

SELECT * FROM treeList   WHERE FIND_IN_SET(id, getChildList('2'));

本文亲测有效

标签

#数据库 #mysql #oracle