MySQL存储过程实现树形遍历

"这篇文档主要介绍了如何在MySQL中实现树形结构的数据遍历，通过创建存储过程和利用临时表的方法解决MySQL没有内置类似Oracle的`connect by`功能的问题。" 在MySQL中处理具有层级关系的数据时，如多级别的菜单栏或权限系统中的部门结构，我们需要找到一种方式来遍历这些树形数据。由于MySQL不提供像Oracle中的`connect by`那样的便捷语法，我们通常需要借助存储过程来实现树的遍历。 首先，我们创建一个名为`csdn.channel`的测试表，用于存储树形结构的数据。表结构包含`id`作为主键，`cname`为节点名称，以及`parent_id`表示父节点的ID。例如： ```sql 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; ``` 然后，我们插入一些示例数据，模拟一个多级菜单或部门结构： ```sql INSERT INTO channel (id, cname, parent_id) VALUES (13, '首页', -1), (14, 'TV580', -1), (15, '生活580', -1), (16, '左上幻灯片', 13), (17, '帮忙', 14), (18, '栏目简介', 17); ``` 由于MySQL的用户定义函数（UDF）不支持递归调用，我们采用临时表和递归存储过程的方式来遍历树。这里有一个名为`pro_cre_childlist`的存储过程，它接收两个参数：`rootId`表示要遍历的起始节点ID，`nDepth`表示当前深度。这个过程会递归地将所有子节点添加到临时表`tmpLst`中： ```sql 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 // DELIMITER ; ``` 在这个过程中，我们首先声明了一些变量，如`done`用于标记是否结束循环，`b`用于存储当前节点ID，以及游标`cur1`用于遍历子节点。然后，我们向临时表`tmpLst`中插入起始节点，并打开游标进行循环。在循环中，我们递归地调用`pro_cre_childlist`，并将当前节点的深度增加1。如果未找到更多子节点，`NOT FOUND`处理器会设置`done`为1，结束循环。最后，关闭游标完成遍历。 通过这种方法，我们可以遍历并获取MySQL数据库中树形结构数据的所有子节点，即使没有像Oracle那样的内置递归功能。这种方案对于处理层级关系复杂的数据非常有用，特别是在设计多级菜单、权限管理或其他树状组织结构的系统时。