Detailed description of MySQL implementation tree traversal and simple implementation examples, mysql examples

Detailed description of MySQL implementation tree traversal and simple implementation examples, mysql examples

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. You can use connect by in Oracle to solve the problem, but it is not supported in MySQL 5.1 (it is said that it has been included in to do). You need to write the process or function to implement it yourself.

1. Create a test table and data:

Drop table if exists 'channel'; create table 'channel' ('id' int (11) not null AUTO_INCREMENT, 'cname' varchar (200) default null, 'parent _ id' int (11) default null, primary key ('id') ENGINE = MyISAM AUTO_INCREMENT = 19 default charset = utf8; /* Data for the table 'channel' */insert into 'channel' ('id', 'cname', 'parent _ id') values (13, 'homepage ', -1), (14, 'tv580',-1), (15, 'life 100',-1), (16, 'slide on the Left ', 13 ), (17, 'help', 14), (18, 'topic introduction', 17 );

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

DELIMITER $ USE 'db1' $ -- traverse sub-nodes from a node down -- recursively generate temporary table data drop procedure if exists 'createchildlst' $ create procedure 'createchildlst' (IN rootId INT, IN nDepth INT) begin declare done int default 0; DECLARE B INT; DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id = rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth = 12; insert into tmpLst VALUES (NULL, rootId, nDepth); OPEN cur1; FETCH cur1 INTO B; WHILE done = 0 do call createChildLst (B, nDepth + 1 ); FETCH cur1 INTO B; END WHILE; CLOSE cur1; END $ -- trace the root node from a node up -- recursively generate temporary table data drop procedure if exists 'createparentlst' $ create procedure 'createparentlst' (IN rootId INT, IN nDepth INT) begin declare done int default 0; DECLARE B INT; DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id = rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth = 12; insert into tmpLst VALUES (NULL, rootId, nDepth); OPEN cur1; FETCH cur1 INTO B; WHILE done = 0 do call createParentLst (B, nDepth + 1); FETCH cur1 INTO B; end while; CLOSE cur1; END $ -- Implement functions similar to Oracle SYS_CONNECT_BY_PATH -- output the id path of a node IN the recursive process drop procedure if exists 'createpathlst' $ create procedure 'createpathlst' (IN nid INT, IN delimit VARCHAR (10), INOUT pathstr VARCHAR (1000) begin declare done int default 0; DECLARE parentid int default 0; DECLARE cur1 cursor for select t. parent_id, CONCAT (CAST (t. parent_id as char), delimit, pathstr) FROM channel AS t WHERE t. id = nid; declare continue handler for not found set done = 1; SET max_sp_recursion_depth = 12; OPEN cur1; FETCH cur1 INTO parentid, pathstr; WHILE done = 0 do call createPathLst (parentid, delimit, pathstr); FETCH cur1 INTO parentid, pathstr; end while; CLOSE cur1; END $ -- recursive process outputs a node name path drop procedure if exists 'createpathnamelst' $ create procedure 'createpathnamelst' (IN nid INT, IN delimit VARCHAR (10 ), INOUT pathstr VARCHAR (1000) begin declare done int default 0; DECLARE parentid int default 0; DECLARE cur1 cursor for select t. parent_id, CONCAT (t. cname, delimit, pathstr) FROM channel AS t WHERE t. id = nid; declare continue handler for not found set done = 1; SET priority = 12; OPEN cur1; FETCH cur1 INTO parentid, pathstr; WHILE done = 0 do call createPathnameLst (parentid, delimit, pathstr); FETCH cur1 INTO parentid, pathstr; end while; CLOSE cur1; END $ -- call the FUNCTION output id path drop function if exists 'fn _ tree_path '$ create function 'fn _ tree_path' (nid INT, delimit VARCHAR (10 )) returns varchar (2000) CHARSET utf8 begin declare pathid VARCHAR (1000); SET @ pathid = CAST (nid as char); CALL createPathLst (nid, delimit, @ pathid ); RETURN @ pathid; END $ -- call the FUNCTION output name path drop function if exists 'fn _ tree_pathname' $ create function 'fn _ tree_pathname' (nid INT, delimit VARCHAR (10) returns varchar (2000) CHARSET utf8 begin declare pathid VARCHAR (1000); SET @ pathid = ''; CALL createPathnameLst (nid, delimit, @ pathid ); RETURN @ pathid; END $ -- call process output subnode drop procedure if exists 'showchildlst' $ create procedure 'showchildlst' (IN rootId INT) begin drop temporary table if exists tmpLst; create temporary table if not exists tmpLst (sno int primary key AUTO_INCREMENT, id INT, depth INT); CALL createChildLst (rootId, 0); 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 FROM tmpLst, channel WHERE tmpLst. id = channel. id order by tmpLst. sno; END $ -- call process output parent node drop procedure if exists 'showparentlst' $ create procedure 'showparentlst' (IN rootId INT) begin drop temporary table if exists tmpLst; create temporary table if not exists tmpLst (sno int primary key AUTO_INCREMENT, id INT, depth INT); CALL createParentLst (rootId, 0); 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 FROM tmpLst, channel WHERE tmpLst. id = channel. id order by tmpLst. sno; END $ DELIMITER;

Iii. Test

CALL showChildLst(-1); CALL showChildLst(13); CALL showChildLst(14); CALL showChildLst(17); CALL showChildLst(18);  CALL showParentLst(-1); CALL showParentLst(13); CALL showParentLst(14); CALL showParentLst(17); CALL showParentLst(18); 

Iv. Legacy issues

1. because mysql does not support dynamic cursors, it is difficult to create a common process or function. You can use two temporary tables for conversion (with recursive calls removed at the same time) is a relatively common implementation.

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.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!