MySQL 如何从表sql中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11709139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete rows from table sql
提问by Ofer
I want to delete specific rows from 8 tables. My problem is that the rows are connected with foreign key. How can I delete all the data that connected to the specific rows that I want to delete? My tables include definition tables (like id, name ,max value, min value...), data tables (like id, user_id, definition_id,....) and history tables (save every change in data table).
我想从 8 个表中删除特定行。我的问题是行与外键连接。如何删除连接到要删除的特定行的所有数据？我的表包括定义表（如 id、名称、最大值、最小值...）、数据表（如 id、user_id、definition_id....）和历史表（保存数据表中的每个更改）。
I thought to use delete on cascade command but I could not find a way to use it.
我想在级联命令上使用删除，但我找不到使用它的方法。
回答by a_horse_with_no_name
DELETE CASCADEis an attribute of the foreign key constraint. Unfortunately it's not something you can use as an option with a DELETEstatement (which would be really cool actually)
DELETE CASCADE是外键约束的一个属性。不幸的是，这不是您可以用作DELETE声明选项的东西（实际上这真的很酷）
If your foreign keys have not been declared as cascading you need to "work your way up".
如果您的外键没有被声明为级联，您需要“按照自己的方式工作”。
Unfortunately you did not show us your real table structure so let's assume something like this:
不幸的是，您没有向我们展示您的真实表结构，所以让我们假设如下：
main_table (main_id)
child_one (id, main_id)
child_two (id, id_one)
child_three (id, id_two)
(I know you said 8 tables, but for the sake of the demonstration I shortened it a bit, but that doesn't change the underlying "strategy")
（我知道您说的是 8 个表，但为了演示起见，我将其缩短了一点，但这并没有改变底层的“策略”）
Assuming you want to delete the row with main_id = 42from `main_table:
假设您想main_id = 42从 `main_table 中删除该行：
You first need to delete the rows from child_three using something like this:
您首先需要使用以下内容从 child_three 中删除行：
delete from child_three
where id_two in (select id
from child_two
where id_one in (select id
from child_one
where main_id = 42);
Then delete the rows from child_two:
然后从 child_two 中删除行：
delete from child_two
where id_one in (select id
from child_one
where main_id = 42);
Then child_one:
然后child_one：
delete from child_one
where main_id = 42;
And finally the main table:
最后是主表：
delete from main_table
where id = 42;
Some SQL clients can actually generate those statements for you. I don't know if SQL Developer can though.
一些 SQL 客户端实际上可以为您生成这些语句。我不知道 SQL Developer 是否可以。
回答by Michael
I assume that you use InnoDB Engine, since you are talking about foreign keys,
我假设您使用 InnoDB 引擎，因为您在谈论外键，
Easier will be to define properly the table so that a deletion will act as a cascade deletion.
更容易正确定义表，以便删除将作为级联删除。
CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Here is alink with a proper create table statement:
这是带有正确创建表语句的链接：
