无ON DELETE CASCADE时如何基于查询结果批量删除多表对应记录

错误原因 #

原有存储过程的核心问题是：SET @IDs = SELECT ID FROM TABLE_ID WHERE Status=_status; 这条语句试图把多行查询结果赋值给单个标量变量@IDs，MySQL的标量变量只能存储单个值，最终只会保留查询结果的第一行ID，后续的IN (@IDs)条件自然无法匹配到所有需要删除的记录。

可行实现方案 #

方案1：直接嵌套子查询（最简单，适合小数据量场景）

不需要额外存储ID，直接在每个DELETE语句的IN条件里嵌入ID查询逻辑即可：

CREATE PROCEDURE `Delete_card`(IN _status INT)
BEGIN
    DELETE FROM TABLE_A WHERE ID IN (SELECT ID FROM TABLE_ID WHERE Status=_status);
    DELETE FROM TABLE_B WHERE ID IN (SELECT ID FROM TABLE_ID WHERE Status=_status);
    DELETE FROM TABLE_C WHERE ID IN (SELECT ID FROM TABLE_ID WHERE Status=_status);
    DELETE FROM TABLE_D WHERE ID IN (SELECT ID FROM TABLE_ID WHERE Status=_status);
    DELETE FROM TABLE_E WHERE ID IN (SELECT ID FROM TABLE_ID WHERE Status=_status);
    -- 若需要同步删除TABLE_ID中的源记录，可追加下面的语句
    -- DELETE FROM TABLE_ID WHERE Status=_status;
END

方案2：临时表存储ID（保证各表删除的ID集合一致）

如果担心执行多轮DELETE的过程中TABLE_ID的数据发生变更，导致不同表删除的ID不一致，可以用临时表先固化需要删除的ID集合：

CREATE PROCEDURE `Delete_card`(IN _status INT)
BEGIN
    -- 创建临时表存储待删除ID，会话结束后会自动销毁
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_delete_ids (
        id INT PRIMARY KEY
    );
    -- 清空临时表避免残留历史数据
    TRUNCATE TABLE temp_delete_ids;
    -- 写入符合条件的ID集合
    INSERT INTO temp_delete_ids(id) SELECT ID FROM TABLE_ID WHERE Status=_status;
    
    -- 基于临时表执行批量删除
    DELETE FROM TABLE_A WHERE ID IN (SELECT id FROM temp_delete_ids);
    DELETE FROM TABLE_B WHERE ID IN (SELECT id FROM temp_delete_ids);
    DELETE FROM TABLE_C WHERE ID IN (SELECT id FROM temp_delete_ids);
    DELETE FROM TABLE_D WHERE ID IN (SELECT id FROM temp_delete_ids);
    DELETE FROM TABLE_E WHERE ID IN (SELECT id FROM temp_delete_ids);
END

方案3：多表关联DELETE（执行效率最高）

MySQL支持多表关联删除语法，可以一次性完成所有关联表的删除操作，不需要多次查询ID：

CREATE PROCEDURE `Delete_card`(IN _status INT)
BEGIN
    DELETE a, b, c, d, e 
    FROM TABLE_ID t
    LEFT JOIN TABLE_A a ON t.ID = a.ID
    LEFT JOIN TABLE_B b ON t.ID = b.ID
    LEFT JOIN TABLE_C c ON t.ID = c.ID
    LEFT JOIN TABLE_D d ON t.ID = d.ID
    LEFT JOIN TABLE_E e ON t.ID = e.ID
    WHERE t.Status = _status;
END

内容的提问来源于stack exchange，提问作者user14079292