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

[MySQL] tree traversal (query of multi-level menu bar and multi-level upper and lower departments)
Preface:
1. Create a test table and data:
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
2.2 trace the root node from a node and recursively generate temporary table data
-- Pro_cre_parentlist
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
2.4. A node name path is output in the recursive process.
-- Pro_cre_pnlist
2.5. Call the function output id path
-- Fn_tree_path
2.6. Call the function output name path
-- Fn_tree_pathname
2.7. subnode output during the call Process
-- Pro_show_childLst
2.8. parent node output during the call Process
-- Pro_show_parentLst
3. Start the test:
Mysql> CALL pro_show_childLst (-1 );
3.2. subnodes under the Home Page are displayed.
CALL pro_show_childLst (13 );
3.3. All subnodes under TV580 are displayed.
CALL pro_show_childLst (14 );
3.4. The "help" node has a subnode, which is displayed as follows:
CALL pro_show_childLst (17 );
3.5. "topic Introduction" does not have subnodes, so only the final nodes are displayed:
Mysql> CALL pro_show_childLst (18 );
3.6, displays the root node's parent node
CALL pro_show_parentLst (-1 );
3.7. The parent node of the "Homepage" is displayed.
CALL pro_show_parentLst (13 );
3.8. The "TV580" parent node is displayed. parent_id is-1.
CALL pro_show_parentLst (14 );
3.9. The parent node of the "help" node is displayed.
Mysql>
3.10. displays the parent node of the lowest-layer node "topic Introduction ".
CALL pro_show_parentLst (18 );
Reference URL:
Http://jan.kneschke.de/projects/mysql/sp/sp_tree. SQL