代码之家 › 专栏 › 技术社区 › Jürgen Steinblock

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

recursion stored-procedures mysql

Jürgen Steinblock · 技术社区 · 14 年前

我有下表:

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专家(就是你)可以在几秒钟内破解一个工作语句。

4 回复 | 直到 10 年前

Haim Evgi 14 年前

它仅在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;

PaÅlo Ebermann 13 年前

看一看 Managing Hierarchical Data in MySQL 作者:迈克·希尔耶。

它包含了处理分层数据的完整示例。

Victor.Palyvoda 10 年前

如何避免程序:

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;

Check out Fiddle!

注意!如果世界其他地区 parent_id>id .

干杯!

Jürgen Steinblock 10 年前

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 ;