58 lines
2.6 KiB
MySQL
58 lines
2.6 KiB
MySQL
|
-- =============================================
|
|||
|
|
-- 功能：领料单库存快照功能
|
|||
|
|
-- 版本：v1.6.037
|
|||
|
|
-- 日期：2026-02-04
|
|||
|
|
-- 作者：周启威
|
|||
|
|
-- 说明：为领料单明细表添加库存快照相关字段，用于记录领料时的库存状态
|
|||
|
|
-- =============================================
|
|||
|
|
|
|||
|
|
-- 1. 为领料单明细表添加库存快照相关字段
|
|||
|
|
ALTER TABLE wm_production_pick_entry
|
|||
|
|
ADD COLUMN inventory_snapshot DECIMAL(18,6) DEFAULT NULL COMMENT '领料时库存快照',
|
|||
|
|
ADD COLUMN snapshot_time DATETIME DEFAULT NULL COMMENT '快照记录时间',
|
|||
|
|
ADD COLUMN snapshot_warehouse_id BIGINT DEFAULT NULL COMMENT '快照来源仓库ID',
|
|||
|
|
ADD COLUMN snapshot_batch_number VARCHAR(100) DEFAULT NULL COMMENT '快照来源批次号';
|
|||
|
|
|
|||
|
|
-- 2. 添加索引以优化查询性能
|
|||
|
|
-- 用于快速查询指定物料的最新库存快照
|
|||
|
|
CREATE INDEX idx_material_snapshot_time
|
|||
|
|
ON wm_production_pick_entry(material_id, snapshot_time DESC);
|
|||
|
|
|
|||
|
|
-- 3. 添加字段说明注释
|
|||
|
|
ALTER TABLE wm_production_pick_entry
|
|||
|
|
MODIFY COLUMN inventory_snapshot DECIMAL(18,6) DEFAULT NULL COMMENT '领料时库存快照（记录领料时该物料的实时库存总量）',
|
|||
|
|
MODIFY COLUMN snapshot_time DATETIME DEFAULT NULL COMMENT '快照记录时间（领料单创建时间）',
|
|||
|
|
MODIFY COLUMN snapshot_warehouse_id BIGINT DEFAULT NULL COMMENT '快照来源仓库ID（领料单关联的仓库）',
|
|||
|
|
MODIFY COLUMN snapshot_batch_number VARCHAR(100) DEFAULT NULL COMMENT '快照来源批次号（如有多个批次，记录第一个）';
|
|||
|
|
|
|||
|
|
-- 4. 验证字段是否添加成功
|
|||
|
|
SELECT
|
|||
|
|
COLUMN_NAME AS '字段名',
|
|||
|
|
COLUMN_TYPE AS '字段类型',
|
|||
|
|
IS_NULLABLE AS '是否可空',
|
|||
|
|
COLUMN_DEFAULT AS '默认值',
|
|||
|
|
COLUMN_COMMENT AS '字段说明'
|
|||
|
|
FROM
|
|||
|
|
INFORMATION_SCHEMA.COLUMNS
|
|||
|
|
WHERE
|
|||
|
|
TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'wm_production_pick_entry'
|
|||
|
|
AND COLUMN_NAME IN ('inventory_snapshot', 'snapshot_time', 'snapshot_warehouse_id', 'snapshot_batch_number')
|
|||
|
|
ORDER BY
|
|||
|
|
ORDINAL_POSITION;
|
|||
|
|
|
|||
|
|
-- 5. 验证索引是否创建成功
|
|||
|
|
SHOW INDEX FROM wm_production_pick_entry WHERE Key_name = 'idx_material_snapshot_time';
|
|||
|
|
|
|||
|
|
-- =============================================
|
|||
|
|
-- 回滚脚本（如需回滚，请执行以下语句）
|
|||
|
|
-- =============================================
|
|||
|
|
-- ALTER TABLE wm_production_pick_entry
|
|||
|
|
-- DROP COLUMN inventory_snapshot,
|
|||
|
|
-- DROP COLUMN snapshot_time,
|
|||
|
|
-- DROP COLUMN snapshot_warehouse_id,
|
|||
|
|
-- DROP COLUMN snapshot_batch_number;
|
|||
|
|
--
|
|||
|
|
-- DROP INDEX idx_material_snapshot_time ON wms_pro_pick_entry;
|
|||
|
|
-- =============================================
|