1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| -- ============================================================
| -- 物料分类统一迁移：mes_md_item_type -> mdm_item_category
| --
| -- 执行时机：上线前执行一次（MES 分类接口切换底层存储之前）
| -- 特性：
| -- 1. 按原 ID 迁移，保证 mes_md_item.item_type_id、mdm_item.category_id 等历史引用继续有效
| -- 2. 包含已逻辑删除记录，避免引用断链
| -- 3. 可重复执行（主键冲突时更新业务字段，不会产生重复数据）
| -- ============================================================
|
| INSERT INTO mdm_item_category
| (id, parent_id, code, name, item_type, item_or_product, sort, status, remark,
| creator, create_time, updater, update_time, deleted)
| SELECT new.id,
| COALESCE(new.parent_id, 0),
| new.code,
| new.name,
| CASE WHEN UPPER(new.item_or_product) = 'PRODUCT' THEN 2 ELSE 1 END,
| new.item_or_product,
| COALESCE(new.sort, 0),
| COALESCE(new.status, 0),
| new.remark,
| COALESCE(new.creator, ''),
| COALESCE(new.create_time, CURRENT_TIMESTAMP),
| COALESCE(new.updater, ''),
| COALESCE(new.update_time, CURRENT_TIMESTAMP),
| COALESCE(new.deleted, b'0')
| FROM mes_md_item_type AS new
| WHERE new.name IS NOT NULL
| ON DUPLICATE KEY UPDATE
| parent_id = COALESCE(new.parent_id, 0),
| code = new.code,
| name = new.name,
| item_type = CASE WHEN UPPER(new.item_or_product) = 'PRODUCT' THEN 2 ELSE 1 END,
| item_or_product = new.item_or_product,
| sort = COALESCE(new.sort, 0),
| status = COALESCE(new.status, 0),
| remark = new.remark,
| deleted = COALESCE(new.deleted, b'0');
|
| -- 迁移结果核对：两侧 ID 应完全一致
| -- SELECT (SELECT COUNT(*) FROM mes_md_item_type) AS mes_cnt,
| -- (SELECT COUNT(*) FROM mdm_item_category) AS mdm_cnt,
| -- (SELECT COUNT(*) FROM mes_md_item_type t
| -- WHERE NOT EXISTS (SELECT 1 FROM mdm_item_category c WHERE c.id = t.id)) AS missing_cnt;
|
|
