MySQL中使用左外连接删除行的问题

在本文中，我们将介绍MySQL中使用左外连接删除行时可能出现的问题。

在MySQL中，LEFT OUTER JOIN是一种连接类型，它返回左侧表中的所有记录以及匹配右侧表中的记录。它还可以用于删除操作，例如我们想从表A中删除没有与表B中匹配的行，我们可以使用以下代码：

DELETE A FROM tableA A
LEFT OUTER JOIN tableB B
ON A.id = B.id
WHERE B.id IS NULL;

这看起来像是一个很好的解决方案，但它实际上可能会出现意外的结果。让我们来看看为什么。

阅读更多：MySQL 教程

左外连接删除行的问题

考虑以下数据：

tableA
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
+----+-------+

tableB
+----+-------+
| id | value |
+----+-------+
| 1  | 100   |
+----+-------+

假设我们使用以上代码将tableA中没有匹配记录的行删除，期望结果应该是tableA中只剩下id=1的记录。

现在让我们来解释一下SQL是如何工作的：LEFT OUTER JOIN将tableA和tableB连接在一起，并返回以下结果：

+----+-------+------+-------+
| id | name  | id   | value |
+----+-------+------+-------+
| 1  | Alice | 1    | 100   |
| 2  | Bob   | NULL | NULL  |
+----+-------+------+-------+

然后，WHERE子句将筛选出id=NULL（即没有与tableB匹配的记录）的行，这样tableA中的id=2的行就被删除了。但是，由于SQL使用了SELECT *，所有列都被返回，即使我们只需要tableA中的列。这将意味着我们可能会删掉几乎所有的列，这对数据库的性能是有害的。

解决方案

为了解决这个问题，我们可以使用以下代码：

DELETE A FROM tableA A
LEFT OUTER JOIN tableB B
ON A.id = B.id
WHERE B.id IS NULL
AND A.id IS NOT NULL;

我们添加了一个额外的条件AND A.id IS NOT NULL，以确保当前正在处理的行是tableA中的行，而不是大量NULL值。

如果使用以上代码，我们将得到以下结果，一条记录都没有被删除：

tableA
+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
+----+-------+

tableB
+----+-------+
| id | value |
+----+-------+
| 1  | 100   |
+----+-------+