SQL 递归查询基础：WITH RECURSIVE 语法与树/图遍历
说实话，刚接触反欺诈图谱的时候，我脑子里第一个念头就是——这种父子关系、多层传递的数据，用普通 SQL 怎么写？
后来我发现，SQL 早就准备好了答案：WITH RECURSIVE。它就像一把专门为树和图设计的瑞士军刀。今天我就跟你聊聊这把刀怎么用。
为什么需要递归查询？
举个我在项目中遇到的场景：某个用户设备关联了多个账号，这些账号又关联了其他设备，再关联更多手机号……你想想看，这就是一张典型的图结构。
用 JOIN 硬写？
- 层级深度不确定，你没法提前写好 10 层 JOIN
- 而且每层 JOIN 性能爆炸，维护起来更是噩梦
递归查询正是为了解决这种「深度未知但需要完整遍历」的场景。
| 场景 | 传统做法 | 递归做法 |
|---|---|---|
| 查员工下属树 | 反复 JOIN 直到 NULL | CTE 递归一次搞定 |
| 找设备关联的团伙 | 多层子查询 | 递归遍历图 |
| 计算 BOM 物料展开 | 存储过程循环 | WITH RECURSIVE 简洁 |
WITH RECURSIVE 语法结构
说白了，就三部分：锚点 + 递归 + 终止条件。
WITH RECURSIVE cte_name AS (
-- 1. 锚点（起始行）
SELECT ...
FROM table
WHERE condition
UNION ALL
-- 2. 递归部分
SELECT ...
FROM table
JOIN cte_name ON ...
)
-- 3. 最终查询
SELECT * FROM cte_name;
我习惯把锚点叫做「种子」——就是整个遍历的起点。递归部分每次从上一轮结果出发，扩展新节点。终止条件由递归部分自身的 JOIN 条件天然保证：当没有新记录产生时，递归自动停止。
小技巧： 递归部分里一定不要写成
UNION，要用 UNION ALL。因为我们需要保留所有路径，而不是去重。去重反而可能导致死循环。
实战：遍历一个设备关联图
来看一张反欺诈中常见的设备-账户二部图。
CREATE TABLE links (
id INT PRIMARY KEY,
from_type VARCHAR(10), -- 'device' 或 'account'
from_id VARCHAR(50),
to_type VARCHAR(10),
to_id VARCHAR(50),
link_time DATETIME
);
INSERT INTO links VALUES
(1, 'device', 'D1', 'account', 'A1', '2024-01-01'),
(2, 'account', 'A1', 'device', 'D2', '2024-01-02'),
(3, 'device', 'D2', 'account', 'A2', '2024-01-03'),
(4, 'account', 'A2', 'device', 'D3', '2024-01-04'),
(5, 'device', 'D1', 'account', 'A3', '2024-01-05');
现在想从设备 D1 出发，找出所有相连的设备和账户。
WITH RECURSIVE graph AS (
-- 锚点：D1 直接关联
SELECT id, from_type, from_id, to_type, to_id, 1 AS depth
FROM links
WHERE from_type = 'device' AND from_id = 'D1'
UNION ALL
-- 递归：从已找到的节点继续扩展
SELECT l.id, l.from_type, l.from_id, l.to_type, l.to_id, g.depth + 1
FROM links l
JOIN graph g ON (
(g.to_type = 'device' AND g.to_id = l.from_id AND l.from_type = 'device')
OR
(g.to_type = 'account' AND g.to_id = l.from_id AND l.from_type = 'account')
)
WHERE g.depth < 10 -- 防止循环过深
)
SELECT DISTINCT from_type, from_id, to_type, to_id, depth
FROM graph
ORDER BY depth, from_id;
输出结果会展示出 D1 → A1 → D2 → A2 → D3 这条路径，以及 D1 → A3 这条路径。深度字段帮我们看清扩散层级。
实际效果： 我曾在一次团伙识别中，用这个查询从一台设备出发，5 层就找到了超过 200 个关联账号和 80 台设备。如果手工写 JOIN，那代码量……嗯，不敢想。
遍历图时的循环陷阱
图结构和树不一样，图里有环。比如 A → B → C → A。如果不处理，递归会无限循环直到超时。
我曾经在生产库上踩过这个坑。当时跑了一个包含环的图遍历，结果数据库 CPU 飙到 100%，最后 OOM 崩溃。
怎么防？我总结了三个办法：
- 限制深度：在递归部分加
WHERE depth < n，一般 n 取 10~20 - 记录已访问路径：用一个字符串拼接所有已访问的节点 ID，每次递归检查是否重复
- 用 GROUP BY 去重：最终查询时对节点去重，避免相同节点反复输出
实际项目中，我比较推荐「限制深度 + 去重」组合拳。深度限制避免查询无限跑，去重保证结果干净可读。
归纳总结：树 vs 图 的递归查询差异
| 维度 | 树 | 图 |
|---|---|---|
| 是否有环 | 无环 | 可能有环 |
| 是否需要防循环 | 不需要，深度有限即可 | 必须防循环 |
| 递归方向 | 通常是单向（父→子或子→父） | 双向都可能 |
| 典型场景 | 组织架构、分类目录 | 社交关系、设备账号关联 |
| 结果集要求 | 保留路径顺序 | 通常只取不重复节点 |
注意： 如果你的数据库是 MySQL 8.0 以下版本，不支持 WITH RECURSIVE 怎么办？我建议要么升级到 8.0+，要么改用存储过程加临时表模拟递归。但说实话，升级是最省心的，别跟自己过不去。
核心流程图：递归遍历链路
下面这张图帮你理清整体流程——从输入起点，到锚点查询，再到递归扩展，最后输出结果。
日常小贴士
- 性能优化： 递归部分涉及的列尽量建索引。比如 on
from_type, from_id，to_type, to_id。我试过没索引的递归查询，50 层跑了 8 分钟；加了索引后 10 秒以内。 - 调试技巧： 先用
LIMIT 100看看递归结果对不对，确认逻辑无误了再放开限制。 - 递归深度： 反欺诈场景通常 5~10 层就覆盖大部分网络了，没必要设得太高。