AiDOPWarehouse/tools/sql/fix_executable_daily_plan_collations.sql

-- =============================================================================
-- 可执行日计划列表：统一涉及表的字符串列为 utf8mb4_general_ci
-- 错误示例：Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT)
-- =============================================================================
-- 使用前：
--   1. 备份 aidopdev（或当前库名）
--   2. 将下方 USE 中的库名改为实际库名
--   3. 优先执行「方式一」生成语句，在测试环境验证后再在生产执行
-- =============================================================================

USE aidopdev;

-- -----------------------------------------------------------------------------
-- 方式一（推荐）：按当前列类型生成 MODIFY，仅处理非 utf8mb4_general_ci 的字符串列
-- 在 MySQL 客户端执行本段查询，复制结果列 stmt 中的 SQL 逐条执行（或导出后执行）
-- 注意：未附带 DEFAULT/ON UPDATE，若列有默认值或生成列，请在生成结果上手工补全后再执行
-- -----------------------------------------------------------------------------
SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
    IF(IS_NULLABLE = 'YES', ' NULL', ' NOT NULL'),
    ';'
) AS stmt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'PeriodSequenceDet',
    'LineMaster',
    'ItemMaster',
    'WorkOrdMaster',
    'WorkOrdRouting',
    'mes_morder',
    'ProdLineDetail',
    'ScheduleResultOpMaster',
    'WorkCtrMaster',
    'EquipmentList'
  )
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext')
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> 'utf8mb4_general_ci'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- -----------------------------------------------------------------------------
-- 方式二（可选）：若某表不在 information_schema 或生成语句执行失败，
-- 可对照业务实体手工执行下面示例（长度请按你库中 SHOW CREATE TABLE 调整）
-- -----------------------------------------------------------------------------

/*
ALTER TABLE PeriodSequenceDet
  MODIFY `Domain` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Site` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `ItemNum` VARCHAR(30) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Line` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Op` VARCHAR(6) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `WorkCtr` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Period` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `WorkOrds` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Status` VARCHAR(32) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Employee` VARCHAR(128) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `MoldNum` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `UChar1` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `UChar2` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `CreateUser` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `UpdateUser` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE ScheduleResultOpMaster
  MODIFY `Domain` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `WorkOrd` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `WorkCtr` VARCHAR(8) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Line` VARCHAR(500) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `ItemNum` VARCHAR(30) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Op` VARCHAR(6) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `OpDescr` VARCHAR(256) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `InternalEquipmentCode` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `OccupyInternalEquipmentCode` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `MoldTypeCode` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `AssignedEmployeeID` VARCHAR(128) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `SkillNo` VARCHAR(64) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  MODIFY `Remark` VARCHAR(512) NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
*/

-- 验证（执行修复后应无 unicode_ci / 0900_ai_ci）
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'PeriodSequenceDet', 'LineMaster', 'ItemMaster', 'WorkOrdMaster', 'WorkOrdRouting',
    'mes_morder', 'ProdLineDetail', 'ScheduleResultOpMaster', 'WorkCtrMaster', 'EquipmentList'
  )
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;