mysql子查询遇坑记录

近日，有一个项目逻辑，需要删除重复编号的记录，于是编写了一个SQL语句，大体如下：

语句一：

DELETE 
FROM
    test 
WHERE
    id IN (
    SELECT
        id 
    FROM
        ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 
    ) 

其实上述语句还有其他写法，比如：

语句二：

delete from test

where id in（select max(id) from test having count(id) > 1）

但是这不是本次要讨论的，

第一条语句中，大家应该看出来了，其中的子查询

语句三：

SELECT
    id 
FROM
    ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
WHERE
    count > 1

是有语法错误的，因为其子查询select max(id),count(id) count from test中并不存在id列，因此在上述语句中会报错说找不到id列

当单独运行语句三的时候，mysql也确实返回了错误：

1054 - Unknown column 'id' in 'field list'

但是如果我们运行语句一，按照预想，也会出现上述错误，但是实际情况却是该语句运行成功了，而且将该表所有数据全部删除了

这时，我们将语句一中的delete替换为select *：

SELECT
    * 
FROM
    test 
WHERE
    id IN (
    SELECT
        id 
    FROM
        ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 
    ) 

查询结果确实是查询出了全部数据。

起初，我以为是条件in的原因，但是当我将in条件替换为运算符‘=’后：

SELECT
    * 
FROM
    test 
WHERE
    id = (
    SELECT
        id 
    FROM
        ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 
    ) 

查询结果仍然是全部数据（注意，我这里的数据，子查询查询出来的结果（如果子查询语法正确）只有一条数据，如果子查询查询结果有多条数据，仍然会返回错误：Subquery returns more than 1 row）

然后我发现，第一层子查询查询的列名id在test表中是存在的，也就是说在第二层子查询的表中存在这个列，但是第二层的查询并没有查询这个列。

首先做测试，将第一层子查询的id换一个名字：

SELECT
        id1 
    FROM
        ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 

结果：1054 - Unknown column 'id1' in 'field list'

运行整个语句：

SELECT
    * 
FROM
    test 
WHERE
    id in (
    SELECT
        id1 
    FROM
        ( SELECT max( id ), count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 
    ) 

结果：1054 - Unknown column 'id1' in 'field list'

当然了，正确的语句如下：

SELECT
    * 
FROM
    test 
WHERE
    id in (
    SELECT
        id
    FROM
        ( SELECT max( id ) id, count( id ) count FROM test GROUP BY NAME ) a 
    WHERE
    count > 1 
    ) 

至此，得出初步结论：

mysql的三层查询中，当第一层查询查询的列名在第二层查询的表中，但是并没有在第二层查询的查询结果中时，本次查询的该使用子查询的查询条件不起作用。

该结论对于delete和update都适用。

具体原因还需要继续研究mysql的子查询的运行原理