MySQL删除除最新X条记录以外的所有记录

Question

我有一个在我的PHP网站上每小时运行的脚本。在这个脚本中，我想使用某种MySQL查询来删除一张表中除最新的50条记录外的所有记录。

我该如何做到这样？

// pseudo code: like this?
DELETE from chat WHERE id = max (ID - 50) 

- user1022585

---

你能在你的表中添加一个时间戳字段吗？ - O.O

如果您的ID列是自增1的身份列，您可以像这样操作： DELETE FROM chat WHERE ID NOT IN(SELECT TOP 50 ID FROM chat ORDER BY ID DESC) - pistipanko

在这种情况下使用： DELETE FROM chat WHERE datecol NOT IN(SELECT TOP 50 datecol FROM chat ORDER BY datecol DESC)-- SQL ServerDELETE FROM chat WHERE datecol NOT IN(SELECT datecol FROM chat ORDER BY datecol DESC LIMIT 50) --MySQL - pistipanko

@user1022585 - 我认为你应该根据时间跨度（在x和x之间）删除，而不是记录数量。有没有什么理由必须是最后50条而不是一段时间跨度？ - O.O

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - user1022585

可能是重复的问题：SQL查询：删除表中除最新的N条记录之外的所有记录？ - e-sushi

Answer 1

你可以尝试使用 NOT IN：

针对MySQL的编辑：

DELETE FROM chat WHERE id NOT IN ( 
  SELECT id 
  FROM ( 
    SELECT id 
    FROM chat 
    ORDER BY id DESC 
    LIMIT 50
  ) x 
); 

这是针对 SQL-Server 的：

DELETE FROM chat WHERE id NOT IN 
    (SELECT TOP 50 id FROM chat ORDER BY id DESC)  

假设id较高的值总是较新的。

- Brissles

---

最好按照ID的降序排序。 - Joe Stefanelli

尝试过了，但不起作用 :/ 我还有一个date字段，如果有帮助的话。 - user1022585

糟糕，你的问题被标记为 SQL - 现在尝试使用 MySQL 解决方案。 - Brissles

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - user1022585

这个可以运行，但我不明白为什么需要那个 'x'。有人能解释一下吗？ - user1767586

Answer 2

NOT IN是低效的。您可以稍微修改@Mithrandir之前给出的第一个选项，使其如下所示：

DELETE from chat WHERE id < 
  (SELECT id FROM 
    (SELECT id FROM chat ORDER BY id DESC LIMIT 50) t ORDER BY id ASC LIMIT 1));

- esp

Answer 3

你可以尝试这样做：

DELETE from chat WHERE id < (SELECT max(ID)-50 FROM chat) 

如果您的ID值以1步长递增，那么这将起作用。或者您可以使用类似以下的内容：

DELETE FROM chat WHERE id NOT IN 
    (SELECT id FROM ( SELECT ID FROM chat ORDER BY datetime_col DESC LIMIT 50) t ) -- mysql

- Mithrandir

---

你使用哪个数据库系统？ - Mithrandir