AiDOPWarehouse/tools/sql/fix_remaining_collations.sql

-- 【方案总结】统一 aidopdev 数据库中所有表的 collation
-- 目标：将所有 utf8mb4_0900_ai_ci 和 utf8mb4_unicode_ci 统一改为 utf8mb4_general_ci
-- 避免 JOIN 时的 collation 冲突错误

USE aidopdev;

-- ═══════════════════════════════════════════════════════════
-- 已完成的修复（前面步骤）
-- ═══════════════════════════════════════════════════════════
-- ✓ QualityLineWorkDetail (Domain, Site, ProdLine)
-- ✓ LineMaster (Domain, Line)
-- ✓ ResourceOccupancyTime (Domain, Site, Resource, ResourceType, Descr, Ufld1, Ufld2, CreateUser, UpdateUser)

-- ═══════════════════════════════════════════════════════════
-- 还需修复的核心业务表
-- ═══════════════════════════════════════════════════════════

SELECT '开始修复 NbrMaster 和 NbrDetail 表' AS 步骤;

-- NbrMaster 表
ALTER TABLE NbrMaster 
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Cust VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Department VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- NbrDetail 表
ALTER TABLE NbrDetail
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Department VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SELECT '开始修复 PurOrdMaster 和 PurOrdRctMaster 表' AS 步骤;

-- PurOrdMaster 表
ALTER TABLE PurOrdMaster
  MODIFY Domain VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Site VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Department VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- PurOrdRctMaster 表
ALTER TABLE PurOrdRctMaster
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SELECT '开始修复 ScheduleResultOpMaster 表' AS 步骤;

-- ScheduleResultOpMaster 表
ALTER TABLE ScheduleResultOpMaster
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Line VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SELECT '开始修复 LinkagePlan 表' AS 步骤;

-- LinkagePlan 表（只修改可能用于 JOIN 的字段）
ALTER TABLE LinkagePlan
  MODIFY bill_no VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY custom_no VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY item_number VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SELECT '开始修复 OpTransEmployee 表' AS 步骤;

-- OpTransEmployee 表
ALTER TABLE OpTransEmployee
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- ═══════════════════════════════════════════════════════════
-- 验证修复结果
-- ═══════════════════════════════════════════════════════════
SELECT '验证修复结果：检查核心业务表' AS 步骤;

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME IN (
    'NbrMaster', 'NbrDetail', 'PurOrdMaster', 'PurOrdRctMaster',
    'ScheduleResultOpMaster', 'LinkagePlan', 'OpTransEmployee'
  )
  AND COLUMN_NAME IN ('Domain', 'Site', 'Department', 'Cust', 'Line', 'bill_no', 'custom_no', 'item_number')
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;

SELECT '核心业务表修复完成！' AS 状态;