MariaDB 教程 / 用递归 CTE 查询层级并防止循环访问
递归公共表表达式(recursive common table expression,递归 CTE)从初始行开始,反复沿关系查找下一批行,适合查询深度不固定的层级。可靠的查询需要明确起点、停止条件、足够宽的路径列、循环访问处理和最终排序。
何时需要递归,而不只是普通连接
普通 CTE为当前语句中的查询步骤命名;递归 CTE 还在定义中引用自身。锚点查询产生第一批行,递归部分依据前一步结果查找后续行,本例用 UNION ALL 合并它们。
分类树、组织结构等深度可变的关系适合递归遍历;如果只固定连接两层,普通 JOIN 可能更直接。数据量较大时,还需设计按父键找子节点的索引及明确的深度边界,不能只把上限无限增加。可结合索引与真实查询条件检查子节点查找路径。
MariaDB 的递归 CTE 概述解释了迭代计算与锚点列类型的作用。本篇六行样本用于理解正确性,不构成普遍的性能结论。
准备六行分类树与父键索引
使用管理员或具有所需建库、建表权限的学习账户,在 MariaDB 客户端执行下列完整设置。先检查 SHOW DATABASES;、实际选中的数据库及同名表。IF NOT EXISTS 并不证明已有 appops_lab 属于本练习;若已存在,应先确认归属和结构。category_tree 已存在时不能直接覆盖。
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
category_id INT NOT NULL PRIMARY KEY,
parent_id INT NULL,
category_name VARCHAR(40) NOT NULL,
INDEX ix_parent (parent_id),
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
(1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
(4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');
Catalog 是目录根,含 Books(图书)与 Tools(工具);Books 下有 SQL、Python,Tools 下有 Editors(编辑器)。图中的名称和编号保留 SQL 原值,深度标签使用中文,方便逐行对照。
ix_parent 支持按 parent_id 查找子节点。自引用外键能确认父编号存在,却不能证明全部层级没有循环;父节点存在与关系无环是两个不同条件。
运行包含深度与访问路径的完整查询
WITH RECURSIVE tree AS (
SELECT category_id, parent_id, category_name, 0 AS depth,
CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
CAST(category_name AS CHAR(1000)) AS name_path
FROM category_tree WHERE category_id = 1
UNION ALL
SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
CONCAT(t.visited, c.category_id, '/'),
CONCAT(t.name_path, ' > ', c.category_name)
FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
WHERE t.depth < 8
AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;
锚点从编号 1 开始,深度为零。递归部分把子行的 parent_id 与当前节点编号相连,深度加一。visited 在数字编号两侧使用斜线,例如 /1/ 不会误匹配 /11/,避免简单子串检查把不同编号混淆。
锚点中的两条路径都显式转换为 CHAR(1000)。若由初始短字符串决定过窄类型,后续拼接可能超出列宽。本例假设整数编号、较短名称、最多深度八;真实路径更长时,应根据最大名称、分隔符和深度重新计算宽度,而不是把 1000 当作通用安全值。
| ID | 父编号 | 深度 | name_path |
|---|---|---|---|
| 1 | NULL | 0 | Catalog |
| 2 | 1 | 1 | Catalog > Books |
| 3 | 1 | 1 | Catalog > Tools |
| 4 | 2 | 2 | Catalog > Books > SQL |
| 5 | 2 | 2 | Catalog > Books > Python |
| 6 | 3 | 2 | Catalog > Tools > Editors |
最终的 ORDER BY depth, category_id 才保证表中显示顺序,不能依据递归求值过程猜测输出顺序。按文本路径排序得到的是字典顺序,也可能不同于数字编号排序。
只修改一项条件,观察范围后恢复
先只把 t.depth < 8 改为 t.depth < 1 再运行。结果包含深度零的锚点及直接子节点,即 1、2、3。观察后恢复为八,再单独把锚点改为 category_id = 2,应得到 Books 子树 2、4、5,且 Books 的深度重新从零开始。比较结束恢复原锚点 1。
达到深度上限后停止,不代表已经返回完整层级。先确认真实结构是否更深,以及业务是否有意截断,再决定边界。服务器的max_recursive_iterations 迭代次数限制是另一道计算边界,不能替代业务停止条件或数据完整性检查。
在事务中引入循环,再立即回滚
在同一个连接执行以下两句,再重新运行上面的完整递归查询。另一连接可能看不到尚未提交的修改,不适合直接对照本实验。
START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;
将节点 1 的父节点设为 4,形成沿子关系的 1 → 2 → 4 → 1 循环。查询仍明确从编号 1 开始,visited 检查阻止再次返回已访问的 1,因此结束并返回六行;根行的 parent_id 在事务内变为 4。观察后立即恢复:
ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;
节点 1 的 parent_id 应重新为 NULL,再运行原查询应恢复最初结果。如果锚点改为 parent_id IS NULL,根消失后可能遗漏整个循环组件。因此某次遍历避免重复访问,不能证明数据库所有节点都无环,也不能证明没有无法到达的组件。
MariaDB 还提供 CYCLE ... RESTRICT 语法。本篇用显式路径呈现检查过程;选择其他语法时,也要明确循环键、起点和预期结果。
把查询终止与层级正确性分开检查
- 确认锚点准确选择所需根或多个根,没有遗漏目标组件。
- 分别检查不存在的父节点、自指、循环和不可达组件,不把它们视为同一种错误。
- 按业务选择最大深度,并计算能容纳完整结果的路径列宽。
- 检查子节点查找索引与 EXPLAIN,按预期关系核对行数和路径,而不是仅看查询能否结束。
清理前检查数据库与表的归属,备份要保留的内容。仅确认本篇 category_tree 可丢弃后,执行 DROP TABLE appops_lab.category_tree;;它是结构修改,普通 ROLLBACK 不能撤销。不要把业务表名代入该清理命令。





