MySQL插入新行时如何基于前一行数据跨行列自动计算目标值

你之前用到的AS语法属于MySQL的生成列特性，这类生成列仅能引用当前行的字段，无法实现跨行取值计算，所以你的需求无法通过生成列实现，可通过以下两种方案落地： #

方案1：插入时自动计算并持久化（触发器方案，MySQL 5.7及以上支持） #

这个方案满足你「插入时自动写入对应字段」的需求，通过BEFORE INSERT触发器实现：

第一步：示例表结构 #

CREATE TABLE sleep_records (
    record_date DATE PRIMARY KEY COMMENT '日期',
    getup_time TIME NOT NULL COMMENT '起床时间',
    bed_time TIME NOT NULL COMMENT '就寝时间',
    hours_slept DECIMAL(4,2) DEFAULT NULL COMMENT '睡眠时间（小时）'
);

第二步：创建计算触发器 #

DELIMITER //
CREATE TRIGGER calc_sleep_hours BEFORE INSERT ON sleep_records
FOR EACH ROW
BEGIN
    DECLARE prev_day_bed_time TIME;
    -- 获取前一天的就寝时间
    SELECT bed_time INTO prev_day_bed_time 
    FROM sleep_records 
    WHERE record_date = SUBDATE(NEW.record_date, 1)
    LIMIT 1;
    -- 仅当存在前一天数据时计算睡眠时间
    IF prev_day_bed_time IS NOT NULL THEN
        SET NEW.hours_slept = ROUND(
            TIMESTAMPDIFF(
                SECOND,
                -- 拼接成前一天完整的就寝时间戳
                CONCAT(SUBDATE(NEW.record_date, 1), ' ', prev_day_bed_time),
                -- 拼接成当天完整的起床时间戳
                CONCAT(NEW.record_date, ' ', NEW.getup_time)
            ) / 3600,
            2
        );
    END IF;
END //
DELIMITER ;

注意事项 #

• 仅按日期正序插入数据时可以得到正确结果，如果存在补录过往日期数据的需求，补录后需要手动更新后续日期的hours_slept值，或额外新增AFTER UPDATE类型的触发器同步更新关联行。
• 其他RDBMS（如PostgreSQL、SQL Server）也支持类似的触发器逻辑，仅语法细节存在差异，核心思路一致。

方案2：查询时动态计算（无需持久化存储） #

如果不需要把睡眠时间固化到表中，可直接在查询时通过窗口函数LAG取前一行数据计算，无需维护触发器，补录数据也不需要额外处理：

SELECT 
    record_date,
    getup_time,
    bed_time,
    ROUND(
        TIMESTAMPDIFF(
            SECOND,
            CONCAT(SUBDATE(record_date, 1), ' ', LAG(bed_time) OVER (ORDER BY record_date)),
            CONCAT(record_date, ' ', getup_time)
        ) / 3600,
        2
    ) AS hours_slept
FROM sleep_records;

该方案仅支持MySQL 8.0及以上版本，更低版本可通过自关联实现等价逻辑。

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