调用自身递归的MySQL存储过程

Question

我有下面这张表格：

id | parent_id | quantity
-------------------------
1  | null      | 5
2  | null      | 3
3  | 2         | 10
4  | 2         | 15
5  | 3         | 2
6  | 5         | 4
7  | 1         | 9

现在我需要一个在mysql中调用自身并递归返回计算数量的存储过程。例如，id 6 有5作为父级，3作为父级，2作为父级。因此，我需要计算4 * 2 * 10 * 3（= 240）作为结果。我对存储过程还比较新，将来也不会经常使用它们，因为我更喜欢将业务逻辑放在程序代码中而不是数据库中。但在这种情况下，我无法避免使用它。也许mysql大师（就是你）可以在几秒钟内编写出一个可行的语句。

- Jürgen Steinblock

---

这个问题似乎旨在寻求类似的解决方案：https://dev59.com/E0jSa4cB1Zd3GeqPEEt5。基本上，在mysql中这很棘手！ - Fiona - myaccessible.website

存储过程中允许递归，但默认情况下被禁用。要启用递归，请将max_sp_recursion_depth服务器系统变量设置为大于零的值。 - Haim Evgi

“WITH RECURSIVE TABLE() AS”语句在MySQL中无法识别？ - Novemberland

Answer 1

它只在mysql版本>= 5中工作

存储过程声明是这样的，

你可以稍微改进一下，但是这个是起作用的:

DELIMITER $$

CREATE PROCEDURE calctotal(
   IN number INT,
   OUT total INT
)

BEGIN

   DECLARE parent_ID INT DEFAULT NULL ;
   DECLARE tmptotal INT DEFAULT 0;
   DECLARE tmptotal2 INT DEFAULT 0;

   SELECT parentid   FROM test   WHERE id = number INTO parent_ID;   
   SELECT quantity   FROM test   WHERE id = number INTO tmptotal;     

   IF parent_ID IS NULL
    THEN
    SET total = tmptotal;
   ELSE     
    CALL calctotal(parent_ID, tmptotal2);
    SET total = tmptotal2 * tmptotal;   
   END IF;

END$$

DELIMITER ;

调用方式应该是这样的（设置这个变量很重要）：

SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255; 

CALL calctotal(6, @total);
SELECT @total;

- Haim Evgi

---

看起来不错。我像一个函数一样实现了它，但我总是得到错误信息“不允许递归存储函数和触发器”，但我的Addison-Wesley《MySQL 5》书声称递归对于函数也可以工作。有什么最终想法吗？ - Jürgen Steinblock

存储函数不能递归。该内容来自官方网站：http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html。 - Haim Evgi

这意味着来自mysql团队，但我想他们有禁止此操作的理由。我希望我可以这样做：SELECT id, computed_quantity(id) FROM table。不管怎样，在您的帮助下，我能够将其作为递归过程完成，但这需要我编写一些额外的c#客户端代码以获得所需的结果。 - Jürgen Steinblock

Answer 2

请看Mike Hillyer的MySQL中管理分层数据，其中包含处理分层数据的完整示例。

- bradgonesurfing

---

+1 for the link, 非常有趣。我知道如何使用自连接获取所需的数据，但我想将其作为存储过程（函数）使用，因为我需要在一个查询中多次使用，如果我一遍又一遍地重用我的代码，这将极大地降低可读性。 - Jürgen Steinblock

你需要继续阅读，因为这篇文章并不是关于自连接，而是关于嵌套集，这是完全不同的东西。 - bradgonesurfing

Answer 3

如何避免使用过程？

SELECT quantity from (
 SELECT @rq:=parent_id as id, @val:=@val*quantity as quantity from (
  select * from testTable order by -id limit 1000000 # 'limit' is required for MariaDB if we want to sort rows in subquery
 ) t # we have to inverse ids first in order to get this working...
 join
 ( select @rq:= 6 /* example query */, @val:= 1 /* we are going to multiply values */) tmp
 where id=@rq
) c where id is null;

来试试 Fiddle!

注意！如果 “parent_id > id”，这个方法将无法正常工作。

干杯！

- Victor.Palyvoda

Answer 4

DELIMITER $$
CREATE DEFINER=`arun`@`%` PROCEDURE `recursivesubtree`( in iroot int(100) , in ilevel int(110) , in locid int(101) )
BEGIN
  DECLARE irows,ichildid,iparentid,ichildcount,done INT DEFAULT 0;

  DECLARE cname VARCHAR(64);
  SET irows = ( SELECT COUNT(*) FROM account WHERE parent_id=iroot and location_id=locid );
  IF ilevel = 0 THEN
    DROP TEMPORARY TABLE IF EXISTS _descendants;
    CREATE TEMPORARY TABLE _descendants (
      childID INT, parentID INT, name VARCHAR(64), childcount INT, level INT
  );
  END IF;
  IF irows > 0 THEN
    BEGIN
      DECLARE cur CURSOR FOR
        SELECT
          f.account_id,f.parent_id,f.account_name,
          (SELECT COUNT(*) FROM account WHERE parent_id=t.account_id and location_id=locid ) AS childcount
        FROM account t JOIN account f ON t.account_id=f.account_id
        WHERE t.parent_id=iroot and t.location_id=locid 
        ORDER BY childcount<>0,t.account_id;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
      OPEN cur;
      WHILE NOT done DO
        FETCH cur INTO ichildid,iparentid,cname,ichildcount;
        IF NOT done THEN
          INSERT INTO _descendants VALUES(ichildid,iparentid,cname,ichildcount,ilevel );
          IF ichildcount > 0 THEN
            CALL recursivesubtree( ichildid, ilevel + 1 );
          END IF;
        END IF;
      END WHILE;
      CLOSE cur;
    END;
  END IF;

  IF ilevel = 0 THEN
    -- Show result table headed by name that corresponds to iroot:
    SET cname = (SELECT account_name FROM account WHERE account_id=iroot and location_id=locid );
    SET @sql = CONCAT('SELECT   CONCAT(REPEAT(CHAR(36),2*level),IF(childcount,UPPER(name),name))',
                  ' AS ', CHAR(39),cname,CHAR(39),' FROM _descendants');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
  END IF;
END$$
DELIMITER ;

- ArunKumar