AiDOPWarehouse/tools/sql/fix_workorder_collations.sql

-- 修复工单调度相关表的 collation 冲突
-- 解决 WorkOrdMaster、ItemMaster、mes_morder、PeriodSequenceDet、LineMaster 的排序规则问题

USE aidopdev;

-- 1. 修复 WorkOrdMaster 表
ALTER TABLE WorkOrdMaster 
  MODIFY COLUMN WorkOrd VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN ItemNum VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN `Domain` VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 2. 修复 ItemMaster 表
ALTER TABLE ItemMaster 
  MODIFY COLUMN ItemNum VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN `Domain` VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 3. 修复 mes_morder 表
ALTER TABLE mes_morder 
  MODIFY COLUMN morder_no VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN factory_id VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 4. 修复 PeriodSequenceDet 表
ALTER TABLE PeriodSequenceDet 
  MODIFY COLUMN ItemNum VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN `Domain` VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN `Line` VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 5. 确认 LineMaster 表（之前可能已修复，再次确认）
ALTER TABLE LineMaster 
  MODIFY COLUMN `Line` VARCHAR(50) COLLATE utf8mb4_general_ci,
  MODIFY COLUMN `Domain` VARCHAR(50) COLLATE utf8mb4_general_ci;

-- 验证修改结果
SELECT '修复完成，请验证以下字段的 collation' AS 说明;

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME IN ('WorkOrdMaster', 'ItemMaster', 'mes_morder', 'PeriodSequenceDet', 'LineMaster')
  AND COLUMN_NAME IN ('WorkOrd', 'ItemNum', 'Domain', 'morder_no', 'factory_id', 'Line')
ORDER BY TABLE_NAME, COLUMN_NAME;