MySQL v5.7 分组删除14天前数据但保留用户最新一条记录如何实现？

实现逻辑说明 #

要删除的记录同时满足两个条件：

• 记录日期早于当前日期前14天
• 不属于「所有记录都早于14天的用户」的最新一条（id最大）例外保留记录

操作步骤 #

1. 先查询确认待删除的记录（避免误操作） #

SELECT * FROM likethis
WHERE 
  `date` < CURDATE() - INTERVAL 14 DAY
  AND id NOT IN (
    SELECT max_id FROM (
      -- 筛选出所有记录都早于14天的用户，取每个用户的最大id（即要保留的例外记录）
      SELECT MAX(id) AS max_id
      FROM likethis 
      GROUP BY userid
      HAVING MAX(`date`) < CURDATE() - INTERVAL 14 DAY
    ) AS t
  )

在你的示例数据中，假设当前日期为2021-11-15，这条查询会返回id为5、6的两条记录，符合预期要删除的内容。

2. 执行删除操作 #

确认待删除记录无误后，执行以下DELETE语句：

DELETE FROM likethis
WHERE 
  `date` < CURDATE() - INTERVAL 14 DAY
  AND id NOT IN (
    SELECT max_id FROM (
      SELECT MAX(id) AS max_id
      FROM likethis 
      GROUP BY userid
      HAVING MAX(`date`) < CURDATE() - INTERVAL 14 DAY
    ) AS t
  )

注意事项 #

• 子查询外层额外嵌套了一层临时表t，是为了规避MySQL 5.7中「不能在FROM子句中指定要更新的目标表」的报错
• 可根据实际业务需要替换CURDATE()为指定的基准日期，比如你示例中要以2021-11-15为基准的话，把CURDATE()换成'2021-11-15'即可

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