MySQL implements tree traversal

Two fields with parent-child relationships in a table, such as empno and manager, need to be traversed in this structure. In Oracle, you can use connect by to solve the problem. For more information, see connect by 5.1, which is not supported yet (it is said that it has been included in to do). You need to write the process or function by yourself.

1. Create a test table and data:

Drop table if exists 'channel'; </P> <p> Create Table 'channel' (<br/> 'id' int (11) not null auto_increment, <br/> 'cname' varchar (200) default null, <br/> 'parent _ id' int (11) default null, <br/> Primary Key ('id') <br/>) engine = MyISAM auto_increment = 19 default charset = utf8; </P> <p>/* data for the table 'channel' */</P> <p> insert into 'channel' ('id', 'cname ', 'parent _ id') <br/> values (13, 'homepage',-1), <br/> (14, 'tv580',-1 ), <br/> (15, 'life 100',-1), <br/> (16, 'slide on the Left ', 13), <br/> (17, 'help', 14), <br/> (18, 'topic introduction', 17); </P> <p>

 

Ii. Implement tree traversal using temporary tables and recursive procedures (MySQL UDF cannot be called recursively ):

Delimiter $ </P> <p> Use 'db1' $ </P> <p> -- traverse subnodes from a node <br/> -- recursively generate a temporary table data <br/> drop procedure if exists 'createchildlst '$ </P> <p> Create procedure 'createchildlst' (in rootid int, in ndepth INT) <br/> begin <br/> declare done int default 0; <br/> declare B INT; <br/> declare cur1 cursor for select ID from channel where parent_id = rootid; <br/> declare continue handler for not found set done = 1; <br/> set max_sp_recursion_depth = 12; </P> <p> insert into tmplst values (null, rootid, ndepth); </P> <p> open cur1; </P> <p> fetch cur1 into B; <br/> while done = 0 DO <br/> call createchildlst (B, ndepth + 1 ); <br/> fetch cur1 into B; <br/> end while; </P> <p> close cur1; <br/> end $ </P> <p> -- trace the root node from a node <br/> -- recursively generate temporary table data <br/> drop procedure if exists 'createparentlst' $ </P> <p> Create procedure 'createparentlst' (in rootid int, in ndepth INT) <br/> begin <br/> declare done int default 0; <br/> declare B INT; <br/> declare cur1 cursor for select parent_id from channel where id = rootid; <br/> declare continue handler for not found set done = 1; <br/> set max_sp_recursion_depth = 12; </P> <p> insert into tmplst values (null, rootid, ndepth); </P> <p> open cur1; </P> <p> fetch cur1 into B; <br/> while done = 0 DO <br/> call createparentlst (B, ndepth + 1 ); <br/> fetch cur1 into B; <br/> end while; </P> <p> close cur1; <br/> end $ </P> <p> -- Implement functions similar to Oracle sys_connect_by_path <br/> -- output the ID path of a node in the recursive process <br/> drop procedure if exists 'createpathlst' $ </P> <p> Create procedure 'createpathlst' (in NID int, in delimit varchar (10), inout pathstr varchar (1000) <br/> begin <br/> declare done int default 0; <br/> declare parentid int default 0; <br/> declare cur1 cursor for <br/> select T. parent_id, Concat (cast (T. parent_id as char), delimit, pathstr) <br/> from channel as t where T. id = NID; </P> <p> declare continue handler for not found set done = 1; <br/> set max_sp_recursion_depth = 12; </P> <p> open cur1; </P> <p> fetch cur1 into parentid, pathstr; <br/> while done = 0 DO <br/> call createpathlst (parentid, delimit, pathstr); <br/> fetch cur1 into parentid, pathstr; <br/> end while; </P> <p> close cur1; <br/> end $ </P> <p> -- name path of a node output in the recursive process <br/> drop procedure if exists 'createpathnamelst' $ </P> <p> Create procedure 'createpathnamelst' (in NID int, in delimit varchar (10), inout pathstr varchar (1000) <br/> begin <br/> declare done int default 0; <br/> declare parentid int default 0; <br/> declare cur1 cursor for <br/> select T. parent_id, Concat (T. cname, delimit, pathstr) <br/> from channel as t where T. id = NID; </P> <p> declare continue handler for not found set done = 1; <br/> set max_sp_recursion_depth = 12; </P> <p> open cur1; </P> <p> fetch cur1 into parentid, pathstr; <br/> while done = 0 DO <br/> call createpathnamelst (parentid, delimit, pathstr); <br/> fetch cur1 into parentid, pathstr; <br/> end while; </P> <p> close cur1; <br/> end $ </P> <p> -- call the function output ID path <br/> drop function if exists 'fn _ tree_path' $ </P> <p> Create Function 'fn _ tree_path '(NID int, delimit varchar (10) returns varchar (2000) charset utf8 <br/> begin <br/> declare pathid varchar (1000 ); </P> <p> set @ pathid = cast (NID as char); <br/> call createpathlst (NID, delimit, @ pathid ); </P> <p> return @ pathid; <br/> end $ </P> <p> -- call the function output name path <br/> drop function if exists 'fn _ tree_pathname' $ </P> <p> Create Function 'fn _ tree_pathname' (NID int, delimit varchar (10) returns varchar (2000) charset utf8 <br/> begin <br/> declare pathid varchar (1000 ); </P> <p> set @ pathid = ''; <br/> call createpathnamelst (NID, delimit, @ pathid); </P> <p> return @ pathid; <br/> end $ </P> <p> -- call process output subnode <br/> drop procedure if exists 'showchildlst' $ </P> <p> create procedure 'showchildlst' (in rootid INT) <br/> begin <br/> drop temporary table if exists tmplst; <br/> create temporary table if not exists tmplst <br/> (SNO int primary key auto_increment, id int, depth INT); </P> <p> call createchildlst (rootid, 0); </P> <p> select channel. ID, Concat (space (tmplst. depth * 2), '--', channel. cname) Name, channel. parent_id, tmplst. depth, fn_tree_path (channel. ID, '/') path, fn_tree_pathname (channel. ID, '/') pathname <br/> from tmplst, channel where tmplst. id = channel. id order by tmplst. sno; <br/> end $ </P> <p> -- output parent node of the call Process <br/> drop procedure if exists 'showparentlst' $ </P> <p> create procedure 'showparentlst' (in rootid INT) <br/> begin <br/> drop temporary table if exists tmplst; <br/> create temporary table if not exists tmplst <br/> (SNO int primary key auto_increment, id int, depth INT); </P> <p> call createparentlst (rootid, 0); </P> <p> select channel. ID, Concat (space (tmplst. depth * 2), '--', channel. cname) Name, channel. parent_id, tmplst. depth, fn_tree_path (channel. ID, '/') path, fn_tree_pathname (channel. ID, '/') pathname <br/> from tmplst, channel where tmplst. id = channel. id order by tmplst. sno; <br/> end $ </P> <p> delimiter; <br/>

Iii. Test

Call showchildlst (-1); <br/> call showchildlst (13); <br/> call showchildlst (14); <br/> call showchildlst (17 ); <br/> call showchildlst (18); </P> <p> call showparentlst (-1); <br/> call showparentlst (13 ); <br/> call showparentlst (14); <br/> call showparentlst (17); <br/> call showparentlst (18); <br/>

Iv. Legacy issues
1. because MySQL does not support dynamic cursors, it is difficult to make a common process or function. You can use two temporary tables for conversion (with recursive calls removed at the same time.
2. At present, no matter which method is implemented, the efficiency is not very good. I hope MySQL can implement the connect by function of Oracle itself and it should be relatively optimized.

 

Reference: Query all Tree nodes in MySQL