MySQL中实现每个设备ID在5分钟内只保留一条历史记录

MySQL中实现每个设备ID在5分钟内只保留一条历史记录，可以使用子查询来获取每个设备每5分钟的最新记录，然后删除这些记录之外的记录。以下是一个可能的解决方案：

假设表名为 device_data，并且该表的结构如下：

CREATE TABLE device_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT,
    record_time DATETIME,
    data VARCHAR(255)
);

可以使用以下SQL语句删除多余的记录，只保留每个设备每5分钟内的一条最新记录：

DELETE FROM device_data
WHERE id NOT IN (
    SELECT * FROM (
        SELECT MAX(id) 
        FROM device_data
        GROUP BY device_id, 
                 UNIX_TIMESTAMP(record_time) DIV 300
    ) AS subquery
);

让我们逐步解释这条SQL语句：

1. 内部子查询：SELECT MAX(id) FROM device_data GROUP BY device_id, UNIX_TIMESTAMP(record_time) DIV 300：

   • UNIX_TIMESTAMP(record_time) DIV 300：将记录时间转换为UNIX时间戳，并除以300秒（即5分钟），从而将时间戳按5分钟的间隔分组。
   • GROUP BY device_id, UNIX_TIMESTAMP(record_time) DIV 300：按设备ID和5分钟的时间间隔分组。
   • SELECT MAX(id)：在每个设备的每个5分钟间隔内，选择ID最大的那条记录，即最新的记录。

2. 外层子查询：为了避免MySQL中的某些限制（如不允许在同一个查询中进行更新和选择操作），需要再包装一层子查询：SELECT * FROM (...) AS subquery。

3. DELETE 语句：DELETE FROM device_data WHERE id NOT IN ( ... )：删除不在内层子查询结果中的记录，即每个设备在每个5分钟内的最新记录以外的记录。

这样，这条SQL语句将实现每个设备在每5分钟内只保留一条最新的历史记录。