all-project/backend/script/sql/fix-college-vs-collage-fields.sql

-- 修复学院字段拼写问题
-- 版本: 1.0.0
-- 创建日期: 2025-07-13
-- 描述: 检查并修复学院字段的拼写问题（college vs collage）

-- 第一步：检查字段情况
SELECT
    COUNT(*) as college_name_count,
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'zsjy_enrollment_info'
     AND COLUMN_NAME = 'collage_name'
     AND TABLE_SCHEMA = DATABASE()) as collage_name_count,
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'zsjy_enrollment_info'
     AND COLUMN_NAME = 'college_id'
     AND TABLE_SCHEMA = DATABASE()) as college_id_count,
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'zsjy_enrollment_info'
     AND COLUMN_NAME = 'collage_id'
     AND TABLE_SCHEMA = DATABASE()) as collage_id_count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'zsjy_enrollment_info'
AND COLUMN_NAME = 'college_name'
AND TABLE_SCHEMA = DATABASE();

-- 第二步：如果存在collage_name而不存在college_name，则重命名字段
SET @rename_collage_name_sql = (
    SELECT IF(
        EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'zsjy_enrollment_info'
               AND COLUMN_NAME = 'collage_name'
               AND TABLE_SCHEMA = DATABASE())
        AND
        NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'zsjy_enrollment_info'
                  AND COLUMN_NAME = 'college_name'
                  AND TABLE_SCHEMA = DATABASE()),
        'ALTER TABLE zsjy_enrollment_info CHANGE COLUMN collage_name college_name varchar(100) COMMENT \'学院名称\'',
        'SELECT \'无需修改collage_name字段\' AS message'
    )
);

PREPARE stmt FROM @rename_collage_name_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 第三步：如果存在collage_id而不存在college_id，则重命名字段
SET @rename_collage_id_sql = (
    SELECT IF(
        EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'zsjy_enrollment_info'
               AND COLUMN_NAME = 'collage_id'
               AND TABLE_SCHEMA = DATABASE())
        AND
        NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'zsjy_enrollment_info'
                  AND COLUMN_NAME = 'college_id'
                  AND TABLE_SCHEMA = DATABASE()),
        'ALTER TABLE zsjy_enrollment_info CHANGE COLUMN collage_id college_id varchar(20) COMMENT \'学院代码\'',
        'SELECT \'无需修改collage_id字段\' AS message'
    )
);

PREPARE stmt FROM @rename_collage_id_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 第四步：如果不存在college_name，则添加该字段
SET @add_college_name_sql = (
    SELECT IF(
        NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'zsjy_enrollment_info'
                  AND COLUMN_NAME = 'college_name'
                  AND TABLE_SCHEMA = DATABASE()),
        'ALTER TABLE zsjy_enrollment_info ADD COLUMN college_name varchar(100) COMMENT \'学院名称\'',
        'SELECT \'college_name字段已存在\' AS message'
    )
);

PREPARE stmt FROM @add_college_name_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 第五步：如果不存在college_id，则添加该字段
SET @add_college_id_sql = (
    SELECT IF(
        NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'zsjy_enrollment_info'
                  AND COLUMN_NAME = 'college_id'
                  AND TABLE_SCHEMA = DATABASE()),
        'ALTER TABLE zsjy_enrollment_info ADD COLUMN college_id varchar(20) COMMENT \'学院代码\'',
        'SELECT \'college_id字段已存在\' AS message'
    )
);

PREPARE stmt FROM @add_college_id_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT '学院字段拼写修复完成!' AS result;