mysql函数循环语句吗_如何在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

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.

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.

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);

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).

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

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

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.

Solved 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 ;

解决方案

From mysql :

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

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)