mysql - SQL:删除除每个用户的最后 X 条消息之外的旧消息

Question

我正在构建一个事件流，所以旧消息会从末尾掉落，永远不会再出现。所以，我想定期删除旧消息。但是，我不希望它看起来像一个用户没有事件，所以我想在每个流中至少保留 X 个帖子。

简化后，我的“消息”表有一个“中间”(消息 ID，主键)、一个“uid”(用户 ID)、一个“创建”日期(UNIX 时间戳)和一个“消息”。所以我想要的是做这样的事情:

DELETE FROM messages
WHERE created < ? AND mid NOT IN (
    SELECT mid FROM messages m GROUP BY uid HAVING mid > (
        SELECT mid FROM messages WHERE uid = m.uid ORDER BY mid LIMIT 1 OFFSET ?
    )
)

但是，当我单独尝试在 NOT IN() 中进行查询时，它没有返回任何结果。此外，我认为从您要删除的同一个表中 SELECT 是行不通的。

如有必要，我可以接受 2 个查询，但我希望这不是必需的。

我试着四处搜索，有一堆关于如何删除除最后一条记录以外的所有记录的结果，但我没有找到任何关于删除除最后 N 条记录以外的所有记录的结果。

Answer 1

查看此问题以获取“每组前 n 个”问题的 [mysql] 解决方案:

How to SELECT the newest four items per category?

一旦您拥有包含“每组前 n 个”中所有行的集合(这就是您要保留的内容)。您只需要删除那些不在该集合中的行:

delete x
where x.mid not in ( select mid
                     -- where top n per group criteria
                   )

这是另一个查看相同问题的链接:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/