MySql:删除按日期时间排序的大于n个条目的所有行

Question

有人能帮我删除按日期排序的大于n个条目的所有MySql查询吗？

也就是说，我有1200行带有时间戳列的数据。我需要按日期订购它，并将行保存到前200行。

如果我只有199行数据，那么我需要保存它们。

Answer 1

这将保留前200行(可能还有更多行，具有相同的时间戳)：

DELETE t
FROM 
    tableX AS t
  JOIN
    ( SELECT timestampColumn AS ts
      FROM tableX
      ORDER BY ts ASC
      LIMIT 1 OFFSET 199
    ) tlimit
    ON t.timestampColumn > tlimit.ts

附加问题:为每个用户保留前200行(按uid列)：

DELETE tt
FROM 
      ( SELECT DISTINCT uid             --- these 3 lines can be replaced
        FROM tableX                     --- with:   UserTable AS du
      ) AS du                           --- a table that you probably have
  JOIN
      tableX AS tt
    ON  tt.uid = du.uid
    AND tt.timestampColumn >
        ( SELECT timestampColumn AS ts
          FROM tableX
          WHERE uid = du.uid 
          ORDER BY ts ASC
          LIMIT 1 OFFSET 199
        )

对于一个大表，(uid, timestampColumn)将是有用的。

Answer 2

如果您有一个AUTOINCREMENT主键，并且我们可以假设您没有更新时间戳，那么这个方法应该可以工作并且速度相当快：

DELETE FROM table WHERE id > 
  ( SELECT max(id) FROM 
    ( SELECT id FROM table ORDER BY date DESC LIMIT 200 )
  AS table) ;

考虑到上面的假设，id而不是date的排序会给出完全相同的结果。我还假设您希望删除任何比最新200版本更老的内容，所以ORDER BY是DESC。