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
| -- =============================================
| -- 长辉 chdl 设备台账重置为网关设备
| -- 生成时间:2026-10-28
| -- 背景:设备网关实际上报 7 台设备(630框绞机2 / 管绞机 / 70挤出机3 / 1600 /
| -- 70加90挤出机1 / 150挤出机 / 1250),但台账里只有 3 台无关的测试设备
| -- (框绞机 / 笔记本电脑 / 678),数采数据无法绑定。
| -- 做法:旧设备软删除(deleted=1,可回滚),新增 7 台网关设备并配好数采编码。
| -- 说明:车间/工位留空,需业务侧按实际产线补全;设备类型统一挂「生产」。
| -- =============================================
|
| -- ---------- 1. 旧设备软删除 ----------
| UPDATE mes_dv_machinery SET deleted = b'1', updater = '1', update_time = NOW()
| WHERE id IN (2, 3, 4) AND deleted = b'0';
|
| -- ---------- 2. 清理指向旧设备的点检计划关联 ----------
| UPDATE mes_dv_check_plan_machinery SET deleted = b'1', updater = '1', update_time = NOW()
| WHERE machinery_id IN (2, 3, 4) AND deleted = b'0';
|
| -- ---------- 3. 清理指向旧设备的工作站-设备关联 ----------
| UPDATE mes_md_workstation_machine SET deleted = b'1', updater = '1', update_time = NOW()
| WHERE machinery_id IN (2, 3, 4) AND deleted = b'0';
|
| -- ---------- 4. 新增 7 台网关设备 ----------
| -- collect_device_code 必须与 MQTT 报文首层 key 完全一致,否则匹配不到
| INSERT INTO mes_dv_machinery
| (code, name, machinery_type_id, workshop_id, status, collect_device_code, collect_enabled,
| creator, create_time, updater, update_time, deleted)
| VALUES
| ('MC_20261028001', '630框绞机2', 1, NULL, 1, '630框绞机2', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028002', '管绞机', 1, NULL, 1, '管绞机', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028003', '70挤出机3', 1, NULL, 1, '70挤出机3', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028004', '1600', 1, NULL, 1, '1600', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028005', '70加90挤出机1', 1, NULL, 1, '70加90挤出机1', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028006', '150挤出机', 1, NULL, 1, '150挤出机', b'1', '1', NOW(), '1', NOW(), b'0'),
| ('MC_20261028007', '1250', 1, NULL, 1, '1250', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- ---------- 5. 校验 ----------
| SELECT id, code, name, collect_device_code, collect_enabled+0 AS collect_enabled, status, deleted+0 AS deleted
| FROM mes_dv_machinery
| ORDER BY id;
|
|