mysql批量去除重复记录

删除表中多余的重复记录，根据单个字段判断，如字段名md5，重复记录只留有id最小的记录。

DELETE
FROM
    ir_art
WHERE
    md5 IN (
        SELECT
            md5
        FROM
            ir_art
        GROUP BY
            md5
        HAVING
            count(md5) > 1
    )
AND id NOT IN (
    SELECT
        min(id)
    FROM
        ir_art
    GROUP BY
        md5
    HAVING
        count(md5) > 1
)

命令运行后出现“You can’t specify target table for update in FROM clause”
错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。
将命令改为：

DELETE
FROM
    ir_art
WHERE
    md5 IN (
        SELECT * FROM (
            SELECT
                md5
            FROM
                ir_art
            GROUP BY
                md5
            HAVING
                count(md5) > 1
        )as tb
    )
AND id NOT IN (
    SELECT * FROM (
        SELECT
            min(id)
        FROM
            ir_art
        GROUP BY
            md5
        HAVING
            count(md5) > 1
    )as tb
)

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。