-- 盘点任务、任务行和结果表增量迁移;保留现有数据,可重复执行。 DROP PROCEDURE IF EXISTS add_stock_taking_column; DELIMITER // CREATE PROCEDURE add_stock_taking_column(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_definition VARCHAR(255)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = p_table AND column_name = p_column) THEN SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition); PREPARE statement FROM @ddl; EXECUTE statement; DEALLOCATE PREPARE statement; END IF; END// DELIMITER ; CALL add_stock_taking_column('mes_wm_stock_taking_task', 'taking_date', 'datetime NULL COMMENT ''盘点日期'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'type', 'int NULL COMMENT ''盘点类型'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'user_id', 'bigint NULL COMMENT ''盘点人'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'blind_flag', 'bit(1) NOT NULL DEFAULT b''0'' COMMENT ''是否盲盘'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'frozen', 'bit(1) NOT NULL DEFAULT b''0'' COMMENT ''是否冻结库存'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'start_time', 'datetime NULL COMMENT ''开始时间'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'end_time', 'datetime NULL COMMENT ''结束时间'''); CALL add_stock_taking_column('mes_wm_stock_taking_task', 'difference_processed', 'bit(1) NOT NULL DEFAULT b''0'' COMMENT ''差异已入账'''); UPDATE mes_wm_stock_taking_task SET taking_date = task_date WHERE taking_date IS NULL AND task_date IS NOT NULL; CALL add_stock_taking_column('mes_wm_stock_taking_task_line', 'batch_code', 'varchar(64) NULL COMMENT ''批次号'''); CALL add_stock_taking_column('mes_wm_stock_taking_task_line', 'taking_quantity', 'decimal(24,6) NULL COMMENT ''盘点数量'''); CALL add_stock_taking_column('mes_wm_stock_taking_task_line', 'status', 'int NULL COMMENT ''盘点状态'''); CALL add_stock_taking_column('mes_wm_stock_taking_task_result', 'batch_code', 'varchar(64) NULL COMMENT ''批次号'''); CALL add_stock_taking_column('mes_wm_stock_taking_task_result', 'taking_quantity', 'decimal(24,6) NULL COMMENT ''盘点数量'''); DROP PROCEDURE IF EXISTS add_stock_taking_column;