删除查询与多个表的左联接不一致

Question

我在MySQL v5.6中有三个表，由创建

CREATE TABLE tm (
id INT AUTO_INCREMENT,
PRIMARY KEY (id)
);
CREATE TABLE th (
id INT AUTO_INCREMENT,
PRIMARY KEY (id)
);
CREATE TABLE tr (
id INT AUTO_INCREMENT,
tm_id INT,
th_id INT,
PRIMARY KEY (id), 
CONSTRAINT fk_1 FOREIGN KEY (tm_id) REFERENCES tm (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_2 FOREIGN KEY (th_id) REFERENCES th (id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO tm (id) VALUES (101);
INSERT INTO tm (id) VALUES (102);
INSERT INTO th (id) VALUES (1);
INSERT INTO tr (tm_id, th_id) VALUES (101,1), (102,1);

因此，最后我有三个表，其中包含以下数据：

th
| id |
|----|
|  1 |

tr
| id  | tm_id   | th_id |
|-----|---------|-------|
|  11 |     101 |     1 |
|  12 |     102 |     1 |

tm 
| id   |
|------|
|  101 |
|  102 |

我想做的是用一个SQL查询删除所有它们：

DELETE th, tr, tm
FROM th
LEFT JOIN tr ON tr.th_id = th.id
LEFT JOIN tm ON tr.tm_id = tm.id
WHERE th.id = 1;

因此，th和tr表将为空，但tm将保留id=102

+------+
|   id |
+------+
|  102 |
+------+

我不是在寻找更好的查询，而是想知道为什么删除了tm.id=101，但表中留下了tm.iid=102？

Answer 1

这是InnoDB存储引擎的结果，该引擎用于(默认情况下(创建表和外键约束

检查没有外键的演示，结果是所有表的所有行都被删除了

如果使用MyISAM存储引擎创建表，即使有外键约束，也会得到相同的结果
查看演示

但对于InnoDB存储引擎和外键约束，如13.2.2 DELETE语句所述：

如果使用涉及InnoDB表的多表DELETE语句对于存在外键约束的，MySQL优化器可能以与表不同的顺序处理表父母/子女关系。在这种情况下，语句将失败并滚动返回相反，您应该从单个表中删除，并依赖InnoDB提供的ON DELETE功能可导致其他表以进行相应的修改。

因此，您得到的可能是删除tm的id = 102的回滚结果，因为处理的表的顺序会违反外键约束
查看演示