SQL递归查询-防环-优快云博客

你遇到的问题是：递归 CTE 超过了 1000 次迭代（默认最大深度），而且这个递归死循环（或爆炸性增长）可能是由于数据中形成了环（循环），也就是设备通过端口和线缆间接地又连回自己或连回上游设备，导致递归永不终止。

✅ 问题分析

你这个 SQL 逻辑的核心是：

从某设备的 IN 端口出发；
找到该端口所连接的线缆；
然后找到线缆的另一个端口（对端）；
再根据对端端口所在的设备继续向上游递归。

只要形成了环（比如设备 A → B → C → A），你的递归就会无限回圈，直到 MySQL 强制终止（1000 次）：

🧠 数据中确实存在循环

从你给的数据看，可以推导出如下路径片段：

设备 15（port_id 1，OUT） → cable_id 1 → port_id 2（IN） → device_id 4
设备 4：
- port_id 3（OUT） → cable_id 2 → port_id 15（IN） → device_id 7
- port_id 13（OUT） → cable_id 12 → port_id 25（IN） → device_id 14
- port_id 14（OUT） → cable_id 13 → port_id 26（IN） → device_id 14

现在看这个：

device 4 有多个 OUT 端口（port_id 3, 13, 14）
其中：
- port_id 3 → cable 2 → port_id 15 → device 7
- port_id 15 属于 device 7
- 但是你又有可能在其他路径中从 device 7 再连回 device 4

虽然你贴的数据不完整，但这已经能构成潜在的循环链路。

✅ 解决方法

✅ 方法一：在递归中加上路径防环条件

我们可以使用一个路径记录列表 path，用于判断是否已经走过某个设备。在递归过程中 记录每个已经走过的设备，下一步要前进之前，先检查是否已经走过，如果走过就不再继续 —— 这就是“防环”。

修改 CTE，加一个 path 字段，用于检测是否出现回路：

✅ 修改后的 SQL（带路径记录）

WITH RECURSIVE upstream_devices AS (
    -- 第一步：从起始 device 的 IN 端口出发，找其连接的对端设备
    SELECT 
        d.device_id AS start_device_id,
        p2.device_id AS connected_device_id,
        CAST(CONCAT(',', d.device_id, ',') AS CHAR(1000)) AS path  -- 初始化路径字符串
    FROM device d
        JOIN port p1 ON p1.device_id = d.device_id
        JOIN port_cable_relation pcr1 ON p1.port_id = pcr1.port_id
        JOIN port_cable_relation pcr2 ON pcr1.cable_id = pcr2.cable_id AND p1.port_id != pcr2.port_id
        JOIN port p2 ON p2.port_id = pcr2.port_id
    WHERE d.device_id = ?
        AND p1.direction = 'IN'

    UNION ALL

    -- 第二步：递归查找 connected_device_id 的 IN 端口继续上溯
    SELECT 
        ud.start_device_id,
        p2.device_id AS connected_device_id,
        CONCAT(ud.path, p2.device_id, ',') AS path  -- 把当前 device_id 加入路径
    FROM upstream_devices ud
        JOIN port p1 ON p1.device_id = ud.connected_device_id
        JOIN port_cable_relation pcr1 ON p1.port_id = pcr1.port_id
        JOIN port_cable_relation pcr2 ON pcr1.cable_id = pcr2.cable_id AND p1.port_id != pcr2.port_id
        JOIN port p2 ON p2.port_id = pcr2.port_id
    WHERE p1.direction = 'IN'
        -- ✅ 防环判断：路径里已经包含这个设备就不再递归
        AND LOCATE(CONCAT(',', p2.device_id, ','), ud.path) = 0
)
SELECT DISTINCT connected_device_id FROM upstream_devices;

✅ 每一段的含义与作用

🔹 初始查询部分（非递归）

SELECT d.device_id AS start_device_id, p2.device_id AS connected_device_id, CAST(CONCAT(',', d.device_id, ',') AS CHAR(1000)) AS path

d.device_id: 起点设备 ID；
p2.device_id: 通过线缆连到的另一个设备；
path: 用字符串记录走过的设备（用逗号包裹方便定位，例如 ,15,4,7,）；
- 为什么这么设计？因为这样我们可以通过 LOCATE(',7,', path) 精准判断设备是否走过，避免 7 和 17 混淆。

🔹 递归部分

SELECT ud.start_device_id, p2.device_id AS connected_device_id, CONCAT(ud.path, p2.device_id, ',') AS path

继续从上次递归得到的 connected_device_id（即 ud.connected_device_id）出发；
继续查找它的 IN 端口连接的线缆；
找线缆另一头的端口，再拿到其设备；
并把这个设备加入路径中（用于下次判断环）；
核心防环判断：

AND LOCATE(CONCAT(',', p2.device_id, ','), ud.path) = 0

如果路径中已经包含了下一个设备（比如 ,15,4,7, 中已含 7），就不会再递归，从而避免进入死循环。

🔹 最终输出

SELECT DISTINCT connected_device_id FROM upstream_devices;

你要的其实就是所有“通过递归上溯能找到的设备 ID”，所以这里去重输出。

✅ 示例场景讲解

假设设备连接如下：

设备15 → cable1 → 设备4 → cable2 → 设备7 → cableX → 设备15

设备15最终又回到了自己，这种情况会导致递归循环。

路径变化过程如下：

步骤	当前设备	路径（path）	是否继续？
1	15	`,15,`	是（初始）
2	4	`,15,4,`	是
3	7	`,15,4,7,`	是
4	15	`,15,4,7,15,`	❌ 已有 15
停止			防止进入环路！

✅ 你可能的后续优化建议

记录路径中的完整连接链路（不只是 device_id）：可以记录 port_id、cable_id，方便可视化；
限制最大递归层数（额外保险）：虽然已经防环，但你也可以控制最多递归 50 层，例如用 LENGTH(path) < 200 等条件；
把 connected_device_id 换成设备名称，提升可读性；
调试用：输出完整路径 path 便于分析连接链。

✅ 方法二：在递归中加上递归深度限制

✅ 方法三：允许记录环路，但标记它是环

✅ 在连接表中新增字段标记环路

字段名	类型	说明
`is_loop`	TINYINT(1)	是否构成环（0=否，1=是）
`loop_id`	VARCHAR	环路编号，方便后续追踪哪个设备属于同一个环

✅ 使用逻辑：

当你准备写入连接 A → B 时，先用递归判断是否 B 已经能到达 A
如果能，说明 A → B 会形成环
✅ 允许写入，但 is_loop=1，并记录这个环路编号（如自动生成 loop-20250704-001）

✅ 总结

关键点	解释
为什么递归死循环？	数据中存在环结构，设备间连回自己或祖先。
如何解决？	在递归中记录“路径”，避免重复访问同一设备。
为什么要用 `LOCATE` 判断？	避免设备重复（环）时继续递归。
还有更保险做法吗？	可以额外加最大递归层数限制。