如何获得mysql存储过程递归的深度? [英] How to get depth in mysql store procedure recursion?

问题描述

我有一个递归 mysql 存储过程，我已经为其设置了 max_sp_recursion_depth=10.

I have a recursive mysql stored procedures for which I have set the max_sp_recursion_depth=10.

现在，没有设置局部变量，我想知道在单次执行期间递归的级别是什么.

Now, without setting a local variable, i would like to know what recursion's level is during single execution.

我认为肯定有一个存储深度的会话变量(当您达到最大级别时，您怎么知道)但我找不到它.我会避免使用变量来逐步执行此操作.我怎么知道这个(如果有的话)系统变量?

I think that surely there is a session variable that stores the depth (how else would you know when you reach the maximum level) but I could not find it. I would avoid using a variable to do this incrementally. How could i know this (if any) system variable?

推荐答案

我知道你特别问了如何在没有用户创建的变量的情况下做到这一点 - 但对于其他遇到这个问题的人来说值得发布如何使用，因为它相当简单:

I know you specifically asked how to do this without a user-created variable - but for others coming across this thought it would be worth posting how to do it with one as it's fairly simple:

CREATE PROCEDURE sp_recursive
BEGIN
  // ... DECLAREs here

  -- Set maximum recursion depth (max is 255)
  SET @@SESSION.max_sp_recursion_depth = 10;

  -- Increment current recursion depth
  SET @recursion_depth = IFNULL(@recursion_depth + 1, 0);

  -- ... More stored procedure code

  -- Decrement current recursion depth. Note: Care must be taken to ensure this line
  -- is *always* executed at the end of the stored procedure.
  SET @recursion_depth = @recursion_depth - 1;
END

说明

每次进入存储过程时，@recursion_depth 会话范围的变量都会通过上述 SET 语句递增.第一次输入时，变量未初始化，因此具有 NULL 值 - 这由 IFNULL() 检查，在此将其重新分配为零案件.在即将退出的存储过程结束时，深度需要递减.

The @recursion_depth session-scoped variable is incremented by the above SET statement each time the stored procedure is entered. The first time it is entered, the variable is uninitialized and so has a value of NULL - this is checked for by the IFNULL(), which reassigns it to zero in this case. At the end of the stored procedure just before exiting, the depth needs to be decremented.

补充说明

值得注意的是，SQL Server 确实提供了一个内置的 @@NESTLEVEL 变量来执行上述操作 - 但不幸的是 MySQL 似乎没有等效的变量.

Worth noting that SQL Server does provide an in-built @@NESTLEVEL variable for doing the above - but unfortunately MySQL doesn't seem to have an equivalent.

这篇关于如何获得mysql存储过程递归的深度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！