时间戳

首页 > 解决方案 > 删除数据库中除最后日期外的所有项目

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

问题描述

我有一个看起来（非常简化）的 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 日志记录标准设计的，我认为没有简单的方法来改变它。我想解决的是如何从一个表中选择，然后删除同一个表中除某些行之外的所有行。我只有 MySQL 的中级知识。）

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

编辑：

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

标签： mysqlsqlsql-deletemysql-8.0

解决方案

您可以使用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是您的主键列）。

推荐阅读