mysql - 删除数据库中除最后日期以外的所有项目

Question

我有一个看起来(非常简化)的 MySQL 表，如下所示:

CREATE TABLE `logging` (
  `id` bigint(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `level` smallint(3) NOT NULL,
  `message` longtext CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL
);

我想删除特定 level 的所有行，除了最后一个( time 是最新的)。

有没有办法用 level 选择所有行设置为特定值，然后在单个 SQL 查询中删除除最新行之外的所有行？我将如何开始解决这个问题？

(正如我所说，这是一个非常简化的表格，所以请不要试图讨论该表格可能存在的设计问题。我删除了一些列。它是按照 PSR-3 logging standard 设计的，我认为没有简单的方法改变它。我想解决的是如何从一个表中进行选择，然后删除同一个表中除某些行之外的所有行。我只有 MySQL 的中级知识。)

谢谢你把我推向正确的方向:)

编辑:

数据库版本是/usr/sbin/mysqld Ver 8.0.18-0ubuntu0.19.10.1 for Linux on x86_64 ((Ubuntu))

Answer 1

您可以使用 ROW_NUMBER()分析函数(如使用 DB 版本 8+):

DELETE lg FROM `logging` AS lg
 WHERE lg.`id` IN
      ( SELECT t.`id` 
          FROM
         (
          SELECT t.*,
                 ROW_NUMBER() OVER (ORDER BY `time` DESC) as rn
            FROM `logging` t
       --  WHERE `level` = @lvl -- optionally add this line to restrict for a spesific value of `level`
         ) t
         WHERE t.rn > 1
       )

删除除最后插入的行之外的所有行(考虑 id 是您的主键列)。