-- ============================================================ -- 工艺路线产品:补齐「报工入仓库 / 入库库区 / 入库库位」三列 -- 生成时间:2026-10-30 -- -- 问题:前端工艺路线产品表单早已做了三级联动(warehouseId → locationId → areaId), -- 列表也展示 warehouseName / locationName / areaName, -- 但数据库表、DO、SaveReqVO、RespVO 全都没有这三个字段, -- 导致:1) 编辑时下拉框不回显 2) 填了也存不进库 3) 列表三列永远为空 -- -- 做法:补三列,关联 MES 仓储主数据 -- warehouse_id → mes_wm_warehouse.id (报工入仓库) -- location_id → mes_wm_warehouse_location.id(入库库区) -- area_id → mes_wm_warehouse_area.id (入库库位) -- 层级关系:仓库 → 库区 → 库位,库区挂在仓库下,库位挂在库区下 -- -- 幂等:先查 information_schema 再 ALTER,可重复执行 -- ============================================================ DROP PROCEDURE IF EXISTS sp_route_product_add_col; DELIMITER $$ CREATE PROCEDURE sp_route_product_add_col(IN p_col VARCHAR(64), IN p_comment VARCHAR(255), IN p_after VARCHAR(64)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = p_col) THEN SET @s = CONCAT('ALTER TABLE `mes_pro_route_product` ADD COLUMN `', p_col, '` bigint NULL COMMENT ''', p_comment, ''' AFTER `', p_after, '`'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END IF; IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND index_name = CONCAT('idx_', p_col)) THEN SET @s2 = CONCAT('CREATE INDEX `idx_', p_col, '` ON `mes_pro_route_product` (`', p_col, '`)'); PREPARE st2 FROM @s2; EXECUTE st2; DEALLOCATE PREPARE st2; END IF; END$$ DELIMITER ; CALL sp_route_product_add_col('warehouse_id', '报工入仓库(关联 mes_wm_warehouse.id)', 'item_id'); CALL sp_route_product_add_col('location_id', '入库库区(关联 mes_wm_warehouse_location.id)', 'warehouse_id'); CALL sp_route_product_add_col('area_id', '入库库位(关联 mes_wm_warehouse_area.id)', 'location_id'); DROP PROCEDURE IF EXISTS sp_route_product_add_col; -- ---------- 校验 ---------- SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND COLUMN_NAME IN ('warehouse_id','location_id','area_id') ORDER BY ORDINAL_POSITION;