【知识记录】mysql树状结构表的递归查询

在Oracle中可以通过connect by prior来进行节点及其子节点的查询，那么在Mysql中应该用什么方法来进行这样的查询呢。

经过查找资料发现主要有两种方法：

1.函数实现

2.存储过程加临时表实现

在此记录和分享一下这两种方法的具体实现。

建表语句：

机构表

DROP TABLE IF EXISTS `a_sys_org`; CREATE TABLE `a_sys_org` (   `org_id` varchar(32) NOT NULL COMMENT '机构号',   `org_name` varchar(256) NOT NULL COMMENT '机构名称',   `parent_org_id` varchar(32) NOT NULL COMMENT '父机构号',   PRIMARY KEY (`org_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统机构表';

人员表

DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` (   `emp_id` varchar(32) NOT NULL COMMENT '人员编号',   `emp_name` varchar(256) NOT NULL COMMENT '姓名',   `emp_org_id` varchar(32) NOT NULL COMMENT '机构号',   PRIMARY KEY (`emp_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人员表';

/*方法一：函数实现

*最大支持字符串连接102400

*查询：SELECT getChildidList(org_id);

*/

DROP FUNCTION IF EXISTS getChildidList; CREATE FUNCTION getChildidList(rootId VARCHAR(32)) RETURNS text BEGIN DECLARE sTemp text; DECLARE sTempChd text; SET@@group_concat_max_len = 102400; SET sTemp = '$'; SET sTempChd = rootId;  WHILE sTempChd IS NOT NULL DO SET sTemp = concat(sTemp, ',', sTempChd); SELECT group_concat(org_id) INTO sTempChd FROM a_sys_org WHERE FIND_IN_SET(parent_org_id, sTempChd) > 0; END WHILE; SET@@group_concat_max_len = 1024; RETURN SUBSTRING(sTemp,3); END

测试语句:

SELECT emp_org_id, emp_name FROM employee WHERE FIND_IN_SET(emp_org_id,getChildidList('102')) ORDER BY emp_org_id;

查询结果：

/*方法二：存储过程加临时表实现

*递归深度限制99层

*查询：CALL showChildList(org_id);

*/

DROP PROCEDURE IF EXISTS showChildList; CREATE PROCEDURE showChildList (IN rootId VARCHAR(32)) BEGIN   SET@@max_sp_recursion_depth=99; DROP TEMPORARY TABLE IF EXISTS temp_child_list; CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT ); -- 插入当前节点 INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1); -- 插入子节点 CALL createChildList (rootId, 0); SELECT o.org_id, e.emp_name, o.org_name FROM temp_child_list t, a_sys_org o, employee e WHERE t._id = o.org_id AND e.emp_org_id = o.org_id ORDER BY t.sno; END; DROP PROCEDURE IF EXISTS createChildList; CREATE PROCEDURE createChildList ( IN rootId VARCHAR (32), IN depth_var INT ) BEGIN DECLARE done INT DEFAULT 0; DECLARE b VARCHAR (32); DECLARE cur1 CURSOR FOR SELECT org_id FROM a_sys_org WHERE parent_org_id = rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; INSERT INTO temp_child_list (_id, _depth) SELECT org_id, depth_var FROM a_sys_org WHERE parent_org_id = rootId; OPEN cur1; FETCH cur1 INTO b; WHILE done = 0 DO CALL createChildList (b, depth_var + 1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; END;

测试语句:

CALL showChildList('104');

查询结果：