-- MDM 物料补「颜色」字段(需求:物料颜色在物料管理(mdm)页面同样可维护) -- 与 mes_md_item.color 保持一致,并按物料编码把 MES 已有的颜色回填过来。 SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdm_item' AND COLUMN_NAME='color'); SET @s := IF(@c=0, 'ALTER TABLE mdm_item ADD COLUMN color VARCHAR(64) DEFAULT NULL COMMENT ''颜色'' AFTER specification', 'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; -- 回填:按物料编码把 MES 侧已维护的颜色同步到 MDM(仅填充 MDM 为空的记录) UPDATE mdm_item m JOIN mes_md_item s ON s.code = m.code AND CAST(s.deleted AS UNSIGNED)=0 SET m.color = s.color WHERE CAST(m.deleted AS UNSIGNED)=0 AND s.color IS NOT NULL AND s.color <> '' AND (m.color IS NULL OR m.color = ''); -- 反向回填:MDM 有颜色而 MES 没有时,同步到 MES(保持两边一致) UPDATE mes_md_item s JOIN mdm_item m ON m.code = s.code AND CAST(m.deleted AS UNSIGNED)=0 SET s.color = m.color WHERE CAST(s.deleted AS UNSIGNED)=0 AND m.color IS NOT NULL AND m.color <> '' AND (s.color IS NULL OR s.color = '');