MySQL层级查询实战：无函数实现部门父路径

本次需要击毙的MySQL函数

函数主要用于获取部门的完整层级路径，方便在应用程序或SQL查询中直接调用，快速获得部门的上下级关系信息。执行该函数之后简单使用SQL可以实现数据库中部门名称查询。例如下面sql

select name,GetDepartmentParentNames(du.department_code, du.tenant_id) as department_full_name xxx   from  tableName

CREATE
DEFINER = xxx@`%`
FUNCTION GetDepartmentParentNames(_code VARCHAR(255), _tenant_id VARCHAR(255)) RETURNS TEXT
BEGIN
    DECLARE _name TEXT;
    DECLARE _parentCode VARCHAR(255);
    DECLARE _tempName TEXT;
    DECLARE _tempCode VARCHAR(255);

    -- 根据传入的部门code和租户id，查询该部门的code、name和父级pid
    SELECT code, name, pid INTO _tempCode, _tempName, _parentCode 
    FROM table1 
    WHERE code = _code AND tenant_id = _tenant_id;

    -- 初始化_name变量，格式为 "code#name"
    SET _name = CONCAT(_tempCode, '#', _tempName);

    -- 通过循环，逐级查找父部门，直到父部门pid为'0'（表示无父部门）
    WHILE _parentCode <> '0' DO
        SELECT code, name, pid INTO _tempCode, _tempName, _parentCode 
        FROM table1 
        WHERE code = _parentCode AND tenant_id = _tenant_id;

        -- 将当前父部门信息拼接到_name前面，格式依然是 "code#name"，用逗号分隔
        SET _name = CONCAT(CONCAT(_tempCode, '#', _tempName), ',', _name);
    END WHILE;

    -- 返回拼接好的字符串，包含从顶级父部门到当前部门的所有层级信息
    RETURN _name;
END;

如何进行重构解决

分析函数的作用是通过递归的方式，基于部门code和tenant_id，逐级向上查找父部门，拼接出完整的部门层级名称字符串。

方案一采用MySQL8+的CTE实现

WITH RECURSIVE dept_path AS (
    SELECT code, name, pid, CAST(CONCAT(code, '#', name) AS CHAR(1000)) AS full_path
    FROM table1
    WHERE code = #{department_code} AND tenant_id = #{tenant_id}
    
    UNION ALL
    
    SELECT d.code, d.name, d.pid, CONCAT(CONCAT(d.code, '#', d.name), ',', dp.full_path)
    FROM table1 d
    JOIN dept_path dp ON dp.pid = d.code
    WHERE d.tenant_id = #{tenant_id} AND d.pid <> '0'
)
SELECT full_path FROM dept_path WHERE pid = '0' LIMIT 1;

但是我不会这个咋办，那就换一种实现方式~

方案二应用层或存储过程外部实现递归

涉及机密下述代码进行过脱敏处理

1. 修改SQL查询部门部分，直接查询用户对应的部门编码和部门名称，不调用递归函数

  <select id="xxxxx" resultType="xxxxxxx">
    SELECT du.user_code AS code, du.department_code AS departmentNo
    FROM table1 du
    WHERE du.tenant_id = #{tenantId}
    <if test="userCodes != null and userCodes.size() > 0">
      AND du.user_code IN
      <foreach collection="userCodes" item="item" separator="," open="(" close=")">
        #{item}
      </foreach>
    </if>
  </select>

调用上述方法获取到集合之后需要利用Set集合进行去重

     // 提取所有部门code（包括父部门）用于查询部门信息
        Set<String> allDeptCodes = new HashSet<>();
        for (KbUserRoleInfo ud : userDepartments) {
            allDeptCodes.add(ud.getDepartmentNo());
        }

2.新增查询部门信息相关信息sql

  <select id="listDepartmentsByCodes" resultType="xxxxxx">
    SELECT code, name, pid
    FROM table1
    WHERE tenant_id = #{tenantId}
    AND code IN
    <foreach collection="codes" item="code" separator="," open="(" close=")">
      #{code}
    </foreach>
  </select>

获取到部门相关信息之后，构建map结构，部门编码为key，部门信息为value

        List<KbDepartment> departments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(allDeptCodes));
        Map<String, KbDepartment> departmentMap = departments.stream()
                .collect(Collectors.toMap(KbDepartment::getCode, d -> d));

