获取错误代码：2013在运行递归存储过程时查询期间与MySQL Server的连接丢失

Question

我已经阅读了几乎所有相关的问题。但是它对我不起作用。 我正在尝试通过整个子父母数据进行遍历，以便我进行了递归存储程序。

CREATE DEFINER=`root`@`localhost` PROCEDURE `g_tree`(in p_parent varchar(30), in depth int)
    BEGIN


DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;


/*table consisting all child*/
if depth = 0 then 
    drop table if exists final_child_tree;
    create temporary table final_child_tree (final_child varchar(15));
end if;


/*child_t stores all connections of p_parent as single record of set of strings*/
    drop temporary table if exists child_t;
    create temporary table child_t (t_child varchar(15), iterated int default 0);
    
/*make table consitsing all conneciton in distinct record*/
    set @list := (select child from connections where parent = p_parent );
    iterator:
    LOOP
    select * from child_t;
    IF CHAR_LENGTH(TRIM(@list)) = 0 OR @list IS NULL THEN

        select * from final_child_tree;
        LEAVE iterator;
        
    END IF;
    select * from final_child_tree;
    SET _next = SUBSTRING_INDEX(@list,',',1);

    SET _nextlen = CHAR_LENGTH(_next);

    SET _value = TRIM(_next);

    INSERT INTO child_t VALUES (_value,0);
    insert into final_child_tree values (_value);
    SET @list = INSERT(@list,1,_nextlen + 1,'');
    
    END LOOP;

/*get child of child*/
iterator :
loop 
    if exists (select t_child from child_t where iterated = 0 limit 1) then
        set @new_parent := (select t_child from child_t where iterated = 0 limit 1);
        set @childExists := (select count(*) from connections where parent = @newParent);
        if @new_parent != 0 and @childExists != 0 then 
            call g_tree (@new_parent,1);
        end if;
    else 
        leave iterator;
    end if;
end loop;

END

它丢了一个错误：

错误代码：2013在查询期间与MySQL Server丢失了连接

错误代码：2013年在查询表格

：连接：

 +--------+----------------------+
|父|孩子|
+--------+-----------------+
| 1 | C1，C2，C3 |
| C1 | C11，C12，C13 |
| C2 | C21，C22 |
+--------+-----------------+
 

还有其他更好的方法吗？

Answer 1

存储层次数据的归一化方法是存储对父母的引用，而不是存储孩子的逗号分隔列表。

id	parent_id
1	null
C1	1
C2	1
C3	1
C11 C1	C1 C1
C1 C1	C1
C1 C1	C1
C21	C2 C2
C22	C2

现在您可以使用递归CTE查询来从树的给定节点中获取所有后代：

WITH RECURSIVE cte (parent_id) AS (
 SELECT id FROM connection WHERE parent_id IS NULL
 UNION ALL
 SELECT id FROM connection JOIN cte on connection.parent_id = cte.parent_id
)
SELECT * FROM cte;