MySQL 在一个查询中从多个表中删除

Question

我有这些表，我正在尝试运行一个多重删除查询，该查询将在删除维修记录时删除所有产品和任务。请注意，链接表具有 ON DELETE CASCADE 约束。

现在我遇到的问题是，当我运行此查询时，它只删除了repair、taskRepair(1 个任务记录，但总共有 3 个) ，以及与已删除任务关联的产品。

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 = ?

我确实将其修改为 SELECT 查询，将 DELETE bp, t, r 更改为 SELECT * 并且它确实返回了所有记录。所以我的问题是这个查询有什么问题？

Answer 1

我相信在这些情况下，最佳做法是使用 ON DELETE CASCADE 选项在表上定义外键约束。

这种删除父表记录的方式就是删除子表记录，只要有关系就只需要处理一条即可。

---

如果您仍然希望使用查询执行多个删除，这似乎是可行的，但不能使用新语法连接。来自manual :

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”.

手册中还包含一个示例:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

我希望以下内容对您有用(我没有数据库，所以无法实际测试查询):

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 = ?

请注意我是如何将带有 taskRepair 的 JOIN 更改为 FROM 列表中的另一个表，并将条件一直添加到内部WHERE 子句。

---

来源:

• MySQL Documentation: Foreign Key Constraints
• MySQL Documentation: Delete