<think>
我们正在讨论的是MySQL多级查询，特别是处理多级分类（如商品分类、地区分类）的情况。根据引用内容，多级查询通常涉及递归或层级数据的处理。
常见的多级查询需求包括：
1. 查询某个节点的所有子节点（向下递归）
2. 查询某个节点的所有父节点（向上递归）
3. 查询树形结构（多级菜单）
由于MySQL本身不支持递归查询（在MySQL 8.0之前），我们通常使用以下方法：
方法一：使用多次连接（JOIN）查询，但层级固定（适用于层级固定且不太深的情况）
方法二：使用存储过程或函数（如引用[3]所示）实现递归
方法三：在应用层处理（如使用MyBatis或JPA的ResultMap进行嵌套，如引用[4]所示）
方法四：使用闭包表（设计时增加一张表来存储节点之间的关系路径）
注意：MySQL 8.0开始支持WITH RECURSIVE语法，可以简化递归查询。
下面分别给出示例：
一、使用函数递归（适用于MySQL 8.0以下版本）
1. 向上递归（查询所有父节点）示例（参考引用[3]）：
创建一个函数，输入节点ID，返回所有父节点ID的字符串（用逗号分隔）。
二、使用MySQL 8.0的递归查询
1. 向下递归（查询所有子节点）：
```sql
WITH RECURSIVE cte AS (
SELECT id, pid, name
FROM sort
WHERE id = ? -- 指定起始节点
UNION ALL
SELECT s.id, s.pid, s.name
FROM sort s
INNER JOIN cte ON s.pid = cte.id
)
SELECT * FROM cte;
```
2. 向上递归（查询所有父节点）：
```sql
WITH RECURSIVE cte AS (
SELECT id, pid, name
FROM sort
WHERE id = ? -- 指定起始节点
UNION ALL
SELECT s.id, s.pid, s.name
FROM sort s
INNER JOIN cte ON s.id = cte.pid
)
SELECT * FROM cte;
```
三、使用多次连接（固定层级）
例如，假设我们最多有3级：
```sql
SELECT t1.name AS level1, t2.name AS level2, t3.name AS level3
FROM sort t1
LEFT JOIN sort t2 ON t2.pid = t1.id
LEFT JOIN sort t3 ON t3.pid = t2.id
WHERE t1.pid IS NULL;
```
四、使用闭包表（需要设计额外的表）
闭包表包含祖先节点和后代节点，以及它们之间的距离。查询所有子节点：
```sql
SELECT s.*
FROM sort s
JOIN closure_table c ON c.descendant_id = s.id
WHERE c.ancestor_id = ?;
```
五、在应用层处理（如使用MyBatis的嵌套结果映射，如引用[4]）
在MyBatis中配置ResultMap，通过递归查询实现多级菜单：
```xml
<resultMap id="menuResult" type="Menu">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="children" ofType="Menu" select="findMenuByParentId" column="id"/>
</resultMap>
<select id="findMenuByParentId" resultMap="menuResult">
SELECT * FROM menu WHERE parent_id = #{id}
</select>
```
六、使用邻接表模型（即当前表结构）的存储过程（如引用[3]中的函数）
示例函数（向上递归）：
```sql
DROP FUNCTION IF EXISTS queryParentAreaInfo;
DELIMITER ;;
CREATE FUNCTION queryParentAreaInfo(areaId INT) RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '';
SET sTempChd = CAST(areaId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp = CONCAT(sTemp, ',', sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END;;
DELIMITER ;
```
注意：在MySQL 8.0之前，递归查询通常使用存储过程或函数，但效率可能不高。在应用层递归查询（多次查询）也是一种常用方法。
总结：
1. 如果使用MySQL 8.0+，推荐使用递归公用表表达式（WITH RECURSIVE）。
2. 如果使用旧版本，可以考虑使用存储过程/函数，或者应用层递归，或者修改表结构（如闭包表）来优化查询。
根据引用[1]中的表结构，我们可以尝试用递归查询（MySQL 8.0）查询id=13的所有子节点：
```sql
WITH RECURSIVE cte AS (
SELECT id, pid, name
FROM sort
WHERE id = 13
UNION ALL
SELECT s.id, s.pid, s.name
FROM sort s
INNER JOIN cte ON s.pid = cte.id
)
SELECT * FROM cte;
```
结果应包含id=13, 133, 134的记录。
相关问题：
1. MySQL 8.0以下版本如何实现递归查询？
2. 闭包表如何设计？
3. 如何优化多级分类查询性能？
4. 在MyBatis中如何配置多级结果映射？
5. 如何处理无限级分类的增删改操作？
</think>### MySQL多级查询方法与示例
多级查询主要用于处理层级结构数据（如分类体系、组织架构等），以下是核心方法和示例：
---
#### 一、常用多级查询方法
1. **递归查询（MySQL 8.0+）**
使用 `WITH RECURSIVE` 实现递归查询（推荐方案）：
```sql
WITH RECURSIVE cte AS (
SELECT id, pid, name
FROM sort
WHERE id = 13 -- 起点ID
UNION ALL
SELECT s.id, s.pid, s.name
FROM sort s
JOIN cte ON s.pid = cte.id
)
SELECT * FROM cte;
```
**结果示例**：
| id | pid | name |
|-----|-----|---------|
| 13 | 1 | a_13 |
| 133 | 13 | a_13_3 |
| 134 | 13 | a_13_4 |
2. **多表连接查询**
适用于固定层级（需要明确层级深度）：
```sql
SELECT
t1.name AS level1,
t2.name AS level2,
t3.name AS level3
FROM sort t1
LEFT JOIN sort t2 ON t2.pid = t1.id
LEFT JOIN sort t3 ON t3.pid = t2.id
WHERE t1.pid IS NULL; -- 根节点条件
```
3. **存储过程/函数递归**（MySQL 5.7）
创建递归函数（参考引用[3]）：
```sql
DELIMITER ;;
CREATE FUNCTION get_parents(areaId INT) RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '';
SET sTempChd = CAST(areaId AS CHAR);
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp, ',', sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END ;;
DELIMITER ;
```
调用：`SELECT get_parents(134);` → 返回 `",1,13,134"`
4. **应用层递归查询**
结合MyBatis/JPA实现（参考引用[4]）：
```xml
<!-- MyBatis映射示例 -->
<resultMap id="menuResult" type="Menu">
<id property="id" column="id"/>
<collection property="children" select="findByParentId" column="id"/>
</resultMap>
```
---
#### 二、典型应用场景
1. **多级菜单查询**
```sql
-- 查询完整树形结构（递归）
WITH RECURSIVE menu_tree AS (
SELECT id, name, pid
FROM menu
WHERE pid IS NULL
UNION ALL
SELECT m.id, m.name, m.pid
FROM menu m
JOIN menu_tree mt ON m.pid = mt.id
)
SELECT * FROM menu_tree;
```
2. **分类路径查询**
```sql
-- 查询分类ID=134的完整路径
WITH RECURSIVE path_cte AS (
SELECT id, pid, name, CAST(name AS CHAR(255)) AS path
FROM sort
WHERE id = 134
UNION ALL
SELECT s.id, s.pid, s.name, CONCAT(s.name, ' > ', p.path)
FROM sort s
JOIN path_cte p ON s.id = p.pid
)
SELECT path FROM path_cte WHERE pid IS NULL;
```
**结果**：`a > a_13 > a_13_4`
---
#### 三、性能优化建议
1. **索引优化**：为 `pid` 字段添加索引
```sql
CREATE INDEX idx_sort_pid ON sort(pid);
```
2. **闭包表设计**：提前存储所有层级关系路径
3. **层级字段冗余**：增加 `level` 字段存储当前层级深度
4. **应用层分治**：对深层级数据分批次查询（避免单次递归过深）
> **重要提示**：MySQL 8.0以下版本推荐使用应用层递归（如Java/Python处理），避免存储过程性能瓶颈。MySQL 8.0+优先选用 `WITH RECURSIVE` 语法[^1][^3]。
---
### 相关问题
1. MySQL 8.0以下版本如何实现递归查询？
2. 多级分类表如何设计更高效？
3. 如何优化深层次递归查询的性能？
4. 闭包表（Closure Table）的具体实现方法是什么？
5. 在MyBatis中如何配置多级结果映射？[^4
多级查询与关系维护
扫一扫
1964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
