为什么我的`delete where id in`语句会删除表中的所有记录？

debugcn 发表于 Dev

合鹏

我正在使用MySQL 5.7.24版本。我想删除回复表中具有相同ex_id和ex_type的记录：

CREATE TABLE `reply` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` varchar(1024) NOT NULL,
  `ex_id` bigint(20) DEFAULT '0',
  `ex_type` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_ex_id_type` (`ex_id`,`ex_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

具有以下数据：

+----+-------------------+-------+---------+
| id | content           | ex_id | ex_type |
+----+-------------------+-------+---------+
|  1 | this is a content |     1 |       1 |
|  2 | this a test       |     2 |       1 |
|  3 | this a contet     |     1 |       1 |
|  4 | the 4th content   |     3 |       1 |
+----+-------------------+-------+---------+

记录1和3共享相同的ex_id和ex_type，我想要删除ID较小的记录（记录1），所以我编写了以下查询：

delete from reply where id in (
    select id from (
        select min(id) from reply group by ex_type and ex_id having count(1) > 1
    ) tmp
)
-- Query OK, 4 rows affected

本应删除一条记录，但所有记录都将被删除。

实际上，此SQL中有一个错误，内部SQLselect min(id) from reply group by ex_type and ex_id having count(1) > 1返回的结果只有一个字段：'min（id）'，外部SQLselect id from () tmp选择一个不存在的字段ID，这会导致错误，但是MySQL仍然执行此sql并删除所有记录。

我想知道为什么会这样。

萨尔曼A

...外部sqlselect id from () tmp选择一个不存在的字段ID，这会导致错误，但是mysql执行此sql并删除所有记录。

我想知道为什么会这样。

此子查询将不会单独运行：

select id from (
    select min(id) from reply group by ex_type and ex_id having count(1) > 1
) tmp
/* SQL Error (1054): Unknown column 'id' in 'field list' */

但是，当它在子查询中运行时，则根据范围解析规则，由于FROM子句中不存在所请求的列，因此id列将解析为外部查询的id列。查询本质上是这样的：

delete from reply where id in (
    select reply.id from (
        select min(id) from reply group by ex_type and ex_id having count(1) > 1
    ) tmp
)
/* Affected rows: 4  Found rows: 0  Warnings: 0  Duration for 1 query: 0.031 sec. */

因为1 IN（1），2 IN（2），3 IN（3）...都为真，所以该条件对于所有行都是真。解决拼写错误（group by ex_type and ex_id）无法解决问题，请将查询更改为此：

delete from reply where id in (
    select tmp.id from (
        select min(id) as id from reply group by ex_type, ex_id having count(1) > 1
    ) tmp
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。