MySQL含UNIX时间戳表排序后计算相邻行时间差并更新delta列

前置操作：新增delta列 #

首先执行SQL给目标表新增存储差值的列：

ALTER TABLE `你的表名` ADD COLUMN `delta` INT UNSIGNED DEFAULT NULL COMMENT '与上一行的时间差（秒）';

方案1：MySQL 8.0+ 高效批量方案（推荐，大数据量最优） #

用原生窗口函数LAG直接批量计算相邻行差值，仅需一次全表扫描，性能最高：

UPDATE `你的表名` t1
JOIN (
    SELECT 
        id,
        -- 直接用UNIX时间戳相减，无需类型转换
        `timestamp` - LAG(`timestamp`, 1) OVER (ORDER BY `timestamp` ASC) AS calculated_delta
    FROM `你的表名`
    -- 大数据量分批处理可在此加WHERE条件拆分范围，例如：WHERE `timestamp` BETWEEN 1623400800 AND 1623444060
) t2 ON t1.id = t2.id
SET t1.delta = t2.calculated_delta;

方案2：MySQL 5.x 兼容方案 #

通过用户变量模拟窗口函数效果，同样为单次排序遍历，性能优于逐行查询方案：

-- 初始化存储上一行时间戳的变量
SET @prev_ts = NULL;
UPDATE `你的表名`
SET delta = IF(
    @prev_ts IS NULL,
    NULL,
    `timestamp` - @prev_ts
),
-- 保证赋值顺序，避免变量更新早于差值计算
`timestamp` = `timestamp`,
@prev_ts = `timestamp`
ORDER BY `timestamp` ASC
-- 分批处理可在此加WHERE条件拆分范围，例如：WHERE id BETWEEN 1 AND 10000
;

注意事项 #

• 操作前务必先备份全表或目标分片数据，避免误操作导致数据丢失
• 若表数据量超过百万级，建议加WHERE条件按时间范围或ID范围拆分任务，避免长事务锁表影响业务
• 执行完成后可抽样验证结果：查询前10行数据对比delta值是否符合预期

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