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

快来打我* 2022-05-30 02:57 3025阅读 0赞

最近在项目中遇到了根据父级部门的部门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、测试

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

![这里写图片描述][70]

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

Data too long for column 'oTemp' at row 1

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

[70]: /images/20220530/241b99d103474c83ad50597fc906fbdb.png
[mysql]: http://blog.csdn.net/moyanxuan_1993_2_24/article/details/52790314