ANSI SQL 提供了递归 CTE（公共表表达式），使用 WITH RECURSIVE 语法标准化于 SQL:1999。为了防止在层次遍历中出现无限循环，该标准定义了 CYCLE 检测子句，可以自动跟踪已访问的节点并在重新访问节点时终止特定分支。这个机制允许查询处理包含循环引用的图结构，而不会挂起或消耗无限资源。
当数据库系统缺乏原生的 CYCLE 子句支持时，您必须在递归成员中实现手动路径跟踪。您可以使用字符串连接或数组聚合构建一个路径列，该列积累已访问的标识符，然后过滤递归连接以排除当前节点已存在于构建路径中的行。这种方法在提供显式控制遍历终止条件的同时，保持了 ANSI SQL 的合规性。
一家制造公司维护着一个代表电子组件的 BOM 数据库，在该数据库中，组件包含子组件，数据输入错误偶尔会导致循环依赖。工程团队需要一个完整的组件爆炸报告，但现有的过程脚本在遇到这些循环时会由于无限循环而失败。他们需要一个完全在数据库引擎内运行的解决方案，以利用现有索引并最小化数据传输。
团队最初考虑了一个客户端 Python 解决方案，该方案抓取所有关系，并在应用内存中执行图遍历。虽然这种方法利用集合进行简单的循环检测，但在处理数百万个组件记录时，它引入了显著的网络开销和内存压力。此外，它违反了在数据库层保持逻辑的要求，确保事务一致性。
他们评估了第二种选择，使用存储过程进行显式堆栈管理和迭代。这种方法提供了对遍历深度的细粒度控制，但牺牲了 SQL 引擎的基于集合的优化能力。逐行处理比面向集合的替代方案慢得多，尤其是对于每个层级有许多分支的广泛层次结构。
选择的解决方案使用递归 CTE，通过一个数组路径列进行手动循环检测，兼容 PostgreSQL 和 Oracle 标准。锚定成员选择根组件，而递归成员仅在子组件标识符不包含在正在累积的路径数组中时才连接到子组件，使用 NOT (child_id = ANY(path_array))。该实现成功地在 400 毫秒内识别出生产数据中的七个循环引用链，同时保持了纯声明性的 ANSI SQL 语法。
为什么在递归 CTE 中选择 UNION 和 UNION ALL 会影响循环检测的准确性？
递归成员迭代执行，基于上一次迭代的结果集，直到返回零行。UNION 意味着 DISTINCT，在下一次递归开始之前会消除整个结果集中的重复行。如果两个不同的遍历路径到达同一个节点，UNION 可能会去重一个实例，导致路径跟踪机制错过会形成循环的替代路径，从而导致循环检测的假阴性。
如何在使用手动路径跟踪时区分合法的深层次层次结构和循环？
候选人通常通过仅比较直接父节点标识符来实现循环检测，而不是完整的祖先链。这种缺陷的做法无法检测在层次结构中更高处发生的循环，例如祖父-孙子循环，因为直接父节点与当前节点不同。一个稳健的解决方案会验证当前节点与路径列中的所有已累积祖先标识符，从而确保检测到遍历历史中的任何深度的循环。
在递归遍历中，实践中的内存考虑如何区分深度优先搜索和广度优先搜索？
深度优先搜索 使用基于堆栈的方法，仅在内存中保留从根到叶子的当前路径，对于深而狭的层次结构在内存上是高效的。广度优先搜索 保持当前深度层次的所有节点前沿，这对于有成千上万的兄弟节点的广泛图形可能消耗大量内存。虽然标准 ANSI SQL 支持这两种搜索策略，但选择不适合您数据拓扑的错误策略可能会导致内存耗尽或临时磁盘溢出，进而显著降低性能。