我面临相同的情况。我想从同一张表中获取特定父级的所有子列表,然而MySQL 8以下版本不提供递归CTE。
我通过递归存储过程解决了我的问题。在其中,我们需要将max_sp_recursion_depth设置为递归存储过程调用。
我的表结构如下:
![enter image description here](https://istack.dev59.com/4OcXb.webp)
我的带递归的存储过程如下:
DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
SET @@SESSION.max_sp_recursion_depth=25; $$
CREATE PROCEDURE getAllChilds (IN inId int(11), INOUT list_obj text, IN end_arr CHAR(1))
BEGIN
DECLARE stop_cur INTEGER DEFAULT 0;
DECLARE _id INTEGER DEFAULT 0;
DECLARE _name VARCHAR(20) DEFAULT 0;
DEClARE curSelfId CURSOR FOR SELECT id, name FROM new_table where parentId = inId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop_cur = 1;
OPEN curSelfId;
getSelfId: LOOP
FETCH curSelfId INTO _id, _name;
IF stop_cur = 1 THEN LEAVE getSelfId; END IF;
IF list_obj is null or list_obj = "" then
SET list_obj = CONCAT("[","{",'"name":"',_name,'","id":"',_id,'"',"}");
else
SET list_obj = CONCAT(list_obj,',', "{",'"name":"',_name,'","id":"',_id,'"',"}");
end if;
CALL getAllChilds(_id,list_obj,"");
END LOOP getSelfId;
CLOSE curSelfId;
IF end_arr is not null and end_arr != "" then
SET list_obj = CONCAT(list_obj,end_arr);
SELECT @ids;
end if;
END$$
DELIMITER ;
调用这个存储过程需要传递3个参数:
- 父级id
- 空/ null对象
结束数组标志(为追加只附加最后一个对象而非所有对象)。
CALL getAllChilds(1,@ids,']');
使用此存储过程,您可以获取JSON字符串中的所有子级别。
您可以解析此JSON字符串并使用任何JSON解析器将其转换为任何对象。
或者
我们可以将此答案包装在存储过程中,以便在任何编程语言中使用它,如JAVA。
我们将其包装成存储过程是因为无法在查询中使用:=。
这里我们使用了find_in_set函数。
我的存储过程(没有递归)。
DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
CREATE PROCEDURE getAllChilds (IN inId int(11))
BEGIN
select id,
name,
parentid
from
(select * from new_table
order by parentid, id) new_table,
(select @pv := inId) initialisation
where
find_in_set(parentid, @pv) > 0
and @pv := concat(@pv, ',', id);
END$$
DELIMITER ;
调用此存储过程只需要父级 Id。
CALL getAllChilds(1);