问题:ZWZ100001从一个查询中删除两个表中的两个表

我有两个表:订单和订单\ _items。两者都共享field OrderID。

I want to delete all rows from both tables where orderIDu003d500, but I need to do this in only one query.这可能吗?

解答

您可以在删除级联上使用定义表。如果这样做,则只需在订单表上删除即可。 The entries in other tables using order_id as foreign key with that option enabled will be deleted automagically.

此示例来自ZWZ100005 MYSQL手册ZWZ100006 ZWZ100004:

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。