SQL查询：删除表中除最新的N条记录之外的所有记录？

Question

SQL查询：删除表中除最新的N条记录之外的所有记录？

103

是否有可能构建一个单一的MySQL查询语句（不使用变量），从表中删除除最新的N个记录之外的所有记录（按id降序排序）？

类似于这样的东西，只是它不起作用 :)

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

谢谢。

- serg555

17个回答

123

我知道我在挖掘一个相当古老的问题，但最近我遇到了这个问题，但需要一些适用于大量数据的解决方案。没有现有的性能数据，并且由于这个问题已经引起了相当多的关注，所以我想我会发布我发现的内容。

实际起作用的解决方案是Alex Barrett的双子查询/NOT IN方法（类似于Bill Karwin的），以及Quassnoi的LEFT JOIN方法。

不幸的是，上述两种方法都会创建非常大的中间临时表，并且随着未被删除的记录数目的增加，性能迅速下降。

我最终采用了使用 Alex Barrett 的双子查询（感谢！），但使用了 <= 而不是 NOT IN：

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  );

使用 OFFSET 获取第N条记录的ID，并删除该记录以及所有之前的记录。

由于排序已经是这个问题的假设条件（ORDER BY id DESC），因此 <= 是一个完美的选择。

这种方法速度更快，因为子查询生成的临时表仅包含一条记录，而不是N 条记录。

测试用例

我在两个测试案例中测试了三种可行的方法和上述新方法。

两个测试案例都使用10000个现有行，第一个测试保留9000行（删除最早的1000行），第二个测试保留50行（删除最早的9950行）。

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

有趣的是，<= 方法在各方面都表现更好，但实际上它会随着保留的数量增多而变得更好，而不是更差。

- Nicole

12

我再次阅读这个帖子，距离上次已经过去了4.5年。不错的补充！ - Alex Barrett

1

@KenPalmer 你可以使用 ROW_NUMBER() 来查找特定行的偏移量：https://dev59.com/dHRB5IYBdhLWcg3wgHar - Nicole

3

@KenPalmer 在 SQL 和 mySQL 之间切换时，请使用 SELECT TOP 而不是 LIMIT。 - AlphaG33k

1

谢谢你的帮助。这将我的（非常大的）数据集查询时间从12分钟减少到了3.64秒！ - Lieuwe

1

我想知道如何使用“group by”来完成这个任务。 - Edward

显示剩余2条评论

11

很遗憾，对于其他人给出的所有答案，您无法在同一查询中使用DELETE和SELECT从给定的表中进行操作。

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

MySQL也无法在子查询中支持LIMIT。这是MySQL的局限性。

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

我能提供的最佳答案是分两个阶段完成：

SELECT id FROM mytable ORDER BY id DESC LIMIT n;

收集id并将它们组成逗号分隔的字符串：

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

通常将逗号分隔的列表插入SQL语句中会引入一些SQL注入的风险，但在这种情况下，这些值不是来自不受信任的来源，而是来自数据库本身的整数值。

注意：尽管这不能在单个查询中完成工作，但有时更简单、实用的解决方案是最有效的。

- Bill Karwin

但是您可以在删除和选择之间执行内部连接。我下面所做的应该可以工作。 - achinda99

你需要使用一个中间子查询来使子查询中的LIMIT生效。 - Alex Barrett

@achinda99：我在这个帖子里没有看到你的回答...？ - Bill Karwin

我被拉去开会了，抱歉。目前我没有测试环境来测试我写的SQL语句，但我已经按照Alex Barret所做的，并使用内连接使其正常工作。 - achinda99

这是MySQL的一个愚蠢限制。在PostgreSQL中，DELETE FROM mytable WHERE id NOT IN (SELECT id FROM mytable ORDER BY id DESC LIMIT 3);可以正常工作。 - bortzmeyer

9

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

- Quassnoi

8

如果您的id是递增的，那么可以使用类似以下的方法：

delete from table where id < (select max(id) from table)-N

- Justin Wignall

2

这个巧妙的技巧中存在一个大问题：串行号并不总是连续的（例如当出现回滚时）。 - bortzmeyer

