时间戳

首页 > 解决方案 > mysql / sql：如何删除除每个用户的最后 N 个行之外的所有行？

mysql - mysql / sql：如何删除除每个用户的最后 N 个行之外的所有行？

问题描述

我有一个快速增长的消息（id、userid、message）表。

我想删除每个用户的所有消息，除了他的最后 30 条消息

例如：如果 user1 有 100 条消息，我们将删除前 70 条，

如果 user2 有 40 条消息，我们将删除前 10 条，

如果 userN 有 10 条消息，则不采取任何措施

有没有办法用一个 SQL 来做到这一点？

我现在的想法是用 PHP 和湖 N sql 做一个循环，这对 N 个用户来说很长。

标签： mysqlsql

解决方案

MySQL (pre 8.0) 没有真正方便的方法来做到这一点。一种方法使用变量来枚举值：

select m.*,
       (@rn := if(@u = userid, @rn + 1,
                  if(@u := userid, 1, 1)
                 )
       ) as seqnum
from (select m.*
      from messages m
      order by userid, id desc
     ) m cross join
     (select @u := -1, @rn := 0) params;

你可以把它变成一个deleteusing join：

delete m
    from messages m join
        (select m.*,
                (@rn := if(@u = userid, @rn + 1,
                           if(@u := userid, 1, 1)
                          )
                ) as seqnum
         from (select m.*
               from messages m
               order by userid, id desc
              ) m cross join
              (select @u := -1, @rn := 0) params
        ) mm
        on m.id = mm.id
where seqnum > 30;

正如我在评论中所说，我认为这不是解决现实问题的好方法。消息的历史很有用，可能还有其他方法可以实现您想要的性能。在经过调整的系统中，用户的 30 条消息和用户的 70 条消息之间的差异不应该对性能产生太大影响。

推荐阅读