[MySQL] tree traversal (query of multi-level menu bar and multi-level upper and lower departments) _ MySQL
Source: Internet
Author: User
Preface:
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_childlist
DELIMITER $
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_parentlist
DELIMITER $
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_pathlist
DELIMITER $
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
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_pnlist
DELIMITER $
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
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_path
DELIMITER $
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 pro_cre_pnlist (nid, delimit, @ pathid );
RETURN @ pathid;
END $
DELIMITER;
2.7. subnode output during the call process -- Pro_show_childLst
DELIMITER $
-- Subnode output during the call process
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_parentLst
DELIMITER $
-- Output parent node during the call process
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 $
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
