MySQL——DELETE语句中的坑

在网上搜索了一个MySQL数据库删除重复数据的sql，原文是这样的：

DELETE
FROM
    people
WHERE
    peopleName IN (
        SELECT
            peopleName
        FROM
            people
        GROUP BY
            peopleName
        HAVING
            count(peopleName) > 1
    )
AND peopleId NOT IN (
    SELECT
        min(peopleId)
    FROM
        people
    GROUP BY
        peopleName
    HAVING
        count(peopleName) > 1
)

运行之后报错，起初以为是没给出别名的问题，给别别名之后依然报错，于是将这个sql拆开，只写基本的删除语句：

DELETE FROM people p WHERE p.username = 'sheamus'

执行之后依然不可以，经研究后得知，DELETE语句中使用别名需要在DELETE与FROM中间写上别名

DELETE p FROM people p WHERE p.username = 'sheamus'

这样运行后就可以了。将这个原因放入最初的sql中，发现还是不行，加上别名之后的sql还是报错，仔细在网上找了关于MySQL语句应注意的地方，终于找到原因，原来在DELETE语句中有WHERE语句中不能出现出现同一张表，people这张表出现在WHERE语句中所有这个语句报错，正确的语句应该是：

DELETE
FROM
    `user`
WHERE
    username IN (
        SELECT
            username
        FROM (
			SELECT * FROM `user`
			 ) AS t
        GROUP BY
            username
        HAVING
            count(username) > 1
    )
AND uid NOT IN (
    SELECT
        min(uid)
    FROM(
		SELECT * FROM `user`
		) AS t
    GROUP BY
        username
    HAVING
        count(username) > 1
)

MySQL删除重复数据的sql经测试可用。