AiDOPWarehouse/tools/sql/fix_collation.sql

-- 统一 collation 为 utf8mb4_general_ci，解决 JOIN 时的排序规则冲突
-- 执行前会先检查当前字段定义

USE aidopdev;

-- 检查当前 QualityLineWorkDetail 的 collation
SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND TABLE_NAME = 'QualityLineWorkDetail'
  AND COLUMN_NAME IN ('Domain', 'ProdLine', 'Site');

-- 检查当前 LineMaster 的 collation
SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND TABLE_NAME = 'LineMaster'
  AND COLUMN_NAME IN ('Domain', 'Line');

-- 修改 QualityLineWorkDetail 表的字段为 utf8mb4_general_ci（保持原长度：ProdLine 12）
ALTER TABLE QualityLineWorkDetail 
  MODIFY Domain VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Site VARCHAR(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY ProdLine VARCHAR(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 修改 LineMaster 表的字段为 utf8mb4_general_ci（保持原长度：Domain 50, Line 100）
ALTER TABLE LineMaster 
  MODIFY Domain VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY Line VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 验证修改结果
SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND TABLE_NAME IN ('QualityLineWorkDetail', 'LineMaster')
  AND COLUMN_NAME IN ('Domain', 'ProdLine', 'Site', 'Line')
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 测试 JOIN 是否还有 collation 冲突
SELECT COUNT(*) AS test_join_count
FROM QualityLineWorkDetail qd 
LEFT JOIN LineMaster lm
  ON qd.Domain = lm.Domain AND qd.ProdLine = lm.Line
WHERE qd.IsActive = 1
LIMIT 1;

SELECT '修改完成！' AS Status;