需要删除子节点基于父节点ID使用mysql存储过程

Question

问题:

我需要删除基于父节点ID的子节点。所以请帮帮我。

注：

下面是我根据父节点id选择子节点的存储过程。

CREATE DEFINER=`root`@`localhost` PROCEDURE `Hierarchy`(IN `GivenID` INT, IN `initial` INT)
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;
    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT GID FROM civicsoft_group WHERE Parent_Group_ID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (TGID int,TParent_Group_ID int, TGroup_Name varchar(32),TGroup_Type varchar(1),TProject_Status tinyint(1),TEID int,TEmp_Group_ID int,TEmp_Name varchar(100),TEmp_Type varchar(2));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT cg.GID,cg.Parent_Group_ID,cg.Group_Name,cg.Group_Type,cg.Project_Status,ce.EID,ce.Emp_Group_ID,ce.Emp_Name,ce.Emp_Type FROM civicsoft_group cg LEFT JOIN civicsoft_employee ce ON cg.GID = ce.Emp_Group_ID  WHERE cg.GID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --

    SET @@GLOBAL.max_sp_recursion_depth = 255;
    SET @@session.max_sp_recursion_depth = 255; 

        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN
SELECT * FROM OUT_TEMP;
        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END

Answer 1

最简单的方法是，向引用父表的子表添加外键。当父级和子级在同一个表中时，这也起作用。在此外键中启用删除级联时的选项。然后只需删除父条目，MySQL也会为您删除子条目。