In the Mysql database through the stored procedure to implement the tree traversal _mysql

About the multi-level menu bar or the authority of the Department of the hierarchy of the tree traversal, Oracle has connect by to achieve, MySQL does not have such a convenient way, so MySQL traversal data table is we often encounter headaches, the following through the stored procedures to achieve.

1, set up test table and data:

DROP TABLE IF EXISTS csdn.channel; 
CREATE TABLE Csdn.channel ( 
ID INT (one) not NULL auto_increment, 
cname VARCHAR (=) DEFAULT null, 
parent_id in T (one) default NULL, 
PRIMARY KEY (ID) 
) engine=innodb default Charset=utf8; 
INSERT into Channel (id,cname,parent_id) 
VALUES (13, ' home ', -1), 
(' TV580 ', -1), 
(15, ' Life 580 ',-1), 
(16, ' upper left slide ', 
(17, ' Help ', "), 
(18, ' column profile '); 
DROP TABLE IF EXISTS channel;

2, the use of temporary tables and recursive procedures to implement the tree traversal (MySQL UDF can not be recursive calls):

2.1, traversing the child node down from a node, recursively generating temporary table data

--Pro_cre_childlist
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist
CREATE PROCEDURE csdn.pro_cre_childlist ( In Rootid int,in ndepth int)
DECLARE done int DEFAULT 0;
DECLARE b INT;
DECLARE Cur1 CURSOR for SELECT ID from channel WHERE parent_id=rootid;
DECLARE CONTINUE HANDLER for does 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 does call 
pro_cre_childlist (b,ndepth+1); 
FETCH cur1 into B; 
End while; 

2.2, from a node to trace up the root node, recursive generation of temporary table data

--Pro_cre_parentlist
DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist
CREATE PROCEDURE csdn.pro_cre_ Parentlist (in Rootid int,in ndepth int) 
BEGIN 
DECLARE do int DEFAULT 0; 
DECLARE b INT; 
DECLARE cur1 CURSOR for SELECT parent_id from channel WHERE Id=rootid; 
DECLARE CONTINUE HANDLER for does 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 does call 
pro_cre_parentlist (b,ndepth+1); 
FETCH cur1 into B; 
End while; 

2.3, to achieve similar functions of Oracle Sys_connect_by_path, recursive process output a node ID path

--Pro_cre_pathlist use
csdn
DROP PROCEDURE IF EXISTS pro_cre_pathlist
CREATE PROCEDURE pro_cre_pathlist ( In Nid int,in Delimit VARCHAR (Ten), 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 does FOUND SET done = 1; 
SET max_sp_recursion_depth=12; 
OPEN Cur1; 
FETCH cur1 into Parentid,pathstr; 
While Done=0 does call 
pro_cre_pathlist (PARENTID,DELIMIT,PATHSTR); 
FETCH cur1 into Parentid,pathstr; 
End While; 
Close Cur1; 
DELIMITER;

2.4, recursive process output a node name path

--Pro_cre_pnlist use
csdn
DROP PROCEDURE IF EXISTS pro_cre_pnlist
CREATE PROCEDURE pro_cre_pnlist (in Nid Int,in Delimit VARCHAR (a), 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 does FOUND SET done = 1; 
SET max_sp_recursion_depth=12; 
OPEN Cur1; 
FETCH cur1 into Parentid,pathstr; 
While Done=0 does call 
pro_cre_pnlist (PARENTID,DELIMIT,PATHSTR); 
FETCH cur1 into Parentid,pathstr; 
End While; 
Close Cur1; 
DELIMITER;

2.5, call 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 (a)) RETURNS VARCHAR (Watts) CHARSET UTF8 
BEGIN 
DECLARE pathid (VARCHAR); 
SET Pathid=cast (nid as CHAR); 
Call Pro_cre_pathlist (Nid,delimit,pathid); 
return pathid; 
End

2.6, call function output name path

--Fn_tree_pathname
--invokes function output name path 
DELIMITER
DROP function IF EXISTS csdn.fn_tree_pathname
CREATE FUNCTION csdn.fn_tree_pathname (Nid int,delimit VARCHAR ()) RETURNS VARCHAR (Watts) CHARSET UTF8 
BEGIN 
DECLARE Pathid VARCHAR (1000); 
SET pathid= '; 
Call Pro_cre_pnlist (Nid,delimit,pathid); 
return pathid; 
End
DELIMITER;

2.7, call the procedure output child node

--Pro_show_childlst 
DELIMITER
--Call process output child node 
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); 
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, the calling procedure outputs the parent node

--Pro_show_parentlst
DELIMITER
--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); 
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, starting from the root node, displaying the child node collection:

Mysql> call Pro_show_childlst ( -1);
+----+-----------------------+-----------+-------+-------------+----------------------------+
| id | NAME | parent_id | Depth | Path | pathname |
+----+-----------------------+-----------+-------+-------------+----------------------------+
| 13 |--Home |-1 | 1 | -1/13 | Home/|
| 16 | --Top Left Slide | 13 | 2 | -1/13/16 | Home/Top 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/Help/Column profile/|
| 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, show the child node below the first page

Call Pro_show_childlst (a);
Mysql> call Pro_show_childlst (a);
+----+---------------------+-----------+-------+----------+-------------------------+
| id | NAME | parent_id | Depth | Path | pathname |
+----+---------------------+-----------+-------+----------+-------------------------+
| 13 |--Home |-1 | 0 | -1/13 | Home/|
| 16 | --Top Left Slide | 13 | 1 | -1/13/16 | Home/Top Left slide/|
+----+---------------------+-----------+-------+----------+-------------------------+
2 rows in Set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>

3.3, show all child nodes below TV580

Call Pro_show_childlst ();
Mysql> call Pro_show_childlst;
| ID | NAME | parent_id | Depth | Path | pathname |
| 14 | --tv580 | -1 | 0 | -1/14 | tv580/|
| 17 | --Help | 14 | 1 | -1/14/17 | tv580/Help/|
| 18 | --Column Introduction | 17 | 2 | -1/14/17/18 | tv580/Help/Column profile/|
3 Rows in Set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>

3.4, "help" node has a child node, displayed:

Call Pro_show_childlst ();
Mysql> call Pro_show_childlst;
| ID | NAME | parent_id | Depth | Path | pathname |
| 17 | --Help | 14 | 0 | -1/14/17 | tv580/Help/|
| 18 | --Column Introduction | 17 | 1 | -1/14/17/18 | tv580/Help/Column profile/|
2 rows in Set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql>

3.5, "column profile" does not have child nodes, so only the final node is displayed:

Mysql> call Pro_show_childlst (a);
+--| ID | NAME | parent_id | Depth | Path | pathname |
| 18 | --Column Introduction | 17 | 0 | -1/14/17/18 | tv580/Help/Column profile/|
1 row in Set (0.36 sec)
Query OK, 0 rows affected (0.36 sec)
mysql>

3.6, displaying the parent node of the root 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, show the parent node of "first page"

Call Pro_show_parentlst (a);
Mysql> call Pro_show_parentlst;
| ID | NAME | parent_id | Depth | Path | pathname |
| 13 | --Home | -1 | 0 | -1/13 | Home/|
1 row in Set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>

3.8, displays the "TV580" parent node, parent_id is-1

Call Pro_show_parentlst ();
Mysql> call Pro_show_parentlst;
| 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, displaying the parent node of the Help node

Call Pro_show_parentlst ();
Mysql> call Pro_show_parentlst;
| ID | NAME | parent_id | Depth | Path | pathname |
| 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, show the lowest level node "section Introduction" of the parent node

Call Pro_show_parentlst (a);
Mysql> call Pro_show_parentlst (a);
| ID | NAME | parent_id | Depth | Path | pathname |
| 18 | --Column Introduction | 17 | 0 | -1/14/17/18 | tv580/Help/Column profile/|
| 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>

The above is a small set for you to introduce in the MySQL database through the stored procedures to achieve the tree-like traversal, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!