mysql - MySql递归过程：如何修复无限循环

Question

我正在尝试在 MySql 中编写存储过程以从树结构中删除整个子树。

        CREATE PROCEDURE delete_with_children(IN node INT)
        BEGIN
            DECLARE child_id INT;
            DECLARE finished INT DEFAULT 0;
            DECLARE cur CURSOR FOR 
                SELECT id FROM nodes WHERE parent_id = node;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

            DELETE from nodes where id = node;
            OPEN cur;
            WHILE(finished < 1) DO
                FETCH cur INTO child_id;
                CALL delete_with_children(child_id);
            END WHILE;
            CLOSE cur;
        END;

它很好地插入了自己，但是当我尝试使用该结构的参数 4 执行它时https://imgur.com/a/kqW7ulS，我收到这样的错误：递归限制 100（由 max_sp_recursion_depth 变量设置）超出了例程 delete_with_children。如果有人帮助我，我将不胜感激。

标签： mysqlstored-procedures