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

Question

我有一个每小时在我的 php 网站上运行的脚本。在该脚本中，我想要某种 MySQL 查询来删除表中的每条记录，但要删除最新的 50 条记录。

我该如何做类似的事情呢？

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

Answer 1

您可以尝试使用 NOT IN:

EDIT for 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 值始终较新。

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));

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