一个大问题：你不能在FROM子句中指定目标表'table'进行更新。 - Xeno

5

要删除除了最后的 N 记录之外的所有记录，您可以使用下面报告的查询。这是一个单一的查询，但有很多语句，因此它实际上不是一个原始问题中预期的单个查询。此外，由于MySQL中存在错误，您需要一个变量和内置（在查询中）准备好的语句。希望它仍然有用... nnn 是要保留的行，而 theTable 是您正在使用的表。我假设您有一个名为 id 的自动递增记录。
SELECT @ROWS_TO_DELETE := COUNT(*) - nnn FROM `theTable`; SELECT @ROWS_TO_DELETE := IF(@ROWS_TO_DELETE<0,0,@ROWS_TO_DELETE); PREPARE STMT FROM "DELETE FROM `theTable` ORDER BY `id` ASC LIMIT ?"; EXECUTE STMT USING @ROWS_TO_DELETE;

这种方法的好处在于性能方面：我已经在本地测试了一个含有大约13,000条记录并保留最后1,000条记录的数据库上运行了这个查询。它只需要0.08秒就可以完成。
来自已接受答案的脚本...
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 );

需要0.55秒，比之前慢了大约7倍。

测试环境：mySQL 5.5.25在一台装有SSD的晚期2011年i7 MacBookPro上运行。

- Paolo

3

DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)

- Dave

1

这将只保留最新的单行。 - Justin Wignall

我认为这是最好的解决方案！ - attaboyabhipro

1

尝试以下查询：

DELETE FROM tablename WHERE id < (SELECT * FROM (SELECT (MAX(id)-10) FROM tablename ) AS a)

内部子查询将返回前10个值，外部查询将删除除前10个之外的所有记录。

- Nishant Nair

1

对于那些遇到这个答案的人来说，一些关于它如何工作的解释会很有益。通常不建议进行代码转储。 - rayryeng

0

关于什么:

SELECT * FROM table del 
         LEFT JOIN table keep
         ON del.id < keep.id
         GROUP BY del.* HAVING count(*) > N;

它返回具有 N 行以上的行。可能会有用吗？

- Hadrien

0

对于使用 Microsoft SQL Server 而不是 MySQL 的任何人，我想把这个内容加入到混合中。MSSQL 不支持关键字“Limit”，因此您需要使用替代方法。这段代码适用于 SQL 2008，并基于此 SO 帖子。https://dev59.com/nXVD5IYBdhLWcg3wU5-H#1104447

-- Keep the last 10 most recent passwords for this user.
DECLARE @UserID int; SET @UserID = 1004
DECLARE @ThresholdID int -- Position of 10th password.
SELECT  @ThresholdID = UserPasswordHistoryID FROM
        (
            SELECT ROW_NUMBER()
            OVER (ORDER BY UserPasswordHistoryID DESC) AS RowNum, UserPasswordHistoryID
            FROM UserPasswordHistory
            WHERE UserID = @UserID
        ) sub
WHERE   (RowNum = 10) -- Keep this many records.

DELETE  UserPasswordHistory
WHERE   (UserID = @UserID)
        AND (UserPasswordHistoryID < @ThresholdID)

诚然，这并不优雅。如果您能够针对Microsoft SQL进行优化，请分享您的解决方案。谢谢！

- Ken Palmer

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex Barrett · Accepted Answer

无法使用子查询来指定LIMIT子句的值，因此您无法以那种方式删除记录。

这个方法可行（在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
);

需要中间子查询。如果没有它，我们会遇到两个错误：

1. SQL错误（1093）：无法在FROM子句中为更新指定目标表“table” - MySQL不允许您在直接子查询中引用正在删除的表。 2. SQL错误（1235）：此版本的MySQL尚不支持'LIMIT＆IN / ALL / ANY / SOME子查询' - 您不能在NOT IN运算符的直接子查询中使用LIMIT子句。

幸运的是，使用中间子查询可以绕过这两个限制。

Nicole指出，对于某些用例（例如此用例），可以显着优化此查询。我建议阅读该答案以查看它是否适合您的情况。