求按指定条件删除MySQL test表重复记录的SQL查询语句

注意事项 #

操作前请务必先对test表做全量备份，建议先执行查询语句确认待删除的记录符合预期后，再执行删除操作，避免数据丢失。

方案1：MySQL 8.0+ 版本（支持窗口函数，语法更清晰） #

步骤1：查询待删除的重复记录，验证是否符合预期

SELECT * FROM test 
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (
                PARTITION BY institutionid, domain_update, delete_data 
                ORDER BY id ASC
            ) AS row_num
        FROM test
        WHERE processed = 0
    ) t
    WHERE row_num > 1
);

步骤2：确认无误后执行删除

DELETE FROM test 
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (
                PARTITION BY institutionid, domain_update, delete_data 
                ORDER BY id ASC
            ) AS row_num
        FROM test
        WHERE processed = 0
    ) t
    WHERE row_num > 1
);

说明：ORDER BY id ASC代表每组重复记录中保留id最小的条目，如果你需要保留最新插入的记录，可修改为ORDER BY date DESC或者ORDER BY id DESC。

方案2：兼容 MySQL 5.x 低版本（无需窗口函数支持） #

步骤1：查询待删除的重复记录，验证是否符合预期

SELECT t1.* FROM test t1
INNER JOIN test t2 
  ON t1.institutionid = t2.institutionid 
  AND t1.domain_update = t2.domain_update 
  AND t1.delete_data = t2.delete_data 
  AND t1.processed = 0 
  AND t2.processed = 0 
  AND t1.id > t2.id;

步骤2：确认无误后执行删除

DELETE t1 FROM test t1
INNER JOIN test t2 
  ON t1.institutionid = t2.institutionid 
  AND t1.domain_update = t2.domain_update 
  AND t1.delete_data = t2.delete_data 
  AND t1.processed = 0 
  AND t2.processed = 0 
  AND t1.id > t2.id;

说明：该语句逻辑和方案1一致，默认保留id最小的重复条目，如需保留id最大的条目，将t1.id > t2.id修改为t1.id < t2.id即可。

内容的提问来源于stack exchange，提问作者Moolya