Implement tree traversal (query of multi-level menu bar and multi-level upper and lower departments) _ MySQL

For tree traversal of multiple levels of menu bar or lower-level departments in the permission system, connectby is used in oracle, and mysql does not have such a convenient way, therefore, it is a common headache for us to traverse data tables in MySQL. we will use the stored procedure below. 1. preface:

For tree traversal of multiple levels of menu bar or lower-level departments in the permission system, connect by is available in oracle, and mysql does not have such a convenient way, therefore, it is a common headache for us to traverse data tables in MySQL. we will use the stored procedure below.

1. create a test table and data:

Drop table if exists csdn. channel; create table csdn. channel (id INT (11) not null AUTO_INCREMENT, cname VARCHAR (200) default null, parent_id INT (11) default null, primary key (id )) ENGINE = innodb default charset = utf8; 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); drop table if exists channel;

2. implement tree traversal using temporary tables and recursive procedures (mysql UDF cannot be called recursively ):

2.1. Traverse sub-nodes from a node and Recursively generate temporary table data

-- pro_cre_childlistDELIMITER $$     DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$   CREATE PROCEDURE csdn.pro_cre_childlist(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 pro_cre_childlist(b,nDepth+1);                 FETCH cur1 INTO b;         END WHILE;                CLOSE cur1;   END$$   

2.2 trace the root node from a node and Recursively generate temporary table data

-- pro_cre_parentlistDELIMITER $$DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$   CREATE PROCEDURE csdn.pro_cre_parentlist(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 pro_cre_parentlist(b,nDepth+1);                 FETCH cur1 INTO b;         END WHILE;                CLOSE cur1;        END$$   

2.3. implement a function similar to Oracle SYS_CONNECT_BY_PATH, and output the id path of a node in the recursive process.

-- pro_cre_pathlistDELIMITER $$USE csdn$$DROP PROCEDURE IF EXISTS pro_cre_pathlist$$CREATE PROCEDURE pro_cre_pathlist(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 pro_cre_pathlist(parentid,delimit,pathstr);                 FETCH cur1 INTO parentid,pathstr;         END WHILE;                     CLOSE cur1;    END$$DELIMITER ;

2.4. a node name path is output in the recursive process.

-- pro_cre_pnlistDELIMITER $$USE csdn$$DROP PROCEDURE IF EXISTS pro_cre_pnlist$$CREATE PROCEDURE pro_cre_pnlist(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 max_sp_recursion_depth=12;                                  OPEN cur1;                FETCH cur1 INTO parentid,pathstr;         WHILE done=0 DO                            CALL pro_cre_pnlist(parentid,delimit,pathstr);                 FETCH cur1 INTO parentid,pathstr;         END WHILE;                     CLOSE cur1;         END$$DELIMITER ;

2.5. call the function output id path

-- fn_tree_pathDELIMITER $$ DROP FUNCTION IF EXISTS csdn.fn_tree_path$$   CREATE FUNCTION csdn.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 pro_cre_pathlist(nid,delimit,@pathid);          RETURN @pathid;   END$$       

2.6. call the function output name path

-- Fn_tree_pathname -- call the FUNCTION output name path DELIMITER $ drop function if exists csdn. fn_tree_pathname $ create function csdn. fn_tree_pathname (nid INT, delimit VARCHAR (10) returns varchar (2000) CHARSET utf8 begin declare pathid VARCHAR (1000); SET @ pathid = ''; CALL limit (nid, delimit, @ pathid); RETURN @ pathid; END $ DELIMITER;

2.7. subnode output during the call process

-- Pro_show_childLst DELIMITER $ -- call process output subnode drop procedure if exists pro_show_childLst $ create procedure pro_show_childLst (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 pro_cre_childlist (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 $

2.8. parent node output during the call process

-- Pro_show_parentLstDELIMITER $ -- call process output parent node drop procedure if exists 'Pro _ show_parentLst '$ create procedure 'Pro _ show_parentLst' (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 pro_cre_parentlist (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 $

3. start the test:

3.1: displays the child node set from the root node:

Mysql> CALL pro_show_childLst (-1 ); + ---- + certificate + ----------- + ------- + ----------- + certificate + | id | NAME | parent_id | depth | path | pathname | + ---- + --------------------- + ----------- + --------- + certificate + | 13 | -- homepage |-1 | 1 |-1/13 | homepage/| 16 | -- upper left slide | 13 | 2 |-1/13/16 | homepage/upper left slide/| 14 | -- TV580 |-1 | 1 |-1/14 | TV580/| 17 | -- help | 14 | 2 |-1/14/17 | TV580/help/| 18 | -- Column introduction | 17 | 3 |-1/14/17/18 | TV580//| 15 | -- life 580 |-1 | 1 |-1/15 | life 580/| + ---- + ----------------------- + ----------- + ------- + ------------- + ---------------------------- + 6 rows in set (0.05 sec) query OK, 0 rows affected (0.05 sec)

3.2. subnodes under the home page are displayed.

CALL pro_show_childLst (13); mysql> CALL pro_show_childLst (13 ); + ---- + signature + ----------- + ------- + ---------- + signature + | id | NAME | parent_id | depth | path | pathname | + ---- + --------------------- + --------- + ------- + ------------ + signature + | 13 | -- homepage |-1 | 0 |-1/13 | homepage/| 16 | -- upper left slide | 13 | 1 |-1/13/16 | homepage/upper left slide/| + ---- + --------------------- + ----------- + ------- + ---------- + ------------------------- + 2 rows in set (0.02 sec) query OK, 0 rows affected (0.02 sec) mysql>

3.3. all subnodes under TV580 are displayed.

CALL pro_show_childLst (14); mysql> CALL pro_show_childLst (14 ); + ---- + certificate + ----------- + ------- + ----------- + certificate + | id | NAME | parent_id | depth | path | pathname | + ---- + -------------------- + ----------- + ------- + ------------- + certificate + | 14 | -- TV580 |-1 | 0 |-1/14 | TV580/| 17 | -- help | 14 | 1 |-1/14/17 | TV580/help/| 18 | -- topic Overview | 17 | 2 |-1/14/17/18 | TV580/help/topic overview/| + ---- + -------------------- + ----------- + --------------- + ---------------------------- + 3 rows in set (0.02 sec) query OK, 0 rows affected (0.02 sec) mysql>

3.4. the "help" node has a subnode, which is displayed as follows:

CALL pro_show_childLst (17); mysql> CALL pro_show_childLst (17 ); + ---- + certificate + ----------- + ------- + ----------- + certificate + | id | NAME | parent_id | depth | path | pathname | + ---- + ------------------ + --------- + ------- + --------------- + certificate + | 17 | -- help | 14 | 0 |-1/14/17 | TV580/help/| 18 | -- Topic overview | 17 | 1 |-1/14/17/18 | TV580/help/topic introduction/| + ---- + ---------------- + ----------- + ------- + ------------- + ---------------------------- + 2 rows in set (0.03 sec) query OK, 0 rows affected (0.03 sec) mysql>

3.5. "topic introduction" does not have subnodes, so only the final nodes are displayed:

Mysql> CALL pro_show_childLst (18 ); + ---- + ---------------- + ----------- + ------- + ------------- + signature + | id | NAME | parent_id | depth | path | pathname | + ---- + ---------------- + ----------- + ------- + signature + | 18 | -- Topic overview | 17 | 0 |-1/14/17/18 | TV580//topic overview/| + ---- + ---------------- + ----------- + --------------- + ------------------------ + 1 row in set (0.36 sec) query OK, 0 rows affected (0.36 sec) mysql>

3.6, displays the root node's parent node

CALL pro_show_parentLst(-1);   mysql> CALL pro_show_parentLst(-1);Empty set (0.01 sec)Query OK, 0 rows affected (0.01 sec)mysql>

3.7. the parent node of the "homepage" is displayed.

CALL pro_show_parentLst (13); mysql> CALL pro_show_parentLst (13 ); + ---- + ---------- + ----------- + ------- + ---------- + | id | NAME | parent_id | depth | path | pathname | + ---- + ---------- + ----------- + ------- + ------------ + | 13 | -- homepage |-1 | 0 |-1/13 | homepage/| + ---- + ---------- + ----------- + ------- + ---------- + 1 row in set (0.02 sec) query OK, 0 rows affected (0.02 sec) mysql>

3.8. The "TV580" parent node is displayed. parent_id is-1.

CALL pro_show_parentLst(14);   mysql> CALL pro_show_parentLst(14);   +----+---------+-----------+-------+-------+----------+| id | NAME    | parent_id | depth | path  | pathname |+----+---------+-----------+-------+-------+----------+| 14 | --TV580 |        -1 |     0 | -1/14 | TV580/   |+----+---------+-----------+-------+-------+----------+1 row in set (0.02 sec)Query OK, 0 rows affected (0.02 sec)

3.9. the parent node of the "help" node is displayed.

Mysql> CALL pro_show_parentLst (17); mysql> CALL pro_show_parentLst (17 ); + ---- + ----------- + ------- + ---------- + --------------- + | id | NAME | parent_id | depth | path | pathname | + ---- + ----------- + ------- + ---------- + certificate + | 17 | -- help | 14 | 0 |-1/14/17 | TV580/help/| 14 | -- TV580 |-1 | 1 |-1/14 | TV580/| + ---- + ----------- + ----------- + ------- + ---------- + --------------- + 2 rows in set (0.02 sec) query OK, 0 rows affected (0.02 sec) mysql>

3.10. displays the parent node of the lowest-layer node "topic introduction ".

CALL pro_show_parentLst (18); mysql> CALL pro_show_parentLst (18 ); + ---- + ---------------- + ----------- + ------- + ------------- + signature + | id | NAME | parent_id | depth | path | pathname | + ---- + ---------------- + ----------- + ------- + signature + | 18 | -- Topic overview | 17 | 0 |-1/14/17/18 | TV580/help/topic overview/| 17 | -- help | 14 | 1 |-1/14/17 | TV580/ help/| 14 | -- TV580 |-1 | 2 |-1/14 | TV580/| + ---- + ---------------- + ----------- + ------- + ------------- + ---------------------------- + 3 rows in set (0.02 sec) query OK, 0 rows affected (0.02 sec) mysql>