mysql - 根据查询结果删除多个表

Question

我们的数据库没有实现 ON DELETE CASCADE所以我需要创建一个脚本或一个存储过程来删除多个表中的记录给定 QUery 中 Ids 的结果:
我在存储过程中尝试过，但无法在变量中分配多行:

CREATE PROCEDURE `Delete_card`(IN _status INT)
BEGIN

    SET @IDs = SELECT ID FROM TABLE_ID WHERE Status=_status;
    
    DELETE FROM TABLE_A WHERE ID IN (@IDs);
    DELETE FROM TABLE_B WHERE ID IN (@IDs);
    DELETE FROM TABLE_C WHERE ID IN (@IDs);
    DELETE FROM TABLE_D WHERE ID IN (@IDs);
    DELETE FROM TABLE_E WHERE ID IN (@IDs);
END

Answer 1

您可以使用多表 DELETE 语句来实现:

DELETE a, b, c
FROM TABLE_ID t
LEFT JOIN TABLE_A a ON a.id = t.id
LEFT JOIN TABLE_B b ON b.id = t.id
LEFT JOIN TABLE_C c ON c.id = t.id
..................................
WHERE t.Status = ?;

更改 ?带有您想要的状态值。
查看简化版 demo .