创建触发器调用存储过程校验传感器数据阈值并更新有效性字段

现有逻辑的问题 #

当前使用的查询存在两处核心缺陷：

• 仅筛选了符合阈值范围的记录，未覆盖数值超限场景，无法得到校验不通过的0值结果
• 采用隐式多表连接缺少关联条件，且未关联validity表，无法完成有效性字段的更新操作

实现方案 #

方案1：配置AFTER INSERT触发器（推荐） #

适配「records表写入新数据时自动校验」的需求，无需主动调用存储过程，插入动作触发后自动完成校验和结果写入，以下为MySQL语法示例：

DELIMITER //
CREATE TRIGGER check_record_validity AFTER INSERT ON records
FOR EACH ROW
BEGIN
    DECLARE valid_flag TINYINT;
    -- 关联查询对应测量类型的阈值，判断上报值是否在合法区间
    SELECT IF(NEW.value BETWEEN mt.limit_min AND mt.limit_max, 1, 0) INTO valid_flag
    FROM sensors s
    INNER JOIN measure_type mt ON s.measure_variable = mt.measure_variable
    WHERE s.sensorID = NEW.sensorID;
    -- 更新validity表的有效性标识
    -- 若validity表需先插入recordId再更新状态，替换为对应UPDATE语句即可
    INSERT INTO validity (recordId, record_validity) VALUES (NEW.recordId, valid_flag);
END //
DELIMITER ;

方案2：修改现有存储过程 #

如果需要保留原有存储过程的使用逻辑，直接新增关联更新validity表的逻辑即可：

-- 更新符合阈值要求的记录，标记为有效
UPDATE validity v
INNER JOIN records r ON v.recordId = r.recordId
INNER JOIN sensors s ON r.sensorID = s.sensorID
INNER JOIN measure_type mt ON s.measure_variable = mt.measure_variable
SET v.record_validity = 1
WHERE r.value BETWEEN mt.limit_min AND mt.limit_max;

-- 更新超出阈值范围的记录，标记为无效
UPDATE validity v
INNER JOIN records r ON v.recordId = r.recordId
INNER JOIN sensors s ON r.sensorID = s.sensorID
INNER JOIN measure_type mt ON s.measure_variable = mt.measure_variable
SET v.record_validity = 0
WHERE r.value < mt.limit_min OR r.value > mt.limit_max;

注：如果需要批量校验历史存量数据，直接运行上述两条UPDATE语句即可完成全量有效性标记。

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