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

## 本次需要击毙的MySQL函数

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

```java

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

```


```java

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实现

```java

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查询部门部分，直接查询用户对应的部门编码和部门名称，不调用递归函数

```java

<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集合进行去重

```java

// 提取所有部门code（包括父部门）用于查询部门信息

Set<String> allDeptCodes = new HashSet<>();

for (KbUserRoleInfo ud : userDepartments) {

allDeptCodes.add(ud.getDepartmentNo());

}

```

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

```java

<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

```java

List<KbDepartment> departments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(allDeptCodes));

Map<String, KbDepartment> departmentMap = departments.stream()

.collect(Collectors.toMap(KbDepartment::getCode, d -> d));

```

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

```java

/**

* 递归添加父部门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.根据完整部门编码获取完整部门信息

```java

// 再次查询所有部门信息（包含父部门）

List<KbDepartment> allDepartments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(expandedDeptCodes));

Map<String, KbDepartment> allDepartmentMap = allDepartments.stream()

.collect(Collectors.toMap(KbDepartment::getCode, d -> d));

```

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

```java

// 由于一个用户会涉及多个组织假如，因此构建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.部门名称格式化

```java

/**

* 重置部门名称

*/

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));

}

```