-- =============================================
|
-- 长辉 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;
|
