mysql子查询遇坑记录

苏友朋

发布于 2020.08.18 11:22 阅读 196 评论 0

近日,有一个项目逻辑,需要删除重复编号的记录,于是编写了一个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的子查询的运行原理