按指定字段删除MySQL重复记录，保留最新created_at记录

操作前必做：备份数据 #

删除操作不可逆，先备份原表数据避免意外：

CREATE TABLE your_table_backup AS SELECT * FROM your_table_name;

方法一：使用窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数对重复分组的记录按created_at倒序排名，排名大于1的即为需要删除的重复项。

1. 先验证要删除的记录（可选但推荐） #

提前确认待删除数据是否符合预期：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY DATE(created_at), aircraft_id, dep_airport_id, arr_airport_id
               ORDER BY created_at DESC
           ) AS rn
    FROM your_table_name
) t
WHERE rn > 1;

2. 执行删除 #

如果表有主键（比如id），用主键关联效率更高：

DELETE FROM your_table_name
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                   PARTITION BY DATE(created_at), aircraft_id, dep_airport_id, arr_airport_id
                   ORDER BY created_at DESC
               ) AS rn
        FROM your_table_name
    ) t
    WHERE rn > 1
);

无主键时，用多字段关联删除：

DELETE t1
FROM your_table_name t1
INNER JOIN (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY DATE(created_at), aircraft_id, dep_airport_id, arr_airport_id
               ORDER BY created_at DESC
           ) AS rn
    FROM your_table_name
) t2 ON t1.created_at = t2.created_at 
    AND t1.aircraft_id = t2.aircraft_id 
    AND t1.dep_airport_id = t2.dep_airport_id 
    AND t1.arr_airport_id = t2.arr_airport_id
WHERE t2.rn > 1;

方法二：关联删除（兼容MySQL 5.x版本） #

通过子查询找出每个重复分组的最新created_at，再删除不属于最新记录的条目。

1. 验证要删除的记录（可选） #

SELECT t1.*
FROM your_table_name t1
LEFT JOIN (
    SELECT DATE(created_at) AS record_date, aircraft_id, dep_airport_id, arr_airport_id, MAX(created_at) AS latest_created
    FROM your_table_name
    GROUP BY record_date, aircraft_id, dep_airport_id, arr_airport_id
) t2 ON DATE(t1.created_at) = t2.record_date
    AND t1.aircraft_id = t2.aircraft_id
    AND t1.dep_airport_id = t2.dep_airport_id
    AND t1.arr_airport_id = t2.arr_airport_id
    AND t1.created_at = t2.latest_created
WHERE t2.latest_created IS NULL;

2. 执行删除 #

DELETE t1
FROM your_table_name t1
LEFT JOIN (
    SELECT DATE(created_at) AS record_date, aircraft_id, dep_airport_id, arr_airport_id, MAX(created_at) AS latest_created
    FROM your_table_name
    GROUP BY record_date, aircraft_id, dep_airport_id, arr_airport_id
) t2 ON DATE(t1.created_at) = t2.record_date
    AND t1.aircraft_id = t2.aircraft_id
    AND t1.dep_airport_id = t2.dep_airport_id
    AND t1.arr_airport_id = t2.arr_airport_id
    AND t1.created_at = t2.latest_created
WHERE t2.latest_created IS NULL;

注意事项 #

• 替换所有your_table_name为实际表名
• 优先使用主键关联的删除方式，避免因时间戳精度问题导致的匹配错误
• 执行删除前务必确认验证结果符合预期

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