SQL查询:从表中删除除最新N之外的所有记录? [英] SQL query: Delete all records from the table except latest N?

问题描述

是否可以构建单个mysql查询(不带变量)以从表中删除所有记录，但最新的N(按ID desc排序)除外?

Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)?

像这样的东西，只有它不起作用:)

Something like this, only it doesn't work :)

delete from table order by id ASC limit ((select count(*) from table ) - N)

谢谢.

推荐答案

您不能以这种方式删除记录，主要问题是不能使用子查询来指定LIMIT子句的值.

You cannot delete the records that way, the main issue being that you cannot use a subquery to specify the value of a LIMIT clause.

这有效(在MySQL 5.0.67中测试):

This works (tested in MySQL 5.0.67):

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

需要中间子查询 .没有它，我们将遇到两个错误:

The intermediate subquery is required. Without it we'd run into two errors:

1. SQL错误(1093):您无法在FROM子句中指定目标表表"进行更新-MySQL不允许您直接在直接引用的表中进行引用子查询.
2. SQL错误(1235):此版本的MySQL尚不支持'LIMIT& IN/ALL/ANY/SOME子查询" -您不能在NOT IN运算符的直接子查询中使用LIMIT子句.

1. SQL Error (1093): You can't specify target table 'table' for update in FROM clause - MySQL doesn't allow you to refer to the table you are deleting from within a direct subquery.
2. SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - You can't use the LIMIT clause within a direct subquery of a NOT IN operator.

幸运的是，使用中间子查询使我们能够绕过这两个限制.

Fortunately, using an intermediate subquery allows us to bypass both of these limitations.

Nicole指出，可以针对某些用例(例如此用例)显着优化此查询.我建议同时阅读该答案，以查看它是否适合您.

Nicole has pointed out this query can be optimised significantly for certain use cases (such as this one). I recommend reading that answer as well to see if it fits yours.

这篇关于SQL查询:从表中删除除最新N之外的所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！