I have read almost all related questions. but its not working for me. I am trying to traverse through whole child parent data to do so I have made recursive stored procedure.

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

and its throwing an error :

Error Code: 2013 Lost connection to MySQL server during query

tables :

connection :

 -------- ------------- 
| parent | child       |
 -------- ------------- 
| 1      | c1,c2,c3    |
| c1     | c11,c12,c13 |
| c2     | c21,c22     |
 -------- ------------- 

is there any other better way to do this ?

CodePudding user response：

The normalized way to store hierarchical data is to store a reference to the parent, not store a comma-separated list of the children.

Now you can use a recursive CTE query to get all the descendants from a given node of the tree:

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;