0Pricing
SQL Academy · 课时

避免无限循环

了解深度限制和循环检测

避免无限循环 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

无限循环问题

递归 CTE 功能强大，但也存在一个严重风险：如果查询永远无法到达锚点情况，就会无限循环，耗尽所有可用内存并导致数据库会话崩溃。

了解无限循环发生的原因，是防止此类问题的第一步。

循环何时不会结束？

当递归项不断生成新行，却始终无法到达不再生成新行的状态时，递归 CTE 就会无限循环。

这通常发生在两种情况下：缺少或错误的终止条件，或者数据存在循环，其中节点 A 指向 B，而 B 又指回 A。

-- Simple recursive CTE that WOULD loop forever
-- (do NOT run this as-is; illustration only)
WITH RECURSIVE counter AS (
  SELECT 1 AS n          -- base case
  UNION ALL
  SELECT n + 1           -- recursive term
  FROM counter
  -- no WHERE clause to stop it!
)
SELECT n FROM counter;

添加深度限制

最简单的防护措施是使用深度计数器。添加一列，在每个递归步骤中将其加 1，然后在超过最大深度时停止。

无论数据情况如何，这都能保证递归终止，而所选的限制值会提供一个安全上限。

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM counter
  WHERE n < 10       -- stop at depth 10
)
SELECT n FROM counter;

层级查询中的深度限制

遍历员工层级结构时，您可以同时跟踪深度和路径。WHERE depth < 5 子句可以防止遍历超过 5 个层级，即使数据中存在更深层级或循环链接也一样。

CREATE TEMP TABLE employees (
  id   INT PRIMARY KEY,
  name TEXT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   1),
  (3, 'Carol', 2),
  (4, 'Dave',  3);

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- root

  UNION ALL

  SELECT e.id, e.name, e.manager_id, h.depth + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.id
  WHERE h.depth < 5                 -- depth limit
)
SELECT id, name, depth FROM hierarchy ORDER BY depth, id;

什么是循环检测？

在图数据中，如果沿着边遍历最终回到已经访问过的节点，就会形成循环。例如：A → B → C → A。

深度限制仍然可以让循环数据中的查询终止，但它无法告诉您循环位于何处。显式循环检测则可以做到这一点。

CREATE TEMP TABLE edges (
  from_node INT,
  to_node   INT
);

-- Introduce a cycle: 1->2->3->1
INSERT INTO edges VALUES
  (1, 2),
  (2, 3),
  (3, 1),   -- cycle back to 1
  (1, 4);   -- also a non-cyclic branch

SELECT * FROM edges;

使用数组跟踪已访问的节点

一种可靠的循环检测技术是在递归过程中携带一个已访问节点标识符数组。访问下一个节点之前，检查它是否已经存在于数组中。如果存在，就跳过它。

PostgreSQL 借助 ANY(array) 运算符和 || 数组追加运算符，可以轻松实现这一点。

WITH RECURSIVE traverse AS (
  -- Start from node 1
  SELECT from_node,
         to_node,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE NOT (e.from_node = ANY(t.visited))   -- skip visited nodes
)
SELECT from_node, to_node, visited
FROM traverse;

CYCLE 子句（PostgreSQL 14+）

PostgreSQL 14 为递归 CTE 引入了内置的 CYCLE 子句。它会自动添加两列：检测到循环时值为 true 的布尔标志，以及记录遍历路径的数组。

这比手动维护数组更加简洁。

WITH RECURSIVE traverse AS (
  SELECT from_node, to_node
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node, e.to_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
)
CYCLE from_node SET is_cycle USING path
SELECT from_node, to_node, is_cycle, path
FROM traverse;

结合深度限制和循环检测

同时使用深度限制和循环检测，可以提供最强的安全保障：

  • 无论数据质量如何，深度限制都会充当硬性上限。
  • 循环检测会在发现循环的瞬间提前停止，从而避免不必要的迭代。

在生产环境的查询中，请始终至少应用其中一种防护措施。

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10                           -- depth limit
    AND NOT (e.from_node = ANY(t.visited))     -- cycle guard
)
SELECT from_node, to_node, depth, visited
FROM traverse;

将完整路径构建为字符串

除了循环检测之外，将完整的遍历路径记录为人类可读的字符串也很有用。使用 -> 分隔节点标识符进行拼接，可以轻松显示或调试图中的遍历路线。

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited,
         from_node::TEXT AS path_str
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node,
         t.path_str || ' -> ' || e.from_node::TEXT
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10
    AND NOT (e.from_node = ANY(t.visited))
)
SELECT from_node, to_node, path_str, depth
FROM traverse
ORDER BY depth;

设置最大递归迭代次数

某些数据库（MariaDB、旧版 MySQL）使用会话变量来限制递归次数。在 PostgreSQL 中，等效做法是依靠您自行编写的深度计数器，或使用语句级超时。

设置 statement_timeout 是一种最后防线，可在设定时间后终止任何失控的查询。

-- PostgreSQL: set a statement timeout as a safety net
SET statement_timeout = '5s';

-- Now any query that runs longer than 5 seconds is cancelled
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 1000000
)
SELECT MAX(n) FROM counter;

-- Reset to default when done
SET statement_timeout = '0';

选择合适的深度限制

不存在适用于所有情况的深度限制。请根据数据中合理的最大深度进行选择：

  • 组织结构图很少超过 10–15 个层级，因此可以使用 depth < 20 作为宽裕的缓冲。
  • 文件系统树的深度可能达到 50–100 个层级。
  • 社交网络图遍历通常限制为 3–6 跳。

将限制设置得足够高，以涵盖有效数据；同时也要足够低，以便尽早发现失控的查询。

-- Example: org chart with a generous but safe depth cap
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
  WHERE o.depth < 20    -- realistic upper bound for an org chart
)
SELECT id, name, depth
FROM org
ORDER BY depth, name;

深度限制与循环检测

您应该使用哪种技术？

回顾：确保递归查询安全

下面总结了您所学的避免递归 CTE 无限循环的方法：

  • 深度限制 — 添加计数器列，并使用 WHERE depth < N 停止。始终有效，且易于实现。
  • 基于数组的循环检测 — 在数组中携带已访问节点的标识符，并跳过其中已有的节点。在首次发现循环时提前停止。
  • CYCLE 子句（PostgreSQL 14+） — 使用 is_cycle 和 path 列自动跟踪循环的内置语法。
  • 语句超时 — 针对失控查询的数据库级安全防护，不能替代正确的逻辑。
  • 同时使用两者 — 在生产环境中结合深度限制和循环检测，以获得最强的保障。

借助这些技术，您可以放心遍历层级结构和图，而不必担心数据库崩溃。

常见问题解答

「避免无限循环」课时是免费的吗？

是的 — 「避免无限循环」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「避免无限循环」这节课中我会学到什么？

了解深度限制和循环检测 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「避免无限循环」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 递归 CTE 的工作原理
  2. 遍历分类树
  3. 生成序列
  4. 避免无限循环
← 返回 SQL Academy