MySQL创建一个循环自身的存储过程

Question

我想创建一个mysql函数，它可以使用表category中的列related找到所有相关的祖先，然后使用所有这些祖先( child ，孙子...... .) id 包括其自身，以使用 category 列在 listing_category 中查找这些 id 的所有实例。

类别

ID, Related
1,0
2,1
3,1
4,1
5,0
6,5
7,1
8,7
9,7
10,1

如果我选择 1，则 2,3,4,7,10 是它的子代，8,9 是它的孙子。

列表类别

Category
1
1
2
3
3
5
6
9
7
7

现在我想创建一个 MySql 函数，它可以计算另一个名为 listing_category 的表中 1,2,3,4,7,10,8,9 的所有实例

   create function listing_count(ID int(11)) returns int(11)
   begin 
    declare count int(11);
    set count=(select count(*) from listing_category where category=ID);
    while (select id from category where related=ID) as childID and count<100 do
     set count=count+listing_count(childID);
    end while;
    return count; 
   end

因此 listing_count(1) 将找到 category 内的所有亲戚 2,3,4,7,10,8,9，然后计算 1,2 的所有实例listing_category 内的,3,4,7,10,8,9。因此，在此示例中将返回 8 的计数。

可以用mysql存储过程吗？

Answer 1

您可以使用递归存储过程来完成此操作。这样做的优点是，无论祖先的深度如何，它都可以工作。 child 、孙子、曾孙等。

delimiter //
drop PROCEDURE if EXISTS listing_count //
create procedure listing_count(in parentID int(11), out thesum int(11))
begin 
  declare childID int(11);
  declare childSum int(11);
  declare finished int default 0;
  declare childID_cursor cursor for select id from category where related=parentID;
  declare continue handler for not found set finished = 1;
  select count(*) into thesum from listing_category where category=parentID;
  open childID_cursor;
  get_children: LOOP
    fetch childID_cursor into childID;
    if finished = 1 then 
      LEAVE get_children;
    end if;
    call listing_count(childID, childSum);
    set thesum = thesum + childSum;
  end loop get_children;
  close childID_cursor;
end
//

根据您的数据，此查询会产生预期结果 (8):

SET @@SESSION.max_sp_recursion_depth=25;
call listing_count(1, @x);
select @x;

如果你确实想要一个函数，你可以将过程包装在一个函数中(因为 MySQL 不允许你创建递归函数):

DELIMITER //
drop function if exists lc//
create function lc(id int(11)) RETURNS int(11)
BEGIN
  declare sum int(11);
  call listing_count(id, sum);
  return sum;
END
//
select lc(1)

输出:

8