MySQL在一个查询中从多个表中删除 [英] MySQL Delete from multiple tables in one query
问题描述
我有这些表，并且正在尝试运行多次删除查询，该查询将在删除维修记录时删除所有产品和任务.
请注意，链接表具有ON DELETE CASCADE约束.
I have these tables and I'm trying to run a multiple delete query that would delete all the products and tasks when deleting a repair record.
Note that the linking tables have ON DELETE CASCADE constraints.
现在我遇到的问题是，当我运行此查询时，它只会删除repair，taskRepair(1个任务记录，但总共有3个任务记录)，以及与已删除任务相关联的产品.
Now the problem that I am having is that when I run this query it only deletes the repair, taskRepair, (1 task record but it has 3 in total), and the products associated with the deleted task.
DELETE bp, t, r
FROM repair AS r
LEFT JOIN taskRepair AS tr ON r.repairID = tr.repairID
INNER JOIN task AS t ON t.taskID = tr.taskID
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
INNER JOIN boughtProduct AS bp ON bp.boughtProductID = bpt.boughtProductID
WHERE r.repairID = ?
我确实将其修改为将DELETE bp, t, r更改为SELECT *的SELECT查询，并且确实返回了所有记录.所以我的问题是此查询出了什么问题?
I did modify this to a SELECT Query changing DELETE bp, t, r to SELECT * and it did return all the records. So my question is what's wrong with this query?
推荐答案
我相信在这种情况下，最佳实践是使用ON DELETE CASCADE选项在表上定义外键约束.
I believe in those cases, best practice is defining foreign key constraints on the tables with ON DELETE CASCADE option.
通过这种方式从父表中删除记录会从子表中删除记录，并且只要有关系就只需要处理一个.
This way deleting the record from the parent table removes the records from child table, and you only need to deal with one as long as there's a relation.
如果您仍希望使用查询执行多个删除，则似乎可以，但不能使用new-syntax联接.从手册:
If you are still looking to perform multiple deletes using a query, it seems to be possible, but not using a new-syntax join. From the manual:
您可以在DELETE语句中指定多个表，以根据WHERE子句中的特定条件从一个或多个表中删除行.但是，不能在多表DELETE中使用ORDER BY或LIMIT. table_references子句列出了联接中涉及的表.第12.2.8.1节"JOIN语法"中介绍了其语法.
You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, "JOIN Syntax".
该手册还包括一个示例:
The manual includes an example as well:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
我希望以下内容对您有用(我没有数据库，所以我无法实际测试查询):
I expect something along the lines of the following will work for you (I haven't the DB so I can't actually test the query):
DELETE bp, t, r
FROM repair AS r, taskRepair AS tr
INNER JOIN task AS t ON t.taskID = tr.taskID
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
INNER JOIN boughtProduct AS bp ON bp.boughtProductID = bpt.boughtProductID
WHERE r.repairID = tr.repairID AND r.repairID = ?
编辑-第二个建议:
DELETE bp, t, r
FROM repair AS r,
task AS t,
taskRepair AS tr,
boughtProduct AS bp
LEFT JOIN boughtProductTask AS bpt ON bpt.taskID = t.taskID
WHERE r.repairID = tr.repairID
AND t.taskID = tr.taskID
AND bp.boughtProductID = bpt.boughtProductID
AND r.repairID = ?
请注意我是如何将JOIN和taskRepair更改为FROM列表中的另一个表的，并一直将该条件添加到WHERE子句中.
Note how I have changed the JOIN with taskRepair to another table in the FROM list, and added the condition all the way down inside the WHERE clause.
来源:
- MySQL Documentation: Foreign Key Constraints
- MySQL Documentation: Delete
这篇关于MySQL在一个查询中从多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
