MySQL : Cascade update on same table

Question

我有一个 MySQL 表，其中每条记录都引用其parent_id:

| id | Summary | parent_id | hidden |

-------------------------------------

|  1 | First   |      NULL |      0 |

|  2 | Hello   |         1 |      0 |

|  3 | john    |         1 |      0 |

|  4 | Second  |      NULL |      0 |

|  5 | World   |         2 |      0 |

|  6 | Doe     |         4 |      0 |

我想级联更新，这样如果第 1 行被隐藏，它的子行(第 2 行)及其子行的子行(第 5 行)也被隐藏。

用 MySQL 可以吗？

我已经有一个可以正常工作的DELETE ON CASCADE 约束。

CONSTRAINT FK_ID_With_CascadeDelete FOREIGN KEY (parent_id) REFERENCES MyTable (id) ON DELETE CASCADE

Answer 1

您可以按如下方式使用存储过程。

DELIMITER $$

DROP PROCEDURE IF EXISTS `update_node`$$

CREATE PROCEDURE `update_node`(IN p_id INT) 

 proc: BEGIN

    DECLARE e_no_id CONDITION FOR SQLSTATE '45000';

    IF ( p_id IS NULL ) THEN
            SIGNAL e_no_id SET MESSAGE_TEXT = 'The id cannot be empty.';
            LEAVE proc;
        END IF;

    DROP TEMPORARY TABLE IF EXISTS del_temp_table;      

    CREATE TEMPORARY TABLE IF NOT EXISTS del_temp_table(
     id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     delids INTEGER UNSIGNED NOT NULL,
     node INTEGER DEFAULT 0
    );

    SET @rept := @loopcount := 1;   
    INSERT INTO del_temp_table (delids, node) SELECT p_id, @rept;

    myloop: WHILE (1 = 1)
    DO      
        SELECT COUNT(id) AS cnt FROM test WHERE `parent_id` IN ( SELECT DISTINCT(delids) FROM del_temp_table WHERE node = @rept ) INTO @loopcount;  

        IF (@loopcount = 0) THEN
           LEAVE myloop;
        ELSE
           SET @rept := @rept + 1;
           SELECT GROUP_CONCAT(d1.delids) INTO @wherein FROM ( SELECT DISTINCT(delids) FROM del_temp_table WHERE node = (@rept - 1) ) AS d1;
           INSERT INTO del_temp_table (delids, node) SELECT id, @rept FROM test WHERE FIND_IN_SET( parent_id, @wherein ) > 0;
        END IF ;

    END WHILE myloop;   

    UPDATE test SET hidden = 1 WHERE id IN ( SELECT delids FROM del_temp_table );       

    END$$

DELIMITER ;

这里我使用临时表来存储递归节点，并每次使用该表来获取下一个子节点并将其插入到同一个表中进行迭代。

我相信这可以帮助你。 将表名称 test 替换为您的表名称