mysql - 如何在函数中循环 MYSQL 查询/也许？

Question

到目前为止，我得到的是一个根据 IF 条件更新一行的代码 - 但它重要的是它是可靠的和原子的(事务也是如此)。

它应该基于@q(数量)循环/重复自身 - 但它可以在 MYSQL 函数中而不是在一个固定变量中。我怎样才能做到这一点？我添加了表格最终的样子

SET @q=1000;
SET @p=5.00;
SET @email='test@test.com';

update 1detail 
SET quantity = 
if((@q := quantity - @q) >= 0, @q, 0)
WHERE price>=@p ORDER BY datetime DESC LIMIT 1;
SET @q = if(@q <0,@q-@q-@q, @q);

UPDATE 1detail 
SET quantity =
if(@q > 0, @q,quantity),
email=
if(@q > 0, @email, email)
WHERE price>=@p ORDER BY datetime DESC LIMIT 1;

开头的表格

quantity    price   email            datetime
---------------------------------------------------
800          5.00   test1@test.com    oldest   
50           5.00   test2@test.com  2nd oldest
100         10.00   test3@test.com  3rd oldest    (ignore in processing because @p < price)

循环后的样子

quantity    price   email                                 datetime
-----------------------------------------------------------------
0            5.00    test1@test.com                        oldest           
150*         5.00    test@test.com (changed to @email)     2nd oldest  (now newest)
100         10.00    test3@test.com                        3rd oldest    (ignored)

*情况已经改变，因为最年长的只有 800 个，第二个最年长的只有 50 个所以@q= 1000 (@q) - 800(最旧的)= 200
那么@q = 200 (@q) - 50(第二旧)

--> @q = 150这会更新第二个最旧的行

Answer 1

一个小循环可以这样实现:

declare @counter int
set @counter=0

gohere:

set @counter=@counter+1
if @counter<10
    BEGIN
        select @counter
        GOTO gohere;
    END