-- 质检项合格范围配置:由全局固定四项改为按产品维护 -- 1. 主键由 indicator_code 改为自增 id -- 2. 新增产品绑定、名称、单位、排序、状态字段 -- 3. 历史四项自动迁移为通用默认项(item_id 为空),产品未配置时回退使用 -- 4. 质检指标记录补充名称与单位快照 -- ===== 1. 主键改为自增 id ===== SET @has_id = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'id'); SET @sql = IF(@has_id = 0, 'ALTER TABLE mes_qc_seed_indicator_range DROP PRIMARY KEY, ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ===== 2. 新增配置字段(MySQL 不支持 ADD COLUMN IF NOT EXISTS,逐列判断)===== SET @has_item_id = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'item_id'); SET @sql = IF(@has_item_id = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD COLUMN item_id BIGINT NULL COMMENT ''绑定产品编号(mdm_item,itemType=2),为空表示通用默认项'' AFTER id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @has_name = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'indicator_name'); SET @sql = IF(@has_name = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD COLUMN indicator_name VARCHAR(64) NULL COMMENT ''质检项名称'' AFTER indicator_code', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @has_unit = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'unit'); SET @sql = IF(@has_unit = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD COLUMN unit VARCHAR(16) NULL COMMENT ''计量单位'' AFTER indicator_name', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @has_sort = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'sort'); SET @sql = IF(@has_sort = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD COLUMN sort INT NOT NULL DEFAULT 0 COMMENT ''排序'' AFTER max_value', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @has_status = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND column_name = 'status'); SET @sql = IF(@has_status = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD COLUMN status TINYINT NOT NULL DEFAULT 0 COMMENT ''状态:0启用 1停用'' AFTER sort', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ===== 3. 历史数据回填 ===== UPDATE mes_qc_seed_indicator_range SET indicator_name = CASE indicator_code WHEN 'purity' THEN '纯度' WHEN 'cleanliness' THEN '净度' WHEN 'germination_rate' THEN '发芽率' WHEN 'moisture' THEN '水分' ELSE indicator_code END WHERE indicator_name IS NULL OR indicator_name = ''; UPDATE mes_qc_seed_indicator_range SET unit = '%' WHERE unit IS NULL OR unit = ''; UPDATE mes_qc_seed_indicator_range SET sort = CASE indicator_code WHEN 'purity' THEN 10 WHEN 'cleanliness' THEN 20 WHEN 'germination_rate' THEN 30 WHEN 'moisture' THEN 40 ELSE 999 END WHERE sort IS NULL OR sort = 0; -- ===== 4. 产品 + 编码索引 ===== SET @has_index = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_indicator_range' AND index_name = 'idx_item_code'); SET @sql = IF(@has_index = 0, 'ALTER TABLE mes_qc_seed_indicator_range ADD INDEX idx_item_code (item_id, indicator_code)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ===== 5. 质检指标记录补充名称与单位快照 ===== SET @has_ind_name = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_quality_indicator' AND column_name = 'indicator_name'); SET @sql = IF(@has_ind_name = 0, 'ALTER TABLE mes_qc_seed_quality_indicator ADD COLUMN indicator_name VARCHAR(64) NULL COMMENT ''指标名称快照'' AFTER indicator_code', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @has_ind_unit = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_qc_seed_quality_indicator' AND column_name = 'unit'); SET @sql = IF(@has_ind_unit = 0, 'ALTER TABLE mes_qc_seed_quality_indicator ADD COLUMN unit VARCHAR(16) NULL COMMENT ''指标单位快照'' AFTER indicator_name', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; UPDATE mes_qc_seed_quality_indicator SET indicator_name = CASE indicator_code WHEN 'purity' THEN '纯度' WHEN 'cleanliness' THEN '净度' WHEN 'germination_rate' THEN '发芽率' WHEN 'moisture' THEN '水分' ELSE indicator_code END WHERE indicator_name IS NULL OR indicator_name = ''; UPDATE mes_qc_seed_quality_indicator SET unit = '%' WHERE unit IS NULL OR unit = '';