SQL 查询:删除表中除最新 N 之外的所有记录?

2021-11-20 00:00:00 sql mysql sql-delete

是否可以构建单个 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.

相关文章