AiDOPWarehouse/tools/sql/check_shipping_collation.sql

-- 检查发货计划相关表的 collation
USE aidopdev;

SELECT '发货计划涉及的 JOIN 字段' AS 检查项;

-- ShippingPlanDetail 的 ItemNum 和 bill_no
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'ShippingPlanDetail'
  AND COLUMN_NAME IN ('ItemNum', 'bill_no', 'OrdNbr')
ORDER BY COLUMN_NAME;

-- ASNBOLShipperDetail 的 ContainerItem 和 ordnbr
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'ASNBOLShipperDetail'
  AND COLUMN_NAME IN ('ContainerItem', 'ordnbr', 'OrdNbr')
ORDER BY COLUMN_NAME;

-- 检查所有需要统一的表
SELECT '需要修复为 utf8mb4_general_ci 的表' AS 检查项;
SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND COLLATION_NAME IN ('utf8mb4_0900_ai_ci', 'utf8mb4_unicode_ci')
  AND TABLE_NAME NOT LIKE 'sys_%'
ORDER BY TABLE_NAME, COLUMN_NAME;