-- ============================================= -- MES 数采表索引补齐 -- 生成时间:2026-10-28 -- 背景:建表时只加了普通索引,但代码依赖唯一约束: -- 1. MesCollectRealtimeMapper.selectByDeviceCode 用 selectOne,重复行会抛 TooManyResultsException -- 2. MesCollectDataService.saveRealtime 靠 DuplicateKeyException 兜并发插入 -- 3. saveCycle 同理靠 DuplicateKeyException 去重 -- 说明:用存储过程做幂等(MySQL 8 不支持 ADD INDEX IF NOT EXISTS) -- ============================================= DELIMITER $$ DROP PROCEDURE IF EXISTS chdl_collect_add_index_20261028$$ CREATE PROCEDURE chdl_collect_add_index_20261028( IN p_table_name VARCHAR(64), IN p_index_name VARCHAR(64), IN p_ddl_sql VARCHAR(1000)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table_name AND INDEX_NAME = p_index_name) THEN SET @collect_idx_ddl = p_ddl_sql; PREPARE collect_idx_stmt FROM @collect_idx_ddl; EXECUTE collect_idx_stmt; DEALLOCATE PREPARE collect_idx_stmt; END IF; END$$ DELIMITER ; -- 1. 实时状态:一台设备只有一行,device_code 唯一 CALL chdl_collect_add_index_20261028('mes_collect_realtime','uk_device_code', 'ALTER TABLE mes_collect_realtime ADD UNIQUE KEY uk_device_code (device_code)'); -- 2. 工艺参数快照:一台设备只有一行 CALL chdl_collect_add_index_20261028('mes_collect_tech','uk_device_code', 'ALTER TABLE mes_collect_tech ADD UNIQUE KEY uk_device_code (device_code)'); -- 3. 实时状态历史:同设备同一秒只留一条 CALL chdl_collect_add_index_20261028('mes_collect_realtime_history','uk_device_collect_time', 'ALTER TABLE mes_collect_realtime_history ADD UNIQUE KEY uk_device_collect_time (device_code, collect_time)'); -- 4. 周期过程数据:同设备同一周期只留一条 CALL chdl_collect_add_index_20261028('mes_collect_cycle','uk_device_cycle_no', 'ALTER TABLE mes_collect_cycle ADD UNIQUE KEY uk_device_cycle_no (device_code, cycle_no)'); DROP PROCEDURE chdl_collect_add_index_20261028;