mysql树形遍历_MySQL实现树形的遍历

2.6，调用函数输出name路径

-- fn_tree_pathname

-- 调用函数输出name路径

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，调用过程输出子节点

-- pro_show_childLst

DELIMITER

-- 调用过程输出子节点

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，调用过程输出父节点

-- pro_show_parentLst

DELIMITER

-- 调用过程输出父节点

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，开始测试：

3.1，从根节点开始显示，显示子节点集合：

mysql> CALL pro_show_childLst(-1);

+----+-----------------------+-----------+-------+-------------+----------------------------+

| id | NAME                  | parent_id | depth | path        | pathname                   |

+----+-----------------------+-----------+-------+-------------+----------------------------+

| 13 |   --首页              |        -1 |     1 | -1/13       | 首页/                      |

| 16 |     --左上幻灯片      |        13 |     2 | -1/13/16    | 首页/左上幻灯片/           |

| 14 |   --TV580             |        -1 |     1 | -1/14       | TV580/                     |

| 17 |     --帮忙            |        14 |     2 | -1/14/17    | TV580/帮忙/                |

| 18 |       --栏目简介      |        17 |     3 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

| 15 |   --生活580           |        -1 |     1 | -1/15       | 生活580/                   |

+----+-----------------------+-----------+-------+-------------+----------------------------+

6 rows in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

3.2，显示首页下面的子节点

CALL pro_show_childLst(13);

mysql> CALL pro_show_childLst(13);

+----+---------------------+-----------+-------+----------+-------------------------+

| id | NAME                | parent_id | depth | path     | pathname                |

+----+---------------------+-----------+-------+----------+-------------------------+

| 13 | --首页              |        -1 |     0 | -1/13    | 首页/                   |

| 16 |   --左上幻灯片      |        13 |     1 | -1/13/16 | 首页/左上幻灯片/        |

+----+---------------------+-----------+-------+----------+-------------------------+

2 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

3.3，显示TV580下面的所有子节点

CALL pro_show_childLst(14);

mysql> CALL pro_show_childLst(14);

+----+--------------------+-----------+-------+-------------+----------------------------+

| id | NAME               | parent_id | depth | path        | pathname                   |

+----+--------------------+-----------+-------+-------------+----------------------------+

| 14 | --TV580            |        -1 |     0 | -1/14       | TV580/                     |

| 17 |   --帮忙           |        14 |     1 | -1/14/17    | TV580/帮忙/                |

| 18 |     --栏目简介     |        17 |     2 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+--------------------+-----------+-------+-------------+----------------------------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

3.4，“帮忙”节点有一个子节点，显示出来：

CALL pro_show_childLst(17);

mysql> CALL pro_show_childLst(17);

+----+------------------+-----------+-------+-------------+----------------------------+

| id | NAME             | parent_id | depth | path        | pathname                   |

+----+------------------+-----------+-------+-------------+----------------------------+

| 17 | --帮忙           |        14 |     0 | -1/14/17    | TV580/帮忙/                |

| 18 |   --栏目简介     |        17 |     1 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+------------------+-----------+-------+-------------+----------------------------+

2 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>

3.5，“栏目简介”没有子节点，所以只显示最终节点：

mysql> CALL pro_show_childLst(18);

+----+----------------+-----------+-------+-------------+----------------------------+

| id | NAME           | parent_id | depth | path        | pathname                   |

+----+----------------+-----------+-------+-------------+----------------------------+

| 18 | --栏目简介     |        17 |     0 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+----------------+-----------+-------+-------------+----------------------------+

1 row in set (0.36 sec)

Query OK, 0 rows affected (0.36 sec)

mysql>

3.6，显示根节点的父节点

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，显示“首页”的父节点

CALL pro_show_parentLst(13);

mysql> CALL pro_show_parentLst(13);

+----+----------+-----------+-------+-------+----------+

| id | NAME     | parent_id | depth | path  | pathname |

+----+----------+-----------+-------+-------+----------+

| 13 | --首页   |        -1 |     0 | -1/13 | 首页/    |

+----+----------+-----------+-------+-------+----------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

3.8，显示“TV580”的父节点，parent_id为-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，显示“帮忙”节点的父节点

mysql>

CALL pro_show_parentLst(17);

mysql> CALL pro_show_parentLst(17);

+----+-----------+-----------+-------+----------+---------------+

| id | NAME      | parent_id | depth | path     | pathname      |

+----+-----------+-----------+-------+----------+---------------+

| 17 | --帮忙    |        14 |     0 | -1/14/17 | TV580/帮忙/   |

| 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，显示最低层节点“栏目简介”的父节点

CALL pro_show_parentLst(18);

mysql> CALL pro_show_parentLst(18);

+----+----------------+-----------+-------+-------------+----------------------------+

| id | NAME           | parent_id | depth | path        | pathname                   |

+----+----------------+-----------+-------+-------------+----------------------------+

| 18 | --栏目简介     |        17 |     0 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

| 17 |   --帮忙       |        14 |     1 | -1/14/17    | TV580/帮忙/                |

| 14 |     --TV580    |        -1 |     2 | -1/14       | TV580/                     |

+----+----------------+-----------+-------+-------------+----------------------------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

22/2<12