【知识记录】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');

查询结果：