mysql存储过程仅在我有一行时才有效

Question

我创建了一个存储过程，以便从不同的表中删除多行。基本上有一个主行，所有其他行都通过外键连接。当我在有一个主行时调用该过程时，一切正常，并且仅主行其他行都消失了。但是当有多于一行时，存储过程不起作用。(没有外键交叉，这意味着在某些地方不能有两个主行具有相同的 FK)。如果我删除所有回滚，我会得到“无法删除或更新父行:外键约束失败”，女巫很奇怪，因为当我直接以相同的顺序执行相同的删除语句(没有存储过程)时，即使使用多个主行。任何想法？？这是我的存储过程

    -- --------------------------------------------------------------------------------
    -- Routine DDL
    -- Note: comments before and after the routine body will not be stored by the server
    -- --------------------------------------------------------------------------------
    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `Tik_Del`(IN dell_id INT(11))
    BEGIN
    DECLARE hatan_id INT(11) DEFAULT -1;
    DECLARE cala_id INT(11) DEFAULT -1;
    DECLARE tashlum_id INT(11) DEFAULT -1;
    DECLARE teauda_id INT(11) DEFAULT -1;
    DECLARE haderacha_id INT(11) DEFAULT -1;
    DECLARE rav_mekadesh_id INT(11) DEFAULT -1;
    DECLARE pirtei_hatuna_id INT(11) DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
    START TRANSACTION;

    SET SQL_SAFE_UPDATES=0;
    SELECT `tikim`.`hatan_id`,
        `tikim`.`cala_id`,
        `tikim`.`taslum_id`,
        `tikim`.`teauda_id`,
        `tikim`.`hadracha_id`,
        `tikim`.`rav_mekadesh_id`,
        `tikim`.`pirtei_hatuna_id`
    INTO hatan_id,cala_id,tashlum_id,teauda_id,haderacha_id,rav_mekadesh_id,pirtei_hatuna_id
    FROM `ad120`.`tikim` WHERE `tik_id`=dell_id;

    DELETE FROM `ad120`.`tikim` WHERE `tik_id`=dell_id;

    DELETE FROM `ad120`.`haderachot` WHERE `haderacha_id`=haderacha_id;

    DELETE FROM `ad120`.`tashlumim` WHERE `tashlum_id`=tashlum_id;

    DELETE FROM `ad120`.`rav_mekadesh` WHERE `rav_mekadesh_id`=rav_mekadesh_id;

    DELETE FROM `ad120`.`pirtei_hatuna` WHERE `pirtei_hatuna_id`=pirtei_hatuna_id;

    Call Aish_Del(hatan_id);
    Call Aish_Del(cala_id);

    Call Teauda_Del(teauda_id);


    COMMIT;
    END

Answer 1

您是否考虑过进行多表删除？

DELETE k, h, t, r, p 
FROM `ad120`.`tikim` AS k
LEFT OUTER JOIN `ad120`.`haderachot` AS h USING (`hadracha_id`)
LEFT OUTER JOIN `ad120`.`tashlumim` AS t USING (`tashlum_id`)
LEFT OUTER JOIN `ad120`.`rav_mekadesh` AS r USING (`rav_mekadesh_id`)
LEFT OUTER JOIN `ad120`.`pirtei_hatuna` AS p USING (`pirtei_hatuna_id`)
WHERE k.`tik_id` = dell_id;

另一种替代方法是使用ON DELETE CASCADE声明所有外键。然后，您可以从 tikim 中删除并让其余的级联(我假设 tikim 是所有其他表引用的父表)。