AiDOPWarehouse/tools/sql/check_workorder_collations.sql

-- 检查工单调度相关表的 collation 状态
-- 用于诊断 WorkOrdMaster、ItemMaster、mes_morder、PeriodSequenceDet、LineMaster 的排序规则

USE aidopdev;

SELECT '工单调度相关表的 collation 检查' AS 检查项;

SELECT 
  TABLE_NAME AS 表名, 
  COLUMN_NAME AS 字段名, 
  COLUMN_TYPE AS 字段类型, 
  COLLATION_NAME AS 排序规则
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', 'Site', 'Workshop')
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 标记出不一致的排序规则
SELECT '⚠️ 排序规则不一致的字段组' AS 警告;

SELECT 
  COLUMN_NAME AS 字段名,
  GROUP_CONCAT(DISTINCT COLLATION_NAME ORDER BY COLLATION_NAME SEPARATOR ' vs ') AS 不同的排序规则,
  GROUP_CONCAT(DISTINCT TABLE_NAME ORDER BY TABLE_NAME SEPARATOR ', ') AS 涉及的表
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')
  AND COLLATION_NAME IS NOT NULL
GROUP BY COLUMN_NAME
HAVING COUNT(DISTINCT COLLATION_NAME) > 1;