mysql中使用递归实现父级部门所有子级信息的查询

2018-03-02

最近在项目中遇到了根据父级部门的部门id查询其所有子级部门的需求，下面介绍其详细过程和原理。
1、创建部门表。

/*
Navicat MySQL Data Transfer
Source Server         : LongZheng
Source Server Version : 50718
Source Host           : localhost:3306
Source Database       : ljy
Target Server Type    : MYSQL
Target Server Version : 50718
File Encoding         : 65001
Date: 2018-03-02 21:39:04
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for hr_dept
-- ----------------------------
DROP TABLE IF EXISTS `hr_dept`;
CREATE TABLE `hr_dept` (
  `deptId` bigint(200) NOT NULL,
  `parent_id` bigint(200) DEFAULT NULL,
  `dept_name` varchar(128) DEFAULT NULL,
  `dept_code` varchar(24) DEFAULT NULL,
  `dept_desc` varchar(512) DEFAULT NULL,
  `dept_office_address` varchar(128) DEFAULT NULL,
  `is_delete` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hr_dept
-- ----------------------------
INSERT INTO `hr_dept` VALUES ('1', '3', '行政部门', '100004', '管理公司事物', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('2', '3', '人事部门', '100003', '管理公司员工', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('3', '1', '测试部门', '100005', '前后台测试', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('4', '1', '移动应用部', '100006', '手机端开发', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('5', '1', '金融业务部门', '100007', '银行业务', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('6', '1', '战略规划部', '100001', '研发软件架构', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('7', '2', '应用开发部', '100002', 'PC端后台开发', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('8', '2', '咨询部', '100008', '接洽外部项目', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('9', '2', '华北交付中心', '100009', '调试软件项目', '江苏无锡菱湖大道', '1');

2、创建存储过程.
执行下面的语句就可以创建function了，也就是执行语句创建存储过程。

create function getChildrenOrgOfHr(orgid INT)
returns varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(orgid AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(deptId) INTO oTempChild FROM hr_dept WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END

3、测试

1	select * from hr_dept where FIND_IN_SET(deptId,getChildrenOrgOfHr(2));

这里写图片描述

4、原理
其实就是一个递归的过程，在存储过程中最终返回的是一个字符串数组，这个字符串数组中包含当前父级节点和所有子级节点的id,而这个字符串数组是通过递归进行拼接逐次增加的哦。
5、可能会出现的问题：

1	Data too long for column 'oTemp' at row 1

6、参考文章
mysql递归查询组织机构