当前位置：首页 > news >正文

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

news 2025/6/20 10:29:49

本次需要击毙的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
BEGINDECLARE _name TEXT;DECLARE _parentCode VARCHAR(255);DECLARE _tempName TEXT;DECLARE _tempCode VARCHAR(255);-- 根据传入的部门code和租户id，查询该部门的code、name和父级pidSELECT 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' DOSELECT 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_pathFROM table1WHERE code = #{department_code} AND tenant_id = #{tenant_id}UNION ALLSELECT d.code, d.name, d.pid, CONCAT(CONCAT(d.code, '#', d.name), ',', dp.full_path)FROM table1 dJOIN dept_path dp ON dp.pid = d.codeWHERE 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 departmentNoFROM table1 duWHERE 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, pidFROM table1WHERE 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));}