MySQL 8 用历史非空值填充字段NULL的查询转存储过程实现

• 数据基础：MySQL 8实例中某视图存储的时间序列监测数据，包含UTCTIME时间字段、Flammtemperatur1、Flammtemperatur2、Rauchgasventilator、Rezirkulation等指标字段，部分行的指标值为NULL。
• 核心诉求：所用可视化工具无法识别NULL值，会导致趋势图折线中断，需要对每个指标字段，用该字段最近一次出现的非空值填充对应位置的NULL。
• 现有问题：用户已编写的可运行查询使用了MySQL会话变量，无法封装为视图，需要适配的存储过程实现方案供可视化工具直接调用。

SELECT UTCTIME,
case when Flammtemperatur1 is NULL then
@vorheriger_wert_Flammtemperatur1
else
@vorheriger_wert_Flammtemperatur1 := Flammtemperatur1
end as Flammtemperatur_1,
case when Flammtemperatur2 is NULL then
@vorheriger_wert_Flammtemperatur2
else
@vorheriger_wert_Flammtemperatur2 := Flammtemperatur2
end as Flammtemperatur_2,
case when Rauchgasventilator is NULL then
@vorheriger_wert_Rauchgasventilator
else
@vorheriger_wert_Rauchgasventilator := Rauchgasventilator
end as Rauchgasventilator_,
case when Rezirkulation is NULL then
@vorheriger_wert_Rezirkulation
else
@vorheriger_wert_Rezirkulation := Rezirkulation
end as FRezirkulation_
FROM pivot_test
order by utctime asc

上述查询通过会话变量@vorheriger_wert_XXX存储各字段的上一次非空值，MySQL 8的视图语法不支持使用会话变量，因此无法直接封装为视图。

存储过程创建语句 #

-- 临时修改语句分隔符，避免和存储过程内部的分号冲突
DELIMITER //

CREATE PROCEDURE fill_trend_null_data()
BEGIN
    -- 初始化所有会话变量，避免首次调用时变量未定义导致异常
    SET @vorheriger_wert_Flammtemperatur1 = NULL;
    SET @vorheriger_wert_Flammtemperatur2 = NULL;
    SET @vorheriger_wert_Rauchgasventilator = NULL;
    SET @vorheriger_wert_Rezirkulation = NULL;

    -- 执行原空值填充逻辑
    SELECT UTCTIME,
    case when Flammtemperatur1 is NULL then
    @vorheriger_wert_Flammtemperatur1
    else
    @vorheriger_wert_Flammtemperatur1 := Flammtemperatur1
    end as Flammtemperatur_1,
    case when Flammtemperatur2 is NULL then
    @vorheriger_wert_Flammtemperatur2
    else
    @vorheriger_wert_Flammtemperatur2 := Flammtemperatur2
    end as Flammtemperatur_2,
    case when Rauchgasventilator is NULL then
    @vorheriger_wert_Rauchgasventilator
    else
    @vorheriger_wert_Rauchgasventilator := Rauchgasventilator
    end as Rauchgasventilator_,
    case when Rezirkulation is NULL then
    @vorheriger_wert_Rezirkulation
    else
    @vorheriger_wert_Rezirkulation := Rezirkulation
    end as FRezirkulation_
    FROM pivot_test
    ORDER BY utctime ASC;
END //

-- 改回默认语句分隔符
DELIMITER ;

调用方式 #

可视化工具直接执行以下语句即可获取填充后的数据：
CALL fill_trend_null_data();

注意事项 #

• 若时间序列前几行的指标值为NULL，由于没有更早的非空值可填充，这些行的返回值仍为NULL，属于正常情况。
• 每次调用存储过程时都会重新初始化会话变量，不会受之前会话的变量值影响，多次调用结果一致。

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