在MySQL中选择除顶N之外的所有

Question

我有一个不需要保存很长时间的数据表，所以每天晚上我都想删除除最后20行之外的所有行。

为此，我发现了以下查询：

DELETE FROM Table WHERE ID NOT IN (
    SELECT id FROM (
        SELECT TOP 10 ID FROM Table
    ) AS x
)

MySQL不支持TOP函数，因此我将其重写为使用限制：

DELETE FROM Table WHERE ID NOT IN (
    SELECT id FROM (
        SELECT ID FROM Table ORDER BY ID DESC LIMIT 10
    ) AS x
)

不幸的是，MySQL似乎不支持子查询中的限制函数。那我现在该怎么办？

如何选择ID最高的10行以外的所有行？

我可能只是删除所有的记录，是超过一天或什么，但它觉得我应该能够这样做。

Answer 1

另一种方式是：

DELETE t
FROM 
    TableX AS t
  CROSS JOIN
    ( SELECT Id
      FROM TableX
      ORDER BY Id DESC
      LIMIT 1 OFFSET 9
    ) AS tenth
WHERE t.Id < tenth.id 

Answer 2

由于MySQL在从您要删除的同一表中读取数据时存在缺陷，所以最简单的选项通常是使用临时表。

INSERT INTO yourTempTable
SELECT id FROM yourTable
ORDER BY ID DESC LIMIT 10

DELETE yourTable WHERE id IN (SELECT id FROM yourTempTable)

或其中的许多变体(使用联接而不是IN等)。

主要考虑的不是如何编写第二个查询，而是关于竞争条件。

您的数据可以由临时表和delete之间的另一个进程更改。如果这是可能的，而且重要的话，您需要将其全部包装在一个事务中，并在yourTable上设置一个表锁。

Answer 3

有几种方法可以这样做，我更喜欢这样做:-

 create table tmp_table select * from your_table order by id desc limit 20;
 truncate table your_table;
 insert into your_table select * from tmp_table;
 drop table tmp_table;

乍一看，它似乎很长，

但我个人认为是可以理解的，

而且风险很低(另外，做一个连接应该是有效的)

PS :截断不会重置自动增量字段

ps2 :这种方法假设在截断期间没有写，可以发出锁以保持数据完整性。