最近新项目使用改用Mysql,遇到了一个递归查询的问题,查阅资料后,发现mysql并没有类似orcale的connect by递归查询的方法。
关于oracle的查询, 可以查看我的另一篇博文:
https://blog.csdn.net/it_freshman/article/details/78526871
查阅资料,发现大致有两种解决思路:
- 使用function
- 使用procedure
现有数据库表tpc_oragranization(id:id,父id:parentid),模型&数据参见下图:
使用function
话不多说,直接撸代码:
drop FUNCTION IF EXISTS f_getChildren4Organization;
create function f_getChildren4Organization(v_root_id INT)
returns VARCHAR(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChildren VARCHAR(4000);
SET oTemp = '';
SET oTempChildren = CAST(v_root_id AS CHAR);
WHILE oTempChildren IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChildren);
SELECT GROUP_CONCAT(ID) INTO oTempChildren FROM tpc_ORGANIZATION WHERE FIND_IN_SET(PARENTID,oTempChildren) > 0;
END WHILE;
RETURN oTemp;
end;
测试调用:
select f_getChildren4Organization(1); //结果: ,1,2,3,41,100,101,102
select f_getChildren4Organization(2); //结果: ,2,102
select f_getChildren4Organization(3); //结果: ,3,100,101
select f_getChildren4Organization(4); //结果: ,4
使用function,由于没有找到function使用动态sql的方法,所以针对这种方式,每一个需要递归查询的业务,都需要使用写一个函数不太方便。
使用存储过程
存储过程代码:
drop PROCEDURE IF EXISTS p_getChildren;
CREATE PROCEDURE p_getChildren(
IN in_root_id INT, /*根节点id*/
IN in_table_name VARCHAR(64),/*表名*/
IN in_id_column_name VARCHAR(64),/*id字段名*/
IN in_pid_column_name VARCHAR(64),/*父id字段名*/
OUT out_result VARCHAR(4000)
)
BEGIN
DECLARE v_sql VARCHAR(4000);
DECLARE oTemp VARCHAR(4000);
DECLARE oChildrenTemp VARCHAR(4000);
SET oTemp = '$';
SET oChildrenTemp = CAST(in_root_id AS CHAR);
WHILE oChildrenTemp IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oChildrenTemp);
set v_sql = concat('SELECT GROUP_CONCAT(',in_id_column_name,') INTO @oGrandChildrenTemp FROM ',in_table_name,' WHERE FIND_IN_SET(',in_pid_column_name,',\'',oChildrenTemp,'\') > 0;');
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
set oChildrenTemp = @oGrandChildrenTemp;
END WHILE;
set out_result = oTemp;
end;
调用测试:
CALL p_getChildren(1,'tpc_organization','ID','PARENTID',@sum);
select @sum; // $,1,2,3,41,100,101,102
CALL p_getChildren(2,'tpc_organization','ID','PARENTID',@sum);
select @sum; //$,2,102
CALL p_getChildren(3,'tpc_organization','ID','PARENTID',@sum);
select @sum; //$,3,100,101
最终测试可用。使用procedure的方式能够支持动态sql,针对多业务需要递归查询的场景,只需要一个存储过程即可满足。