-- ============================================= -- 长辉 chdl 环境 MES 模块缺失列补齐 -- 生成时间:2026-10-28 -- 背景:全接口扫描发现 MES 多个 DO 字段在库中无对应列,导致列表查询 500 -- 说明:MySQL 8 不支持 ADD COLUMN IF NOT EXISTS,这里用存储过程做幂等 -- ============================================= DELIMITER $$ DROP PROCEDURE IF EXISTS chdl_mes_add_column_20261028$$ CREATE PROCEDURE chdl_mes_add_column_20261028( IN p_table_name VARCHAR(64), IN p_column_name VARCHAR(64), IN p_ddl_sql VARCHAR(1000)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table_name AND COLUMN_NAME = p_column_name) THEN SET @mes_col_ddl = p_ddl_sql; PREPARE mes_col_stmt FROM @mes_col_ddl; EXECUTE mes_col_stmt; DEALLOCATE PREPARE mes_col_stmt; END IF; END$$ DELIMITER ; -- ---------- 1. 设备台账:数采字段 ---------- CALL chdl_mes_add_column_20261028('mes_dv_machinery','collect_device_code', 'ALTER TABLE mes_dv_machinery ADD COLUMN collect_device_code varchar(64) NULL COMMENT ''数采设备编码'''); CALL chdl_mes_add_column_20261028('mes_dv_machinery','collect_enabled', 'ALTER TABLE mes_dv_machinery ADD COLUMN collect_enabled bit(1) NULL DEFAULT b''0'' COMMENT ''是否启用数采'''); -- ---------- 2. 库存盘点任务 ---------- CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','taking_date', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN taking_date datetime NULL COMMENT ''盘点日期'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','type', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN type tinyint NULL COMMENT ''盘点类型'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','user_id', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN user_id bigint NULL COMMENT ''盘点人编号'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','blind_flag', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN blind_flag bit(1) NULL DEFAULT b''0'' COMMENT ''是否盲盘'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','frozen', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN frozen bit(1) NULL DEFAULT b''0'' COMMENT ''是否冻结库存'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','start_time', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN start_time datetime NULL COMMENT ''开始时间'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task','end_time', 'ALTER TABLE mes_wm_stock_taking_task ADD COLUMN end_time datetime NULL COMMENT ''结束时间'''); -- ---------- 3. 库存盘点明细 ---------- CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task_line','batch_code', 'ALTER TABLE mes_wm_stock_taking_task_line ADD COLUMN batch_code varchar(64) NULL COMMENT ''批次号'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task_line','taking_quantity', 'ALTER TABLE mes_wm_stock_taking_task_line ADD COLUMN taking_quantity decimal(24,6) NULL COMMENT ''盘点数量'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task_line','status', 'ALTER TABLE mes_wm_stock_taking_task_line ADD COLUMN status tinyint NULL COMMENT ''盘点状态'''); -- ---------- 4. 库存盘点结果 ---------- CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task_result','batch_code', 'ALTER TABLE mes_wm_stock_taking_task_result ADD COLUMN batch_code varchar(64) NULL COMMENT ''批次号'''); CALL chdl_mes_add_column_20261028('mes_wm_stock_taking_task_result','taking_quantity', 'ALTER TABLE mes_wm_stock_taking_task_result ADD COLUMN taking_quantity decimal(24,6) NULL COMMENT ''盘点数量'''); -- ---------- 5. 外协发料明细 ---------- CALL chdl_mes_add_column_20261028('mes_wm_outsource_issue_line','material_stock_id', 'ALTER TABLE mes_wm_outsource_issue_line ADD COLUMN material_stock_id bigint NULL COMMENT ''库存编号'''); CALL chdl_mes_add_column_20261028('mes_wm_outsource_issue_line','batch_id', 'ALTER TABLE mes_wm_outsource_issue_line ADD COLUMN batch_id bigint NULL COMMENT ''批次编号'''); -- ---------- 6. 外协入库明细 ---------- CALL chdl_mes_add_column_20261028('mes_wm_outsource_receipt_line','batch_id', 'ALTER TABLE mes_wm_outsource_receipt_line ADD COLUMN batch_id bigint NULL COMMENT ''批次编号'''); CALL chdl_mes_add_column_20261028('mes_wm_outsource_receipt_line','batch_code', 'ALTER TABLE mes_wm_outsource_receipt_line ADD COLUMN batch_code varchar(64) NULL COMMENT ''批次号'''); CALL chdl_mes_add_column_20261028('mes_wm_outsource_receipt_line','production_date', 'ALTER TABLE mes_wm_outsource_receipt_line ADD COLUMN production_date datetime NULL COMMENT ''生产日期'''); CALL chdl_mes_add_column_20261028('mes_wm_outsource_receipt_line','expire_date', 'ALTER TABLE mes_wm_outsource_receipt_line ADD COLUMN expire_date datetime NULL COMMENT ''有效期至'''); CALL chdl_mes_add_column_20261028('mes_wm_outsource_receipt_line','lot_number', 'ALTER TABLE mes_wm_outsource_receipt_line ADD COLUMN lot_number varchar(64) NULL COMMENT ''批号'''); -- ---------- 7. 装箱管理明细 ---------- CALL chdl_mes_add_column_20261028('mes_wm_package_line','work_order_id', 'ALTER TABLE mes_wm_package_line ADD COLUMN work_order_id bigint NULL COMMENT ''生产工单编号'''); CALL chdl_mes_add_column_20261028('mes_wm_package_line','expire_date', 'ALTER TABLE mes_wm_package_line ADD COLUMN expire_date datetime NULL COMMENT ''有效期至'''); DROP PROCEDURE chdl_mes_add_column_20261028; -- ---------- 8. 数据迁移:遗留 task_date → taking_date ---------- -- mes_wm_stock_taking_task 库中存在遗留列 task_date(代码从未使用), -- 新加的 taking_date 需要从它回填,否则历史盘点任务的日期会丢。 UPDATE mes_wm_stock_taking_task SET taking_date = task_date WHERE taking_date IS NULL AND task_date IS NOT NULL;