如何在MySQL中正确循环存储函数? [英] How to properly loop in a stored function on MySQL?

问题描述

我很难正确地完成一个非常简单的存储过程. 请考虑以下文章表格摘要:

I am having some difficulty getting a pretty simple stored procedure right. Consider the following article table snippet:

id    replaced_by     baseID
 1              2          0
 2              3          0
 3              0          0

使用写时复制的简单层次表.编辑文章时，将当前文章的replace_by字段设置为其新副本的ID.

A simple hierarchical table, using copy-on-write. When an article is edited, the replaced_by field of the current article is set to the id of it's new copy.

我添加了一个baseID字段，将来该字段应存储文章的baseID. 在上面的示例中，有一篇文章(例如id 3).它的baseID为1.

I've added a baseID field, which in the future should store the baseID of an article. In my example above, there is one article (eg id 3). It's baseID would be 1.

要获取baseID，我创建了以下存储过程:

To get the baseID, I have created the following stored procedure:

DELIMITER $$

CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    SET x = articleID;
    sloop:LOOP
        SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
        IF y IS NOT NULL THEN
            SET x = y;
            ITERATE sloop;
        ELSE
            LEAVE sloop;
        END IF;  
    END LOOP;
    RETURN x;
END $$

DELIMITER ;

看起来很简单，直到我实际使用以下函数调用该函数为止:

It seems simple enough, until I actually call the function using:

SELECT getBaseID(3);

我希望函数返回1.我什至愿意理解它可能花费一秒钟的时间. 取而代之的是，计算机的CPU高达100％(mysqld).

I would expect, the function to return 1. I'm even willing to understand it can take a slice of a second. Instead, the machine's CPU goes up to 100% (mysqld).

我什至使用REPEAT .. UNTIL和WHILE .. DO重写了相同的函数，最终结果相同.

I have even rewritten the same function using REPEAT .. UNTIL and with WHILE .. DO, with the same end result.

谁能解释为什么我的CPU进入循环时会上升100％?

Can anyone explain why my CPU goes up 100% when it enters the loop?

旁注:我只是想赢得时间.我已经在PHP中创建了完全相同的函数，该函数执行正常，但是我们猜测MySQL可以更快地完成它.我们需要筛选大约1800万条记录.我可以节省的任何时间都是值得的.

Side note: I am trying to simply win time. I have created the exact same function in PHP, which performs okay, but our guess is that MySQL can do it slightly faster. We need to sift through about 18 million records. Any bit of time I can save is going to be worth it.

在此先感谢您的帮助和/或指示.

Thanks in advance for any assistance and/or pointers.

已解决的SQL:

DELIMITER $$

CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    SET x = articleID;
    sloop:LOOP
        SET y = NULL;
        SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
        IF y IS NULL THEN
            LEAVE sloop;
        END IF;  
        SET x = y;
        ITERATE sloop;
    END LOOP;
    RETURN x;
END $$

DELIMITER ;

推荐答案

来自 mysql :

如果查询不返回任何行，则会出现错误代码1329的警告(无数据)，并且变量值保持不变

If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged

因此，在没有找到给定x的记录的情况下(y保持不变)，您将陷入无限循环 尝试使用SET y = (SELECT id ....)代替，或在您的select语句之前添加SET y = null(它应该是循环中的第一个语句)

So you have an infinite loop when no records found with a given x (y remains unchanged) Try SET y = (SELECT id ....) instead or add SET y = null before your select statement (it should be the first statement in the loop)

这篇关于如何在MySQL中正确循环存储函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！