MySQL 递归查询主要用到了 递归公共表表达式（CTE），MySQL 从 8.0 版本开始支持递归 CTE。递归查询常用于处理树形结构，比如组织架构、目录树等。
MySQL 递归查询（递归CTE）示例
1. 表结构举例（员工表）
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT -- 上级ID，顶层为NULL
);
INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'Manager1', 1),
(3, 'Manager2', 1),
(4, 'Employee1', 2),
(5, 'Employee2', 2),
(6, 'Employee3', 3);
2. 递归查询语法
WITH RECURSIVE subordinates AS (
-- 递归锚点，查顶层（CEO）
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归部分，查找直属下属
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
3. 结果示例
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 0 |
| 2 | Manager1 | 1 | 1 |
| 3 | Manager2 | 1 | 1 |
| 4 | Employee1 | 2 | 2 |
| 5 | Employee2 | 2 | 2 |
| 6 | Employee3 | 3 | 2 |
4. 解释
WITH RECURSIVE定义递归公共表表达式（CTE）。- 锚点查询顶层（
manager_id IS NULL）。 - 递归部分将员工表和子查询结果连接，找到所有下属。
level字段表示层级深度。
5. 其他常见用法
- 查找某个员工的所有上级：
WITH RECURSIVE managers AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 5 -- 某个员工ID
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;
好的！下面给你详细讲解 MySQL 递归查询中常用的路径拼接、限制递归层数，以及非 MySQL 8.0 版本实现递归查询的替代方案。
一、路径拼接的树形结构查询
用于显示完整的上下级路径，比如“CEO > Manager1 > Employee1”。
WITH RECURSIVE subordinates AS (
SELECT
id,
name,
manager_id,
CAST(name AS CHAR(1000)) AS path,
0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
CONCAT(s.path, ' > ', e.name),
s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
二、限制递归层数
避免递归过深导致性能问题或死循环。
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
WHERE s.level < 3 -- 限制最大递归层数为3
)
SELECT * FROM subordinates;
三、MySQL 8.0 以下版本递归查询替代方案
MySQL 8.0 之前不支持递归 CTE，常用方案：
1. 自连接（手动多层连接）
适用于已知层数不深的情况，比如3层：
SELECT e1.id, e1.name, e2.name AS manager_name, e3.name AS top_manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
LEFT JOIN employees e3 ON e2.manager_id = e3.id;
缺点：不灵活，层数固定。
2. 存储过程实现递归
用存储过程和临时表实现递归：
DELIMITER $$
CREATE PROCEDURE get_subordinates(IN mgr_id INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_subs;
CREATE TEMPORARY TABLE temp_subs (id INT PRIMARY KEY);
INSERT INTO temp_subs VALUES (mgr_id);
WHILE ROW_COUNT() > 0 DO
INSERT IGNORE INTO temp_subs
SELECT e.id FROM employees e
JOIN temp_subs ts ON e.manager_id = ts.id;
END WHILE;
SELECT e.* FROM employees e
JOIN temp_subs ts ON e.id = ts.id;
END $$
DELIMITER ;
调用：
CALL get_subordinates(1); -- 查CEO及其所有下属
3. 其他方案
- 在应用层递归：用程序代码多次查询构建树。
- 使用第三方插件或数据库（如 MariaDB 的 CONNECT 存储引擎，或者使用支持递归查询的数据库如 PostgreSQL）。
明白了！下面我帮你写一个结合路径拼接的 MySQL 存储过程递归实现示例，适合 MySQL 8.0 以下版本，能递归查找所有下属并拼接上下级路径。
MySQL 存储过程递归查询（路径拼接版）
假设有表 employees，结构和数据如下：
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'Manager1', 1),
(3, 'Manager2', 1),
(4, 'Employee1', 2),
(5, 'Employee2', 2),
(6, 'Employee3', 3);
存储过程代码
DELIMITER $$
CREATE PROCEDURE get_subordinates_with_path(IN mgr_id INT)
BEGIN
-- 临时表存放结果，包含员工ID和路径
DROP TEMPORARY TABLE IF EXISTS temp_subs_path;
CREATE TEMPORARY TABLE temp_subs_path (
id INT PRIMARY KEY,
path VARCHAR(1000)
);
-- 插入初始节点（上级）
INSERT INTO temp_subs_path (id, path)
SELECT id, name FROM employees WHERE id = mgr_id;
DECLARE rows_affected INT DEFAULT 1;
-- 循环递归
WHILE rows_affected > 0 DO
INSERT IGNORE INTO temp_subs_path (id, path)
SELECT e.id, CONCAT(t.path, ' > ', e.name)
FROM employees e
JOIN temp_subs_path t ON e.manager_id = t.id
WHERE e.id NOT IN (SELECT id FROM temp_subs_path);
SET rows_affected = ROW_COUNT();
END WHILE;
-- 返回结果
SELECT * FROM temp_subs_path ORDER BY path;
END $$
DELIMITER ;
调用存储过程
CALL get_subordinates_with_path(1);
返回结果示例
| id | path |
|---|---|
| 1 | CEO |
| 2 | CEO > Manager1 |
| 4 | CEO > Manager1 > Employee1 |
| 5 | CEO > Manager1 > Employee2 |
| 3 | CEO > Manager2 |
| 6 | CEO > Manager2 > Employee3 |
好的！下面给你一个Python调用上面存储过程的示例，演示如何连接 MySQL 执行存储过程并获取结果。示例使用常用的 mysql-connector-python 库，你也可以用 pymysql 或其他库，思路类似。
Python 调用 MySQL 存储过程示例
import mysql.connector
def call_get_subordinates_with_path(mgr_id):
# 连接数据库
conn = mysql.connector.connect(
host='localhost',
user='your_user',
password='your_password',
database='your_database'
)
cursor = conn.cursor()
# 调用存储过程
cursor.callproc('get_subordinates_with_path', [mgr_id])
# 存储过程可能返回多个结果集，遍历获取
for result in cursor.stored_results():
rows = result.fetchall()
print(f"{'ID':<5} {'Path'}")
print('-' * 40)
for row in rows:
print(f"{row[0]:<5} {row[1]}")
cursor.close()
conn.close()
if __name__ == '__main__':
manager_id = 1 # 查询CEO及所有下属
call_get_subordinates_with_path(manager_id)
说明
- 请替换
host、user、password、database为你的数据库配置。 cursor.callproc调用存储过程，传入参数列表。- 通过
cursor.stored_results()获取存储过程返回的结果集。 - 逐行打印 ID 和路径。
好的！下面给你一个 Java（JDBC）调用 MySQL 存储过程 的示例，演示如何连接数据库、调用之前写的 get_subordinates_with_path 存储过程，并获取结果集。
Java 调用 MySQL 存储过程示例（JDBC）
import java.sql.*;
public class CallStoredProcedure {
public static void main(String[] args) {
// 数据库连接参数
String url = "jdbc:mysql://localhost:3306/your_database?useSSL=false&serverTimezone=UTC";
String user = "your_user";
String password = "your_password";
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
// 加载MySQL驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
conn = DriverManager.getConnection(url, user, password);
// 调用存储过程，?为输入参数占位符
String sql = "{CALL get_subordinates_with_path(?)}";
stmt = conn.prepareCall(sql);
// 设置输入参数
stmt.setInt(1, 1); // 例如查询经理ID=1的下属
// 执行存储过程
boolean hasResultSet = stmt.execute();
// 处理结果集
while (hasResultSet) {
rs = stmt.getResultSet();
System.out.printf("%-5s %-50s\n", "ID", "PATH");
System.out.println("-------------------------------------------------------");
while (rs.next()) {
int id = rs.getInt("id");
String path = rs.getString("path");
System.out.printf("%-5d %-50s\n", id, path);
}
hasResultSet = stmt.getMoreResults();
}
} catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("Database error.");
e.printStackTrace();
} finally {
// 关闭资源
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
说明
- 替换
url、user、password为你的数据库连接配置。 - 使用
CallableStatement调用存储过程，传入输入参数。 - 使用
stmt.execute()执行，返回结果集后循环获取数据。 - 处理多个结果集用
stmt.getMoreResults()。
好的！下面给你一个 Spring Boot 调用 MySQL 存储过程 的示例，示范如何通过 JdbcTemplate 和 MyBatis 调用之前写的存储过程 get_subordinates_with_path。
一、使用 Spring JdbcTemplate 调用存储过程示例
1. 配置依赖（pom.xml）
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
2. 代码示例
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Service;
import javax.annotation.PostConstruct;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;
@Service
public class EmployeeService {
@Autowired
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall simpleJdbcCall;
@PostConstruct
public void init() {
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("get_subordinates_with_path")
.returningResultSet("result", (ResultSet rs, int rowNum) -> {
int id = rs.getInt("id");
String path = rs.getString("path");
return new EmployeeDTO(id, path);
});
}
public List<EmployeeDTO> getSubordinatesWithPath(int managerId) {
Map<String, Object> out = simpleJdbcCall.execute(managerId);
return (List<EmployeeDTO>) out.get("result");
}
}
class EmployeeDTO {
private int id;
private String path;
public EmployeeDTO(int id, String path) {
this.id = id;
this.path = path;
}
// getter/setter略
}
二、MyBatis 调用存储过程示例
1. Mapper XML 配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.EmployeeMapper">
<select id="getSubordinatesWithPath" statementType="CALLABLE" resultType="com.example.dto.EmployeeDTO">
{CALL get_subordinates_with_path(#{managerId, mode=IN, jdbcType=INTEGER})}
</select>
</mapper>
2. Mapper 接口
package com.example.mapper;
import com.example.dto.EmployeeDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface EmployeeMapper {
List<EmployeeDTO> getSubordinatesWithPath(@Param("managerId") int managerId);
}
3. DTO 类
package com.example.dto;
public class EmployeeDTO {
private int id;
private String path;
// 构造、getter、setter
}
三、调用示例
@Autowired
private EmployeeService employeeService;
public void test() {
List<EmployeeDTO> list = employeeService.getSubordinatesWithPath(1);
list.forEach(e -> System.out.println(e.getId() + " -> " + e.getPath()));
}
或者直接用 MyBatis Mapper：
@Autowired
private EmployeeMapper employeeMapper;
public void test() {
List<EmployeeDTO> list = employeeMapper.getSubordinatesWithPath(1);
list.forEach(e -> System.out.println(e.getId() + " -> " + e.getPath()));
}
发表回复