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

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.id=102却留在了表中？

Answer 1

这是InnoDB存储引擎的结果，该引擎用于创建表和外键约束。

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

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

检查一下演示。

但是对于InnoDB存储引擎和外键约束，就像13.2.2删除语句中提到的那样。

如果使用包含有外键约束的InnoDB表的多表DELETE语句，则MySQL优化器可能以与其父/子关系不同的顺序处理表。在本例中，语句失败并回滚。相反，您应该从单个表中删除，并依赖InnoDB提供的on delete功能来相应地修改其他表。

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

检查一下演示。