MySQL 从多个表中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/734567/
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
delete rows from multiple tables
提问by AdRock
I'm trying to use SQL to delete multiple rows from multiple tables that are joined together.
我正在尝试使用 SQL 从连接在一起的多个表中删除多行。
Table A is joined to Table B Table B is joined to Table C
表 A 连接到表 B 表 B 连接到表 C
I want to delete all rows in table B & C that correspond to a row in Table A
我想删除表 B 和 C 中与表 A 中的行相对应的所有行
CREATE TABLE `boards` (
`boardid` int(2) NOT NULL AUTO_INCREMENT,
`boardname` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`boardid`)
);
-- --------------------------------------------------------
--
-- Table structure for table `messages`
--
CREATE TABLE `messages` (
`messageid` int(6) NOT NULL AUTO_INCREMENT,
`boardid` int(2) NOT NULL DEFAULT '0',
`topicid` int(4) NOT NULL DEFAULT '0',
`message` text NOT NULL,
`author` varchar(255) NOT NULL DEFAULT '',
`date` datetime DEFAULT NULL,
PRIMARY KEY (`messageid`)
);
-- --------------------------------------------------------
--
-- Table structure for table `topics`
--
CREATE TABLE `topics` (
`topicid` int(4) NOT NULL AUTO_INCREMENT,
`boardid` int(2) NOT NULL DEFAULT '0',
`topicname` varchar(255) NOT NULL DEFAULT '',
`author` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`topicid`)
);
回答by Chad Birch
Well, if you had used InnoDB tables, you could set up a cascading delete with foreign keysthat would do it all automatically. But if you have some reason for using MyISAM, You just use a multiple-table DELETE:
好吧，如果您使用过 InnoDB 表，您可以使用外键设置级联删除，这将自动完成所有操作。但是，如果您有使用 MyISAM 的原因，则只需使用多表 DELETE：
DELETE FROM boards, topics, messages
USING boards INNER JOIN topics INNER JOIN messages
WHERE boards.boardid = $boardid
AND topics.boardid = boards.boardid
AND messages.boardid = boards.boardid;
回答by Bernd Ott
this can be done by your db-system if you are using foreign keys with "on delete cascade".
如果您使用带有“删除级联”的外键，这可以由您的数据库系统完成。
Take a look here: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
看看这里：http: //dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
回答by Peter
You could either just check for presence
你可以只检查存在
delete from topics where boardid in (select boardid from boards)
delete from messages where boardid in (select boardid from boards)
but this would only make sense if this behaviour should not always apply. When the behaviour should always apply, implement foreign keys with delete on cascade
但这只有在这种行为不总是适用的情况下才有意义。当行为应该始终适用时，在级联上使用删除实现外键
explained on a zillion sites, in your helpfiles and here
在无数网站、帮助文件和此处进行了解释
回答by Deepak
Deleting rows from multiple tables can be done in two ways :
从多个表中删除行可以通过两种方式完成：
- Delete rows from one table, determining which rows to delete by referring to another table
- Delete rows from multiple tables with a single statement
- 从一个表中删除行，通过引用另一个表来确定要删除哪些行
- 使用一条语句从多个表中删除行
Multiple-table DELETE statements can be written in two formats. The following example demonstrates one syntax, for a query that deletes rows from a table t1 where the id values match those in a table t2:
多表 DELETE 语句可以用两种格式编写。以下示例演示了一种语法，用于从表 t1 中删除 id 值与表 t2 中的值匹配的行的查询：
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
The second syntax is slightly different:
第二种语法略有不同：
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
To delete the matching records from both tables, the statements are:
要从两个表中删除匹配的记录，语句是：
DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;
The ORDER BY and LIMIT clauses normally supported by UPDATE and DELETE aren't allowed when these statements are used for multiple-table operations.
当这些语句用于多表操作时，不允许使用 UPDATE 和 DELETE 通常支持的 ORDER BY 和 LIMIT 子句。
