如何删除MySQL中同用户且时间差±5分钟的重复记录？

问题分析 #

你的表结构包含id（主键）、user_id和createAt，需要删除同一user_id下createAt时间相差±5分钟的重复记录。你之前尝试的按分钟分组的SQL只能筛选同一分钟内的记录，无法覆盖±5分钟的时间范围，因此无法满足需求。

解决方案 #

前置准备 #

1. 备份数据：删除操作不可逆，执行前务必备份全表数据。
2. 添加索引优化性能：针对10万条数据，先给user_id和createAt添加联合索引，避免全表扫描：

CREATE INDEX idx_user_createAt ON mytable(user_id, createAt);

方法一：自连接删除（兼容所有MySQL版本） #

该方法通过自连接匹配同一user_id下时间差在5分钟内的记录，保留每组中id最小的记录（可根据需求调整保留规则）：

1. 先查询要删除的记录（验证正确性）

SELECT t1.id, t1.user_id, t1.createAt
FROM mytable t1
JOIN mytable t2 
  ON t1.user_id = t2.user_id 
  AND t1.id > t2.id 
  AND TIMESTAMPDIFF(MINUTE, t2.createAt, t1.createAt) BETWEEN 0 AND 5;

• 逻辑：匹配同一用户下，t1的时间在t2之后且相差不超过5分钟，且t1的id更大（保留更早创建的t2）。

2. 执行删除

确认查询结果正确后，执行删除：

DELETE t1
FROM mytable t1
JOIN mytable t2 
  ON t1.user_id = t2.user_id 
  AND t1.id > t2.id 
  AND TIMESTAMPDIFF(MINUTE, t2.createAt, t1.createAt) BETWEEN 0 AND 5;

方法二：窗口函数删除（MySQL 8.0+ 适用） #

如果你的MySQL版本是8.0及以上，可使用窗口函数更直观地标记重复记录：

1. 查询要删除的记录

WITH ranked_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY createAt
        ) AS rn
    FROM mytable
    WHERE EXISTS (
        SELECT 1 
        FROM mytable t2 
        WHERE t2.user_id = mytable.user_id 
          AND TIMESTAMPDIFF(MINUTE, t2.createAt, mytable.createAt) BETWEEN -5 AND 5
          AND t2.id != mytable.id
    )
)
SELECT id FROM ranked_records WHERE rn > 1;

• 逻辑：给同一用户下的记录按时间排序编号，编号大于1的即为需要删除的重复记录。

2. 执行删除

WITH ranked_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY createAt
        ) AS rn
    FROM mytable
    WHERE EXISTS (
        SELECT 1 
        FROM mytable t2 
        WHERE t2.user_id = mytable.user_id 
          AND TIMESTAMPDIFF(MINUTE, t2.createAt, mytable.createAt) BETWEEN -5 AND 5
          AND t2.id != mytable.id
    )
)
DELETE FROM mytable
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

关于你之前代码的问题 #

你之前的SQL使用DATE_FORMAT(createAt,'%Y-%m-%d %H:%i')将时间截断到分钟，然后分组统计，这种方式只能找出同一分钟内的重复记录，完全无法覆盖±5分钟的时间范围，因此无法满足需求。

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