php - MySQL:每个日期选择一条数据

Question

在我当前的项目中，我每天每小时 ping 一次服务器，这导致每天 24 条记录；随着时间的推移，这是大量数据，我希望管理员(在 PHP 中)能够选择一个日期，并删除该日期之前的所有数据，每天一条数据除外。

例如如果选择的日期是 2/4/16，它将删除 2/4/16 之前每天除一条记录之外的所有记录。

相关数据库表的结构是:

server_status:
id int(11)
server_id int(11)
time_checked datetime
status char(1)

我想知道我需要什么 MySQL 语句来实现这一点 - 我浏览了网络，我发现我可能需要创建新表，删除表并替换它，但似乎是它长时间阻塞数据库的响应。我更困惑的是 select 语句 - 我了解如何选择所述日期之前的数据，但我不知道如何 1)每天仅选择 1 条数据，2)删除其余数据。

非常感谢您的帮助!

Answer 1

您应该能够使用以下查询来实现这一点。

SET @date_param = '2016-04-02';

DELETE FROM server_status WHERE time_checked < @date_param AND id NOT IN (
    SELECT id FROM (
        SELECT MIN(id) AS id
        FROM server_status
        WHERE time_checked < @date_param
        GROUP BY DATE(time_checked) 
    ) AS ids_to_keep
)

显然，如果您愿意，您可以将 sql 变量/参数替换为 php/bound 变量/参数。

子查询被包装在额外的 select 中的原因是因为在 MySQL 中，您无法修改在 SELECT 部分中使用的同一个表。包装额外的选择会创建一个隐式临时表，但这似乎是最简单、最干净的方法。