在一个查询中从两个表中删除行 [英] Delete rows from two tables in one query

问题描述

我有两个表:orders和orders_items.两者都共享字段orderID.

I have two tables: orders and orders_items. Both sharing the field orderID.

我想从orderID = 500的两个表中删除所有行，但是我只需要在一个查询中执行此操作.这可能吗?

I want to delete all rows from both tables where orderID=500, but I need to do this in only one query. Is this possible?

推荐答案

您可以使用 ON DELETE CASCADE 定义表.如果这样做，您只需要在订单表上删除即可.使用 order_id 作为外键并启用了该选项的其他表中的条目将被自动删除.

You can define the table with ON DELETE CASCADE. If you do that, you only have to delete on the order table. The entries in other tables using order_id as foreign key with that option enabled will be deleted automagically.

此示例摘自 MySQL手册:

CREATE TABLE parent(
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child(
    id INT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;

请注意，引擎是InnoDB.

Note that the engine is InnoDB.

这篇关于在一个查询中从两个表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！