393 lines
17 KiB
SQL
393 lines
17 KiB
SQL
-- ============================================
|
||
-- 1. 设备主表 (device) 扩展
|
||
-- ============================================
|
||
|
||
-- 新增协议类型字段
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `protocol_type` ENUM('8ADPRO', '8MULTI') NOT NULL DEFAULT '8ADPRO'
|
||
COMMENT '协议类型（8ADPRO=原协议，8MULTI=新协议）' AFTER `device_code`;
|
||
|
||
-- 新增电流量程字段（拆分为起点和终点）
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `current1_range_start` DECIMAL(10,3) DEFAULT 0
|
||
COMMENT '电流1量程起点（A），仅8Multi协议使用' AFTER `protocol_type`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `current1_range_end` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电流1量程终点（A），用于0-65535映射，8Multi设备必填' AFTER `current1_range_start`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `current2_range_start` DECIMAL(10,3) DEFAULT 0
|
||
COMMENT '电流2量程起点（A），仅8Multi协议使用' AFTER `current1_range_end`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `current2_range_end` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电流2量程终点（A），用于0-65535映射，8Multi设备必填' AFTER `current2_range_start`;
|
||
|
||
-- 新增质量量程字段（拆分为起点和终点）
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality1_range_start` DECIMAL(10,3) DEFAULT 0
|
||
COMMENT '质量1量程起点，仅8Multi协议使用' AFTER `current2_range_end`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality1_range_end` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '质量1量程终点，用于0-65535映射，8Multi设备必填' AFTER `quality1_range_start`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality1_unit` VARCHAR(10) DEFAULT NULL
|
||
COMMENT '质量1单位（前端配置，如kg/g）' AFTER `quality1_range_end`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality2_range_start` DECIMAL(10,3) DEFAULT 0
|
||
COMMENT '质量2量程起点，仅8Multi协议使用' AFTER `quality1_unit`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality2_range_end` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '质量2量程终点，用于0-65535映射，8Multi设备必填' AFTER `quality2_range_start`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `quality2_unit` VARCHAR(10) DEFAULT NULL
|
||
COMMENT '质量2单位（前端配置，如kg/g）' AFTER `quality2_range_end`;
|
||
|
||
-- 新增电流单位字段
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `current_unit` VARCHAR(10) DEFAULT 'A'
|
||
COMMENT '电流单位（前端配置，如A/mA）' AFTER `quality2_unit`;
|
||
|
||
-- 新增计数器基准值字段（用于清零功能）
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `counter1_baseline` INT DEFAULT 0
|
||
COMMENT '计数1基准值（清零后的基准）' AFTER `current_unit`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `counter2_baseline` INT DEFAULT 0
|
||
COMMENT '计数2基准值（清零后的基准）' AFTER `counter1_baseline`;
|
||
|
||
-- 新增电压配置字段（用于功率计算）
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `voltage1` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电压1配置（V），用于功率1计算，前端配置' AFTER `counter2_baseline`;
|
||
|
||
ALTER TABLE `device`
|
||
ADD COLUMN `voltage2` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电压2配置（V），用于功率2计算，前端配置' AFTER `voltage1`;
|
||
|
||
-- ============================================
|
||
-- 2. 设备数据表 (device_data) 扩展
|
||
-- ============================================
|
||
|
||
-- 新增电流相关字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `current1_raw` INT UNSIGNED DEFAULT NULL
|
||
COMMENT '电流1原始值（0-65535），仅8Multi协议使用' AFTER `current_value`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `current2_raw` INT UNSIGNED DEFAULT NULL
|
||
COMMENT '电流2原始值（0-65535），仅8Multi协议使用' AFTER `current1_raw`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `current1_value` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电流1实际值（A，经量程转换），仅8Multi协议使用' AFTER `current2_raw`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `current2_value` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '电流2实际值（A，经量程转换），仅8Multi协议使用' AFTER `current1_value`;
|
||
|
||
-- 新增质量相关字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `quality1_raw` INT UNSIGNED DEFAULT NULL
|
||
COMMENT '质量1原始值（0-65535），仅8Multi协议使用' AFTER `current2_value`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `quality2_raw` INT UNSIGNED DEFAULT NULL
|
||
COMMENT '质量2原始值（0-65535），仅8Multi协议使用' AFTER `quality1_raw`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `quality1_value` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '质量1实际值（kg，经量程转换），仅8Multi协议使用' AFTER `quality2_raw`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `quality2_value` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '质量2实际值（kg，经量程转换），仅8Multi协议使用' AFTER `quality1_value`;
|
||
|
||
-- 新增湿度字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `humidity` DECIMAL(10,3) DEFAULT NULL
|
||
COMMENT '湿度值（%RH），仅8Multi协议使用' AFTER `quality2_value`;
|
||
|
||
-- 新增工作状态字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `status_work` TINYINT(1) DEFAULT NULL
|
||
COMMENT '工作状态（0=停止，1=工作），仅8Multi协议使用' AFTER `humidity`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `status_stop` TINYINT(1) DEFAULT NULL
|
||
COMMENT '停机状态（0=运行，1=停机），仅8Multi协议使用' AFTER `status_work`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `status_fault` TINYINT(1) DEFAULT NULL
|
||
COMMENT '故障状态（0=正常，1=故障），仅8Multi协议使用' AFTER `status_stop`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `status_reset` TINYINT(1) DEFAULT NULL
|
||
COMMENT '清零状态（0=正常，1=清零），仅8Multi协议使用' AFTER `status_fault`;
|
||
|
||
-- 新增频率字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `frequency1` INT DEFAULT NULL
|
||
COMMENT '频率1原始值，仅8Multi协议使用' AFTER `status_reset`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `frequency2` INT DEFAULT NULL
|
||
COMMENT '频率2原始值，仅8Multi协议使用' AFTER `frequency1`;
|
||
|
||
-- 新增计数器字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter1_current` INT DEFAULT NULL
|
||
COMMENT '计数1当前累计值（下位机传值），仅8Multi协议使用' AFTER `frequency2`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter1_delta` INT DEFAULT NULL
|
||
COMMENT '计数1增量值（当前值-上次值），仅8Multi协议使用' AFTER `counter1_current`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter2_current` INT DEFAULT NULL
|
||
COMMENT '计数2当前累计值（下位机传值），仅8Multi协议使用' AFTER `counter1_delta`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter2_delta` INT DEFAULT NULL
|
||
COMMENT '计数2增量值（当前值-上次值），仅8Multi协议使用' AFTER `counter2_current`;
|
||
|
||
-- 新增8Multi专用的计数器总计字段（避免触发器冲突）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter1_total_8multi` BIGINT UNSIGNED DEFAULT NULL
|
||
COMMENT '计数1累计总数（8Multi专用，从清零基准值开始累计）' AFTER `counter2_delta`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `counter2_total_8multi` BIGINT UNSIGNED DEFAULT NULL
|
||
COMMENT '计数2累计总数（8Multi专用，从清零基准值开始累计）' AFTER `counter1_total_8multi`;
|
||
|
||
-- 新增继电器状态字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `relay_power` TINYINT(1) DEFAULT NULL
|
||
COMMENT '继电器-供电状态（0=断开，1=闭合），仅8Multi协议使用' AFTER `counter2_total_8multi`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `relay_alarm` TINYINT(1) DEFAULT NULL
|
||
COMMENT '继电器-报警状态（0=正常，1=报警），仅8Multi协议使用' AFTER `relay_power`;
|
||
|
||
-- 新增DO输出字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `do_soft_start` TINYINT(1) DEFAULT NULL
|
||
COMMENT 'DO-软启动（0=关，1=开），仅8Multi协议使用' AFTER `relay_alarm`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `do_stop` TINYINT(1) DEFAULT NULL
|
||
COMMENT 'DO-停止（0=关，1=开），仅8Multi协议使用' AFTER `do_soft_start`;
|
||
|
||
-- 新增从机数据字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `slave_data1` INT DEFAULT NULL
|
||
COMMENT '485从机数据1，仅8Multi协议使用' AFTER `do_stop`;
|
||
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `slave_data2` INT DEFAULT NULL
|
||
COMMENT '485从机数据2，仅8Multi协议使用' AFTER `slave_data1`;
|
||
|
||
-- 新增触摸屏数据字段（8Multi协议）
|
||
ALTER TABLE `device_data`
|
||
ADD COLUMN `touchscreen_data` INT DEFAULT NULL
|
||
COMMENT '触摸屏数据，仅8Multi协议使用' AFTER `slave_data2`;
|
||
|
||
-- ============================================
|
||
-- 3. 动态字段配置表 (device_field_header_config) 扩展
|
||
-- ============================================
|
||
|
||
-- 扩展字段枚举，支持8Multi协议新增字段
|
||
ALTER TABLE `device_field_header_config`
|
||
MODIFY COLUMN `field_key` ENUM(
|
||
-- 8ADPRO协议字段
|
||
'analog1','analog2','analog3','analog4','analog5','analog6','analog7',
|
||
'digital1','digital2','digital3','digital4','digital5','digital6',
|
||
-- 8Multi协议新增字段
|
||
'current1','current2','quality1','quality2','humidity',
|
||
'frequency1','frequency2','slave_data1','slave_data2','touchscreen_data'
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
|
||
COMMENT '字段标识（支持8ADPRO和8Multi协议字段）';
|
||
|
||
-- ============================================
|
||
-- 4. 数据验证和索引优化
|
||
-- ============================================
|
||
|
||
-- 为protocol_type字段添加索引，提升查询性能
|
||
ALTER TABLE `device`
|
||
ADD INDEX `idx_protocol_type` (`protocol_type`) USING BTREE;
|
||
|
||
-- 为device_data表的新增字段添加复合索引（可选，根据实际查询需求）
|
||
-- ALTER TABLE `device_data`
|
||
-- ADD INDEX `idx_dd_multi_status` (`device_id`, `status_work`, `status_fault`) USING BTREE;
|
||
|
||
-- ============================================
|
||
-- 5. 数据迁移和初始化
|
||
-- ============================================
|
||
|
||
-- 确保所有现有设备的protocol_type为8ADPRO（已通过DEFAULT设置）
|
||
UPDATE `device` SET `protocol_type` = '8ADPRO' WHERE `protocol_type` IS NULL;
|
||
|
||
-- ============================================
|
||
-- 6. 测试数据插入示例（可选）
|
||
-- ============================================
|
||
|
||
-- 插入一个8Multi协议测试设备
|
||
-- INSERT INTO `device` (
|
||
-- `device_no`, `device_name`, `protocol_type`,
|
||
-- `current1_range`, `current2_range`,
|
||
-- `quality1_range`, `quality2_range`,
|
||
-- `calibration_offset`, `calibration_factor`,
|
||
-- `is_active`, `created_at`, `updated_at`
|
||
-- ) VALUES (
|
||
-- 9001, '8Multi测试设备1', '8MULTI',
|
||
-- 100.0, 50.0,
|
||
-- 1000.0, 500.0,
|
||
-- 0.0, 1.0,
|
||
-- 1, NOW(), NOW()
|
||
-- );
|
||
|
||
-- ============================================
|
||
-- 7. 回滚脚本（仅供紧急情况使用）
|
||
-- ============================================
|
||
|
||
/*
|
||
-- 回滚device表变更
|
||
ALTER TABLE `device` DROP COLUMN `calibration_factor`;
|
||
ALTER TABLE `device` DROP COLUMN `calibration_offset`;
|
||
ALTER TABLE `device` DROP COLUMN `quality2_range`;
|
||
ALTER TABLE `device` DROP COLUMN `quality1_range`;
|
||
ALTER TABLE `device` DROP COLUMN `current2_range`;
|
||
ALTER TABLE `device` DROP COLUMN `current1_range`;
|
||
ALTER TABLE `device` DROP COLUMN `protocol_type`;
|
||
ALTER TABLE `device` DROP INDEX `idx_protocol_type`;
|
||
|
||
-- 回滚device_data表变更
|
||
ALTER TABLE `device_data` DROP COLUMN `touchscreen_data`;
|
||
ALTER TABLE `device_data` DROP COLUMN `slave_data2`;
|
||
ALTER TABLE `device_data` DROP COLUMN `slave_data1`;
|
||
ALTER TABLE `device_data` DROP COLUMN `do_stop`;
|
||
ALTER TABLE `device_data` DROP COLUMN `do_soft_start`;
|
||
ALTER TABLE `device_data` DROP COLUMN `relay_alarm`;
|
||
ALTER TABLE `device_data` DROP COLUMN `relay_power`;
|
||
ALTER TABLE `device_data` DROP COLUMN `frequency2`;
|
||
ALTER TABLE `device_data` DROP COLUMN `frequency1`;
|
||
ALTER TABLE `device_data` DROP COLUMN `status_reset`;
|
||
ALTER TABLE `device_data` DROP COLUMN `status_fault`;
|
||
ALTER TABLE `device_data` DROP COLUMN `status_stop`;
|
||
ALTER TABLE `device_data` DROP COLUMN `status_work`;
|
||
ALTER TABLE `device_data` DROP COLUMN `humidity`;
|
||
ALTER TABLE `device_data` DROP COLUMN `quality2_value`;
|
||
ALTER TABLE `device_data` DROP COLUMN `quality1_value`;
|
||
ALTER TABLE `device_data` DROP COLUMN `quality2_raw`;
|
||
ALTER TABLE `device_data` DROP COLUMN `quality1_raw`;
|
||
ALTER TABLE `device_data` DROP COLUMN `current2_value`;
|
||
ALTER TABLE `device_data` DROP COLUMN `current1_value`;
|
||
ALTER TABLE `device_data` DROP COLUMN `current2_raw`;
|
||
ALTER TABLE `device_data` DROP COLUMN `current1_raw`;
|
||
|
||
-- 回滚device_field_header_config表变更
|
||
ALTER TABLE `device_field_header_config`
|
||
MODIFY COLUMN `field_key` ENUM(
|
||
'analog1','analog2','analog3','analog4','analog5','analog6','analog7',
|
||
'digital1','digital2','digital3','digital4','digital5','digital6'
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
|
||
COMMENT '字段标识（模拟量/开关量字段名）';
|
||
*/
|
||
|
||
-- ============================================
|
||
-- 8. 变更记录
|
||
-- ============================================
|
||
|
||
-- 版本: v2.1
|
||
-- 日期: 2025-11-15
|
||
-- 变更内容:
|
||
-- 1. device表新增16个字段（协议类型、量程起点/终点配置、单位配置、计数器基准值、电压1/电压2）
|
||
-- - 量程字段拆分为起点和终点，支持非零起点量程
|
||
-- - 量程转换公式: 实际值=(原始值/65535)*(量程终点-量程起点)+量程起点
|
||
-- - 移除校准参数字段（不需要校准功能）
|
||
-- - 新增单位配置字段（质量单位、电流单位）
|
||
-- - 新增计数器基准值字段（用于清零功能）
|
||
-- - 新增两个独立的电压配置字段（用于功率计算）
|
||
-- 2. device_data表新增28个字段（8Multi协议专属数据）
|
||
-- - 频率字段改为INT类型（显示原始值）
|
||
-- - 新增计数器累计值和增量值字段（支持减运算）
|
||
-- - 新增8Multi专用的计数器总计字段（counter1_total_8multi/counter2_total_8multi）
|
||
-- - 避免与8ADPRO的触发器冲突
|
||
-- 3. device_field_header_config表扩展field_key枚举
|
||
-- 4. 不修改现有触发器（保证8ADPRO功能完全不受影响）
|
||
-- 5. 新增索引优化查询性能
|
||
-- 影响范围:
|
||
-- - 不影响现有8ADPRO设备功能（千万不要影响！）
|
||
-- - 所有新增字段均可空或有默认值
|
||
-- - 向后兼容，支持混合部署
|
||
-- 协议判断规则:
|
||
-- - device_no>200: 自动设置为8MULTI协议
|
||
-- - device_no<=200: 自动设置为8ADPRO协议
|
||
-- - 同一设备不能在两种协议之间切换
|
||
-- 协议解析规则:
|
||
-- - DI状态: U16格式取最后4位F然后取反
|
||
-- - 继电器+DO: U16格式取最后4位F然后取反（与DI状态解析规则相同）
|
||
-- - 设备开启判断: 电流1>=2A且电流2>=2A（与运算）
|
||
-- - 计数器: 下位机传累计值，后端计算增量（当前值-上次值）
|
||
-- - 频率: 显示原始值
|
||
-- - 从机数据: 直接展示原始值
|
||
-- - 功率计算: 功率1=电压1*电流1，功率2=电压2*电流2（分别计算，两个独立的电压配置）
|
||
-- 数据上报:
|
||
-- - 上报间隔: 10秒
|
||
-- - 数据去重: 相同数据全部存储
|
||
-- 状态指示灯:
|
||
-- - 工作=绿色，停机=黄色，故障=红色，报警=红色
|
||
|
||
-- ============================================
|
||
-- 6. 说明：不修改触发器（避免影响8ADPRO）
|
||
-- ============================================
|
||
|
||
-- 现有触发器update_counter_totals_on_insert保持不变
|
||
-- 该触发器处理8ADPRO的counter1_total和counter2_total字段
|
||
-- 8Multi使用独立的counter1_total_8multi和counter2_total_8multi字段
|
||
-- 两种协议完全隔离，互不干扰
|
||
|
||
-- ============================================
|
||
-- 执行完成提示
|
||
-- ============================================
|
||
|
||
SELECT '8Multi协议数据库变更脚本执行完成！' AS message,
|
||
'请检查以下内容：' AS note,
|
||
'1. device表是否新增16个字段（量程、单位、计数器基准值、电压1/电压2）' AS check1,
|
||
'2. device_data表是否新增28个字段（含counter1_total_8multi/counter2_total_8multi）' AS check2,
|
||
'3. device_field_header_config表枚举是否扩展' AS check3,
|
||
'4. 现有触发器update_counter_totals_on_insert保持不变' AS check4,
|
||
'5. 现有8ADPRO设备数据是否正常（千万不要影响！）' AS check5;
|
||
|
||
-- ============================================
|
||
-- 9. 性能优化索引（2025-11-16 新增）
|
||
-- ============================================
|
||
|
||
-- 为device_data表添加组合索引，大幅提升历史数据查询性能
|
||
-- 该索引会优化以下查询场景：
|
||
-- 1. 按设备ID查询历史数据
|
||
-- 2. 按时间倒序排序（最新数据在前）
|
||
-- 3. 分页查询性能提升
|
||
-- 预期效果：查询速度从 500ms+ 降低到 10-50ms
|
||
CREATE INDEX idx_device_collected
|
||
ON device_data(device_id, collected_at DESC);
|
||
|
||
-- 验证索引创建成功
|
||
SHOW INDEX FROM device_data WHERE Key_name = 'idx_device_collected';
|
||
|
||
-- 性能分析对比（可选，用于验证优化效果）
|
||
-- EXPLAIN SELECT * FROM device_data
|
||
-- WHERE device_id = 3
|
||
-- ORDER BY collected_at DESC
|
||
-- LIMIT 10;
|
||
|
||
SELECT '性能优化索引创建完成！' AS message,
|
||
'历史数据查询性能已优化' AS note;
|