MySQL8.0.19-通过Limit调试递归CTE

作者：Guilhem Bichot 译：徐轶韬

在MySQL 8.0.1中，我们引入了对递归通用表表达式（CTE）的支持。今天，我想提出一个解决方案，当使用递归CTE编写查询时，几乎每个人都会遇到：发生无限递归时，如何调试？

考虑以下示例查询，该查询生成从1到5的整数：

此查询正常执行，这是它的结果：

现在，假设我在键入它时犯了一个小错误（这只是键盘意外，在“ <”中输入“ *”）：

MySQL现在将错误的WHERE条件转换为“ n * 5 <> 0”，这对于所有行都是正确的。因此，递归算法将生成越来越多的行，进行越来越多的迭代，直到达到默认的最大迭代次数，从而导致错误： ““ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.”

在这种情况下，增加变量不会有任何改善。解决方案是仔细检查查询，直到发现输入错误为止。

尽管这只是一个小示例，但CTE可以永远递归还有其他原因：查询可能非常复杂，我们犯了逻辑错误；或数据集可能是格式错误的层次结构，并且包含意外的循环。或者，在开发时一切都正确，但是，过了一段时间，数据模型得到了发展，原本不可能的循环现在变得完全可能了，查询需要进行一些修复以免阻塞。

举一个例子，关于火箭发射的场景。

2300年，地球人满为患，鼓励人们乘坐下面的太空火箭，迁徙到附近的星球：

请注意，地球的统治者没有建立任何从这些行星返回地球的方法。让我们列出所有可以从地球到达的目的地：首先找到可以直接到达的行星，然后找到可以从这些行星直接到达的行星，依此类推：

现在是2400年，地球上的人口减少了太多，统治者决定将一些移民带回去，于是他们从土星向地球发射了一枚新火箭：

让我们重复一下查询以列出可以从地球到达的所有目的地：

在查询开发很久以后，数据经历了发展，现在需要进行一些修复。而且，修复它的开发人员并不知道是新的土星到地球火箭的原因。他正在寻找发现问题的方法。

因此，无论出于何种原因，您所遇到的只是上述3636错误，现在该怎么办？ 如果查询有数十行，您如何了解逻辑错误在哪里？ 如果数据可以合法地显示一个循环，那么最终您可能会修改查询，以便进行循环检测。但是在进行此类修改之前，您可能更希望先了解循环是如何形成的，涉及哪些表、列……。

为此，MySQL可以做什么来帮助我们调试问题？

从版本8.0.19开始，我使它允许任何递归CTE包含LIMIT子句。因此，递归算法将开始工作，照常运行迭代，累积行，并在这些行的数量超过LIMIT时停止。这时CTE将被视为已完成，并且不会发出任何错误。

当遇到错误3636时，我们现在要做的第一件事是添加一些LIMIT（最初10行？还是100？我们可以尝试）。然后获取查询的输出；通过查看这些数据，我们可以开始了解它们是如何生成的以及为什么产生这么多。

在我们的例子里：

首先是火星，然后是木星，土星，地球，火星！好吧，我们看到似乎在5行之后形成循环；通过在到达的每个行星附近添加路径上的前一个行星，它变得更加清晰：

发现了！有一个土星到地球的火箭，它关闭了循环！这项工作的调试部分已经完成。

在本文的结尾，虽然LIMIT-in-CTE可能不会改变SQL 的面貌，但我相信它几乎可以为在MySQL中操作递归CTE的每个人节省时间，这是一件非常好的事情！

一如既往，感谢您选择MySQL！