MySQL:删除除最后N条记录之外的所有记录
MySQL:删除除最后N条记录之外的所有记录
提问于 2012-07-01 12:26:13
考虑下表：
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条之外，我如何删除所有记录呢？
回答 3
Database Administration用户
回答已采纳
发布于 2012-07-01 13:36:50
您需要稍微更改您的子查询方法--将条件从WHERE子句移动到join，以绕过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 ;Database Administration用户
发布于 2012-07-01 22:25:01
尝试此排除联接，它将删除子查询不匹配的所有ids。
DELETE p FROM pixels p
LEFT JOIN
(SELECT id
FROM pixels
ORDER BY creation_time DESC
LIMIT 5) p2 USING(id)
WHERE p2.id IS NULL;避免NOT IN (SELECT...)在MySQL (几乎)的一切成本。它对外部查询中的每一行执行子查询。这已在未发布的MySQL 5.6中得到修正。
Database Administration用户
发布于 2018-08-10 05:28:08
保持原始文章的简单性，我建议采用以下方法，这也更容易遵循。(假设；是系统上的语句分隔符)
SELECT @first_id_to_keep := id FROM pixels ORDER BY creation_time DESC LIMIT 1 OFFSET 5;
DELETE FROM pixels WHERE id < @first_id_to_keep;页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/20218
复制相关文章
相似问题
腾讯云开发者