3. 递归补全所有部门信息编码需要使用编码查询部门信息

    /**
     * 递归添加父部门code
     */
    private void addParentDepartments(String deptCode, Map<String, KbDepartment> departmentMap, Set<String> expandedDeptCodes) {
        KbDepartment dept = departmentMap.get(deptCode);
        if (dept != null && dept.getPid() != null && !"0".equals(dept.getPid()) && !expandedDeptCodes.contains(dept.getPid())) {
            expandedDeptCodes.add(dept.getPid());
            addParentDepartments(dept.getPid(), departmentMap, expandedDeptCodes);
        }
    }

4.根据完整部门编码获取完整部门信息

        // 再次查询所有部门信息（包含父部门）
        List<KbDepartment> allDepartments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(expandedDeptCodes));
        Map<String, KbDepartment> allDepartmentMap = allDepartments.stream()
                .collect(Collectors.toMap(KbDepartment::getCode, d -> d));

5.构建用户部门信息映射

// 由于一个用户会涉及多个组织假如，因此构建Map结构，如果k-v都是简单的String结构，会出现后面组织覆盖前面组织情况
Map<String, List<String>> userDeptFullPathMap = new HashMap<>();
        for (KbUserRoleInfo ud : userDepartments) {
            String fullPath = buildDepartmentFullPath(ud.getDepartmentNo(), allDepartmentMap);
            userDeptFullPathMap.computeIfAbsent(ud.getCode(), k -> new ArrayList<>()).add(fullPath);
        }


    /**
     * 递归构建部门完整路径字符串，格式：code#name,code#name,...
     */
    private String buildDepartmentFullPath(String deptCode, Map<String, KbDepartment> departmentMap) {
        KbDepartment dept = departmentMap.get(deptCode);
        if (dept == null) {
            return "";
        }
        if ("0".equals(dept.getPid())) {
            return dept.getCode() + "#" + dept.getName();
        }
        String parentPath = buildDepartmentFullPath(dept.getPid(), departmentMap);
        return parentPath + "," + dept.getCode() + "#" + dept.getName();
    }

6.部门名称格式化

    /**
     * 重置部门名称
     */
    public void resetDepartmentName() {
        // 处理部门名称
        String departmentName = this.getDepartmentName();
        // 部门名称从SQL数据库查询的规则是，部门编号#部门名称, 例如：0001#技术部,000101#开发一组
        // 这里将部门名称处理成为 技术部 > 开发一组，通过字符>连接起来
        if (StrUtil.isBlank(departmentName)) {
            return;
        }
        // 一个组织人员有多个部门的情况
        List<String> multipartDepartmentNameList = StrUtil.split(departmentName, "/");
        if (CollUtil.isEmpty(multipartDepartmentNameList)) {
            return;
        }
        List<String> departmentNames = new ArrayList<>();
        List<String> departmentShortNames = new ArrayList<>();
        List<String> departmentNos = new ArrayList<>();
        List<String> orgCodes = new ArrayList<>();
        
        for (String oneDepartmentName : multipartDepartmentNameList) {
            List<String> departmentNameList = StrUtil.split(oneDepartmentName, StrUtil.COMMA);
            // 获取一级的组织编号
            orgCodes.add(DepartmentUtils.getOrgCode(departmentNameList.get(0)));
            
            // 设置最后一个部门名称，获取departmentNameList的末尾元素
            String shortName = DepartmentUtils.splitDepartmentName(departmentNameList.get(departmentNameList.size() - 1));
            departmentShortNames.add(shortName);
            String sortNo = DepartmentUtils.splitDepartmentNo(departmentNameList.get(departmentNameList.size() - 1));
            departmentNos.add(sortNo);
            List<String> names = new LinkedList<>();
            for (String name : departmentNameList) {
                names.add(DepartmentUtils.splitDepartmentName(name));
            }
            departmentNames.add(StrUtil.join(" > ", names));
        }
        this.setOrgCode(StrUtil.join(StrUtil.COMMA, orgCodes));
        // 设置部门名称
        this.setDepartmentName(StrUtil.join(StrUtil.COMMA, departmentNames));
        this.setDepartmentNo(StrUtil.join(StrUtil.COMMA, departmentNos));
        this.setDepartmentShortName(StrUtil.join(StrUtil.COMMA, departmentShortNames));
    }