MES/yawei-mes/.sql/2025-11-15_01_周启威_8Multi接入.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;