MySQL：刪除除最後 N 條記錄之外的所有記錄

考慮下表：

mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | bigint(20)  | NO   | PRI | NULL              | auto_increment |
| pixel_id      | varchar(32) | NO   | MUL | NULL              |                |
| creation_time | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                |
| pixel         | mediumblob  | NO   |     | NULL              |                |
+---------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.04 sec)

有一些數據：

mysql> SELECT * FROM pixels;
+----+----------------------------------+---------------------+----------------------------------+
| id | pixel_id                         | creation_time       | pixel                            |
+----+----------------------------------+---------------------+----------------------------------+
|  1 | 0d1b042671e0f8c1d1f226abe923583c | 2012-07-01 14:42:26 | 2d8292a62e89fcbf8b1592cf53f0dc86 |
|  2 | 9192b7491ac9321ed67c198834965580 | 2012-07-01 14:42:26 | f41a4a3e1a5f2f25c02f2e377627355c |
|  3 | 82b6ad645a4c75a552c0ddfd8d07c38a | 2012-07-01 14:42:27 | bffd2be16fcb82d0592aaa00fe0ebb9d |
|  4 | de41f4932ee7e90bed2e26d4e7e1937a | 2012-07-01 14:42:27 | 6632df3642ce3465ee5160126f20d837 |
|  5 | f98ac2c09574e2accb6cff709ac8a97f | 2012-07-01 14:42:27 | 00d1a3d9e9b51d7e5f66120203189107 |
|  6 | e90a3233fd9054fb3c23d04b03a8dde8 | 2012-07-01 14:42:27 | 4d20a996a46b9767d8c3f6708cb0ce88 |
|  7 | 08177f9f44f3d6fa515bd1a1983a7b45 | 2012-07-01 14:42:28 | ed3a572da6d05d34f5928035bc67d5be |
|  8 | 9d9138ffb7df537d61276a91e837a327 | 2012-07-01 14:42:28 | 3be6876351254ffa4a00364cd3e8c10e |
+----+----------------------------------+---------------------+----------------------------------+

我正在嘗試刪除除最後一行（例如 5 行）之外的所有記錄。“最後”由 確定id，creation_time可能重複。

我試過了：

mysql> SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5;

並得到了合理的答案：

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

我嘗試將其用作子查詢：

mysql> DELETE FROM pixels WHERE id < 
     (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5);
ERROR 1093 (HY000): You can't specify target table 'pixels' for update in FROM clause

嘗試了另一種方法：

mysql> DELETE FROM pixels WHERE id NOT IN 
     (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 5);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

那麼，如何刪除除 N 個最近的記錄之外的所有記錄？

您需要稍微更改您的子查詢方法 - 將條件從WHERE子句移動到連接，以繞過 MySQL 限制。

如果id和creation_time總是定義相同的順序，你可以使用這個：

DELETE p
FROM 
      pixels AS p
  JOIN
      ( SELECT id 
        FROM pixels 
        ORDER BY id       
          LIMIT 1 OFFSET 4
      ) AS lim
    ON p.id < lim.id ;

由於這可能不正確，並且兩個排序有時可能不同，導致刪除的行數多於或少於 5 行，因此您可以使用它，這將更準確，但對於大表可能更慢：

DELETE p
FROM 
      pixels AS p
  JOIN
      ( SELECT creation_time, id 
        FROM pixels 
        ORDER BY creation_time DESC, id DESC
          LIMIT 1 OFFSET 4
      ) AS lim
    ON p.creation_time < lim.creation_time
    OR p.creation_time = lim.creation_time AND p.id < lim.id ;

引用自：https://dba.stackexchange.com/questions/20218