专栏首页MySQL解决方案工程师MySQL8.0.19-通过Limit调试递归CTE

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!

本文分享自微信公众号 - MySQL解决方案工程师(mysqlse),作者:Guilhem Bichot

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-02-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 查看MySQL查询计划的方法和格式

    查看MySQL的查询计划是分析查询的重要方法,可以通过使用EXPLAIN语句来确认优化器将采取哪种查询计划,是否与你的预期一致。

    MySQLSE
  • 必知必会——关于SQL中的NOT IN优化

    如果您使用 SELECT…WHERE x NOT IN(SELECT y FROM…)等“ NOT IN”编写SQL查询,必须了解当“ x”或“ y”为NULL...

    MySQLSE
  • InnoDB的数据锁–第1部分“简介”

    在这个博客系列中,我想向您简要介绍我最近两年从事的工作内容,改善InnoDB锁(表和行)的方式。 我希望从简单的情况和挑战的角度出发,逐步引入越来越多的现实世界...

    MySQLSE
  • 无线VR解决方案这么多,可为何迟迟不见成品推出?

    VRPinea
  • BDK | 资源如何进行分配才高效?

    使用者在使用我们提供的接口获取数据,会进行很多的数据聚合工作,比如统计过去1年的销量、过去1周销量TopK的商品之类的,也有涉及很多更加复杂的指标计算。

    Sam Gor
  • 移动VR平台掀起大战,一体机或将是未来

    VRPinea
  • UploadVR预测:VR将在2018年开始无线化

    VRPinea
  • 侦听局域网内密码

    只需在前面的网络嗅探程序基础上,添加对搜索出的端口号进行的增加功能即可: 代码如下: 在DecodeIPPacket中添加: switch(::ntohs(pT...

    用户1154259
  • React Router 使用教程

    真正学会 React 是一个漫长的过程。 ? 你会发现,它不是一个库,也不是一个框架,而是一个庞大的体系。想要发挥它的威力,整个技术栈都要配合它改造。你要学习一...

    ruanyf
  • 微软bing搜索国内版壁纸分享

    今天分享给大家一个高清壁纸的接口,是微软bing搜索-国内版的接口,日常浏览网站发现搜索首页的背景图每一天都在更换,处于本能的打开了F12看看怎么搞的。结果是必...

    小白程序猿

扫码关注云+社区

领取腾讯云代金券