在MySQL中，级联删除类似ON DELETE CASCADE的一次操作(Cascading deletes like ON DELETE CASCADE for a one time operati

在MySQL中，级联删除类似ON DELETE CASCADE的一次操作(Cascading deletes like ON DELETE CASCADE for a one time operation in MySQL)

是否有某种神奇的SQL语句来删除行及其所有依赖项（由外键约束链接） 而不更改表以添加ON DELETE CASCADE或手动删除每个相关行？

我正在幻想像DELETE FROM `table_a` WHERE `id` = 1 ON DELETE CASCADE; 但我似乎无法在doc @ http://dev.mysql.com/doc/refman/5.5/en/delete.html找到任何有效的方法。

我不希望ALTER表只更改一次操作的约束，然后使用另一个ALTER将其还原 我不想执行DELETE FROM `table_b` WHERE `a_id` = 1; 对于包含FK到table_a每个表

使用MySQL 5.5和InnoDB

Is there some sort of magical SQL statement to delete a row and all its dependents (linked by foreign key constraints) WITHOUT altering the table to add ON DELETE CASCADE or deleting each dependent row manually?

I am fantasizing something such as DELETE FROM `table_a` WHERE `id` = 1 ON DELETE CASCADE; but I can't seem to find anything to this effect in the doc @ http://dev.mysql.com/doc/refman/5.5/en/delete.html

I don't want to ALTER the table to change the constraints for just a one time operation and then revert it back using another ALTER I don't want to execute something like DELETE FROM `table_b` WHERE `a_id` = 1; for each table containing a FK to table_a

Using MySQL 5.5 with InnoDB

最满意答案

不，简单的答案是，不，没有捷径。

您可以记下DELETE语句以删除相关表中的所有相关行，或者使用ON DELETE CASCADE定义外键约束。

请注意 - 只要外键关系中没有循环路径 - 就可以使用从多个表中删除的单个DELETE语句：

DELETE a, b, c, d FROM a LEFT JOIN b ON b.a_id = a.a_id LEFT JOIN c ON c.a_id = a.a_id LEFT JOIN d ON d.b_id = b.b_id WHERE a.a_id = 1 ;

No, the simple answer is, no, there is no shortcut.

You either write down DELETE statements to delete all the related rows in the related tables or you have defined foreign key constraints with ON DELETE CASCADE.

Note that - as long as there are no circular paths in the foreign key relationships - it is possible to use a single DELETE statement that deletes from multiple tables:

DELETE a, b, c, d FROM a LEFT JOIN b ON b.a_id = a.a_id LEFT JOIN c ON c.a_id = a.a_id LEFT JOIN d ON d.b_id = b.b_id WHERE a.a_id = 1 ;