mysql - WHILE循环与插入无法正常工作

我尝试查找给定父级的所有子级和子级子级，但是如果使用WHILE循环，则无法获得所需的输出。令人惊讶的是，如果我手动执行脚本，它会按预期工作。请看下面的脚本。

DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, parent_id INT);
INSERT INTO test VALUES (1,NULL);
INSERT INTO test VALUES (2,1);
INSERT INTO test VALUES (3,2);
INSERT INTO test VALUES (4,2);
INSERT INTO test VALUES (5,3);
INSERT INTO test VALUES (6,4);
INSERT INTO test VALUES (7,2);
INSERT INTO test VALUES (8,4);
INSERT INTO test VALUES (9,1);
INSERT INTO test VALUES (10,1);
COMMIT;

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `get_parent_child_hierarchy`$$

CREATE PROCEDURE `get_parent_child_hierarchy`(Parent_ID INT)
BEGIN

DECLARE `rowcount` INT DEFAULT 1;

DROP TABLE IF EXISTS Temp;

CREATE TABLE Temp(ParentID INT, ChildID INT, Stage INT);


INSERT INTO Temp(ParentID, ChildID, Stage)
SELECT Parent_ID, Parent_ID, 2;

WHILE rowcount > 0 DO

    UPDATE Temp
    SET Stage = 1
    WHERE Stage = 2;
    COMMIT;

    INSERT INTO Temp(ParentID, ChildID, Stage)
    SELECT Parent_ID, id, 2
    FROM Test T
    WHERE Parent_id IN (SELECT ChildID
                        FROM Temp
                        WHERE Stage = 1
                        AND Temp.ChildID <> T.id);


    UPDATE Temp
    SET Stage = 0
    WHERE Stage = 1;
    COMMIT;

    SET rowcount = (SELECT COUNT(ChildID)
                    FROM Temp
                    WHERE Stage = 2);

END WHILE;


SELECT *
FROM Temp
WHERE Parent_ID <> ChildID;

DROP TABLE IF EXISTS Temp;

END$$

DELIMITER ;

输入2

预期产量：

ParentID | ChildID | Stage
---------|---------|------
    2    |    3    |   0
    2    |    4    |   0
    2    |    5    |   0
    2    |    6    |   0
    2    |    7    |   0
    2    |    8    |   0

但是不幸的是，当使用WHILE循环运行脚本时，我得到了输入中的所有行。

任何帮助表示赞赏。

我同意这是特定的问题，而不是技术问题。但是我想知道当使用WHILE循环和INSERT时MySQL是否存在任何问题。

最佳答案

您将输入参数的名称定义为Parent_ID，这是表test中的一列，这是您未获得期望值的原因。

尝试跟随；）

CREATE PROCEDURE `get_parent_child_hierarchy`(pid INT)
BEGIN

DECLARE `rowcount` INT DEFAULT 1;

DROP TABLE IF EXISTS Temp;

CREATE TABLE Temp(ParentID INT, ChildID INT, Stage INT);


INSERT INTO Temp(ParentID, ChildID, Stage)
SELECT pid, pid, 2;

WHILE rowcount > 0 DO

    UPDATE Temp
    SET Stage = 1
    WHERE Stage = 2;
    COMMIT;

    INSERT INTO Temp(ParentID, ChildID, Stage)
    SELECT pid, id, 2
    FROM test T
    WHERE parent_id IN (SELECT ChildID
                        FROM Temp
                        WHERE Stage = 1
                        AND Temp.ChildID <> T.id);


    UPDATE Temp
    SET Stage = 0
    WHERE Stage = 1;
    COMMIT;

    SET rowcount = (SELECT COUNT(ChildID)
                    FROM Temp
                    WHERE Stage = 2);

END WHILE;


SELECT *
FROM Temp
WHERE pid <> ChildID;

DROP TABLE IF EXISTS Temp;

END

关于mysql - WHILE循环与插入无法正常工作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37850130/