-- ============================================================
|
-- MES 物料 ID 迁移脚本
|
-- 目的：将 mes_md_item.id 更新为与 mdm_item.id 一致
|
-- 执行前请备份数据库！
|
-- ============================================================
|
|
-- 1. 创建临时映射表
|
DROP TEMPORARY TABLE IF EXISTS tmp_item_id_mapping;
|
CREATE TEMPORARY TABLE tmp_item_id_mapping (
|
old_id BIGINT NOT NULL,
|
new_id BIGINT NOT NULL,
|
code VARCHAR(64),
|
PRIMARY KEY (old_id)
|
);
|
|
-- 2. 插入需要迁移的物料映射数据
|
INSERT INTO tmp_item_id_mapping (old_id, new_id, code)
|
SELECT id, mdm_item_id, code
|
FROM mes_md_item
|
WHERE mdm_item_id IS NOT NULL AND id != mdm_item_id;
|
|
-- 查看需要迁移的数据
|
SELECT * FROM tmp_item_id_mapping;
|
|
-- 3. 禁用外键检查（避免外键约束阻止更新）
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
-- 4. 更新所有引用 mes_md_item.id 的表
|
-- 注意：以下语句会更新所有 item_id 字段
|
|
-- 4.1 基础数据表
|
UPDATE mes_md_item_batch_config t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_md_product_bom t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_md_product_sip t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_md_product_sop t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.2 生产相关表
|
UPDATE mes_pro_card t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_feedback t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_route_product t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_route_product_bom t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_task t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_task_issue t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_pro_work_order_bom t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.3 质量相关表
|
UPDATE mes_qc_indicator_result t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_qc_ipqc t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_qc_iqc t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_qc_oqc t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_qc_rqc t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_qc_template_item t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.4 仓库管理表 - 入库
|
UPDATE mes_wm_arrival_notice_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_item_receipt_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_item_receipt_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_misc_receipt_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_misc_receipt_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_outsource_receipt_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_outsource_receipt_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_receipt_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_receipt_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.5 仓库管理表 - 出库
|
UPDATE mes_wm_item_consume_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_item_consume_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_misc_issue_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_misc_issue_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_outsource_issue_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_outsource_issue_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_issue_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_issue_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_issue_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_issue_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.6 仓库管理表 - 销售/退货
|
UPDATE mes_wm_product_sales_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_sales_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_sales_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_sales_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_vendor_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_return_vendor_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_sales_notice_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.7 仓库管理表 - 其他
|
UPDATE mes_wm_batch t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_material_stock t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_package_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_sn t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_stock_reserve t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_stock_taking_plan_param t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_stock_taking_task_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_stock_taking_task_result t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_transaction t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_transfer_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_transfer_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 4.8 生产产出表
|
UPDATE mes_wm_product_produce_detail t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
UPDATE mes_wm_product_produce_line t
|
JOIN tmp_item_id_mapping m ON t.item_id = m.old_id
|
SET t.item_id = m.new_id;
|
|
-- 5. 更新主表 mes_md_item 的 id
|
-- 由于 id 是主键，需要先删除旧记录再插入新记录
|
-- 创建临时表保存完整数据
|
DROP TEMPORARY TABLE IF EXISTS tmp_mes_md_item;
|
CREATE TEMPORARY TABLE tmp_mes_md_item AS
|
SELECT * FROM mes_md_item;
|
|
-- 删除需要迁移的记录
|
DELETE FROM mes_md_item WHERE id IN (SELECT old_id FROM tmp_item_id_mapping);
|
|
-- 使用新 ID 重新插入
|
INSERT INTO mes_md_item (
|
id, mdm_item_id, code, name, specification, unit_measure_id, item_type_id,
|
status, safe_stock_flag, min_stock, max_stock, high_value, batch_flag, remark,
|
creator, create_time, updater, update_time, deleted
|
)
|
SELECT
|
m.new_id AS id, -- 使用 MDM 物料 ID
|
t.mdm_item_id,
|
t.code, t.name, t.specification, t.unit_measure_id, t.item_type_id,
|
t.status, t.safe_stock_flag, t.min_stock, t.max_stock, t.high_value, t.batch_flag, t.remark,
|
t.creator, t.create_time, t.updater, t.update_time, t.deleted
|
FROM tmp_mes_md_item t
|
JOIN tmp_item_id_mapping m ON t.id = m.old_id;
|
|
-- 6. 重新启用外键检查
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
-- 7. 清理临时表
|
DROP TEMPORARY TABLE IF EXISTS tmp_item_id_mapping;
|
DROP TEMPORARY TABLE IF EXISTS tmp_mes_md_item;
|
|
-- 8. 验证迁移结果
|
SELECT id, mdm_item_id, code, name
|
FROM mes_md_item
|
WHERE mdm_item_id IS NOT NULL;
|
|
-- 检查是否还有 id != mdm_item_id 的记录
|
SELECT COUNT(*) AS need_migration_count
|
FROM mes_md_item
|
WHERE mdm_item_id IS NOT NULL AND id != mdm_item_id;
|
