MySQL实现设备状态表启停时间更新与状态变更插入需求

需求说明 #

针对每个Device id：

• 若其最新记录的State未发生变化，更新该记录的Stoptime为当前时间
• 若State已变更（或设备无历史记录），插入一条新记录，将Starttime和Stoptime均设为当前时间

实现方案（存储过程） #

通过存储过程可便捷处理这种分支逻辑，代码如下：

DELIMITER //

CREATE PROCEDURE UpdateDeviceState(IN p_device_id INT, IN p_new_state VARCHAR(50))
BEGIN
    DECLARE current_state VARCHAR(50);
    
    -- 获取该设备最新记录的状态
    SELECT State INTO current_state
    FROM your_table_name
    WHERE `Device id` = p_device_id
    ORDER BY Stoptime DESC
    LIMIT 1;
    
    -- 根据状态是否变化执行对应操作
    IF current_state IS NOT NULL AND current_state = p_new_state THEN
        -- 状态未变更，更新最新记录的结束时间
        UPDATE your_table_name
        SET Stoptime = NOW()
        WHERE `Device id` = p_device_id
        ORDER BY Stoptime DESC
        LIMIT 1;
    ELSE
        -- 状态变更或无历史记录，插入新状态记录
        INSERT INTO your_table_name (`Device id`, Starttime, Stoptime, State)
        VALUES (p_device_id, NOW(), NOW(), p_new_state);
    END IF;
END //

DELIMITER ;

使用说明 #

1. 将代码中的your_table_name替换为你的实际表名
2. 根据Device id和State的实际数据类型，调整存储过程的参数类型（比如Device id是字符串则改为VARCHAR）
3. 调用存储过程传入设备ID和新状态即可：

CALL UpdateDeviceState(1, 'running');

注意事项 #

• 确保表中的Starttime和Stoptime字段为DATETIME或TIMESTAMP类型
• 若设备无任何历史记录，存储过程会直接插入新记录，符合需求

内容的提问来源于stack exchange，提问作者TheBurnMill