-- 危险源接入设备采集:绑定设备台账与数采指标键 -- 绑定后由 POST /safety/risk-source/refresh 从 mes_collect_realtime 取值并按阈值判定, -- 不覆盖人工录入模式(machinery_id 为空时仍以 current_value 为准)。 SET @has_machinery := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='safety_risk_source' AND COLUMN_NAME='machinery_id'); SET @s := IF(@has_machinery=0, 'ALTER TABLE safety_risk_source ADD COLUMN machinery_id BIGINT DEFAULT NULL COMMENT ''关联设备台账编号;为空表示人工录入'' AFTER metric_name', 'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @has_metric_key := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='safety_risk_source' AND COLUMN_NAME='metric_key'); SET @s2 := IF(@has_metric_key=0, 'ALTER TABLE safety_risk_source ADD COLUMN metric_key VARCHAR(64) DEFAULT NULL COMMENT ''数采指标键(data_json 字段名)'' AFTER machinery_id', 'SELECT 1'); PREPARE st2 FROM @s2; EXECUTE st2; DEALLOCATE PREPARE st2; SET @has_idx := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='safety_risk_source' AND INDEX_NAME='idx_safety_risk_machinery'); SET @s3 := IF(@has_idx=0, 'ALTER TABLE safety_risk_source ADD KEY idx_safety_risk_machinery (machinery_id, deleted)', 'SELECT 1'); PREPARE st3 FROM @s3; EXECUTE st3; DEALLOCATE PREPARE st3;