MySQL While循环条件从未被满足?

Question

我第一次尝试创建一个过程，根据特定条件将不同的列值从一个表插入到另一个表。我相信我的while循环没有在这里运行:

DELIMITER $$
CREATE PROCEDURE Interpolate(startingRow INT, startingSecond INT, finalYear INT, finalMonth INT, finalDay INT)
BEGIN
DECLARE iterSecond INT DEFAULT 0; #the second we'll use to see if we have prev second below it and curr second after it
DECLARE currMonth INT DEFAULT 0; #these three after help determine when to stop
DECLARE currDay INT DEFAULT 0;
DECLARE currYear INT DEFAULT 0;
DECLARE currSecond INT DEFAULT 0;
DECLARE currBx DOUBLE DEFAULT 0;
DECLARE currBy DOUBLE DEFAULT 0;
DECLARE currBz DOUBLE DEFAULT 0;
DECLARE prevSecond INT DEFAULT 0; #set it to starting second b.c on first time don't want to insert
DECLARE prevBx DOUBLE DEFAULT 0;
DECLARE prevBy DOUBLE DEFAULT 0;
DECLARE prevBz DOUBLE DEFAULT 0;
DECLARE intBx DOUBLE DEFAULT 0;
DECLARE intBy DOUBLE DEFAULT 0;
DECLARE intBz DOUBLE DEFAULT 0;
DECLARE currRowNum INT DEFAULT 0;
SET currRowNum = startingRow;
SET iterSecond = startingSecond;
WHILE (currYear <> finalYear AND currMonth <> finalMonth AND currDay <> finalDay) DO
    SET currSecond = (SELECT seconds FROM apollo12 WHERE row_num = currRowNum);
    SET currYear = (SELECT year FROM apollo12 WHERE row_num = currRowNum);
    SET currMonth = (SELECT month FROM apollo12 WHERE row_num = currRowNum);
    SET currDay = (SELECT day FROM apollo12 WHERE row_num = currRowNum);
    SET currBx = (SELECT b_x FROM apollo12 WHERE row_num = currRowNum);
    SET currBy = (SELECT b_y FROM apollo12 WHERE row_num = currRowNum);
    SET currBz = (SELECT b_z FROM apollo12 WHERE row_num = currRowNum);
    IF prevSecond < iterSecond AND currSecond > iterSecond THEN
        SET intBx = (prevBx + (iterSecond - prevSecond)*(currBx - prevBx)/(currSecond - prevSecond));
        SET intBy = (prevBy + (iterSecond - prevSecond)*(currBy - prevBy)/(currSecond - prevSecond));
        SET intBz = (prevBz + (iterSecond - prevSecond)*(currBz - prevBz)/(currSecond - prevSecond));
        INSERT INTO apollo12int(year, month, day, hour, minute, seconds, b_x, b_y, b_z) SELECT year, month, day, hour, minute, iterSecond, intBx, intBy, intBz FROM apollo12 WHERE row_num = currRowNum;
        SET iterSecond = (iterSecond + 1);
    END IF;
    IF currSecond = iterSecond THEN
        INSERT INTO apollo12int(year, month, day, hour, minute, seconds, b_x, b_y, b_z) SELECT year, month, day, hour, minute, seconds, b_x, b_y, b_z FROM apollo12 WHERE row_num = currRowNum;
        SET iterSecond = (iterSecond + 1);
    END IF;
    SET prevSecond = currSecond;
    SET prevBx = currBx;
    SET prevBy = currBy;
    SET prevBz = currBz;
    SET currRowNum = currRowNum + 1;
END WHILE;
END$$
DELIMITER ;

，当我写CALL Interpolate(1,47, 1969, 11,20)时，这个过程不起作用。任何想法吗?

Answer 1

只有当三个条件都为真时，while才会停止

在你的例子中，1969-11-20

从开始行开始你就没有在任何一行中出现过，你永远不会得到一个结束

您可以先检查这样的行是否存在，或者您可以找到另一个条件来执行while循环