all-project/backend/script/sql/21-add-missing-basic-fields.sql

-- 添加学生信息表缺失的基本字段
-- 时间：2025-01-16
-- 目的：修复字段映射错误

USE hzdyjs_xg;

-- 添加基本信息字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS nation VARCHAR(20) DEFAULT NULL COMMENT '民族' AFTER birth_date;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS school_system INT(2) DEFAULT 4 COMMENT '学制（年）' AFTER enrollment_date;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS training_type VARCHAR(20) DEFAULT NULL COMMENT '培养方式' AFTER education_level;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS source_region VARCHAR(100) DEFAULT NULL COMMENT '生源地区' AFTER training_type;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS middle_school VARCHAR(100) DEFAULT NULL COMMENT '毕业中学' AFTER source_region;

-- 添加状态管理字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS data_source VARCHAR(20) DEFAULT 'manual' COMMENT '数据来源：manual-手工录入，import-批量导入，sync-系统同步' AFTER info_status;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS last_batch_id BIGINT(20) DEFAULT NULL COMMENT '最后修改批次ID' AFTER data_source;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS last_audit_time DATETIME DEFAULT NULL COMMENT '最后审核时间' AFTER last_batch_id;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS is_complete TINYINT(1) DEFAULT 0 COMMENT '信息是否完整：0-不完整，1-完整' AFTER last_audit_time;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS completion_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '完整度百分比' AFTER is_complete;

-- 添加扩展字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS ext_field1 VARCHAR(200) DEFAULT NULL COMMENT '扩展字段1' AFTER completion_rate;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS ext_field2 VARCHAR(200) DEFAULT NULL COMMENT '扩展字段2' AFTER ext_field1;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS ext_field3 VARCHAR(200) DEFAULT NULL COMMENT '扩展字段3' AFTER ext_field2;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS ext_field4 VARCHAR(200) DEFAULT NULL COMMENT '扩展字段4' AFTER ext_field3;
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS ext_field5 VARCHAR(500) DEFAULT NULL COMMENT '扩展字段5' AFTER ext_field4;

-- 添加特殊情况字段
ALTER TABLE xg_student_info ADD COLUMN IF NOT EXISTS special_condition VARCHAR(200) DEFAULT NULL COMMENT '特殊体质或疾病' AFTER weight;

-- 验证字段是否正确添加
SELECT 
    COLUMN_NAME as '字段名',
    DATA_TYPE as '数据类型',
    IS_NULLABLE as '可空',
    COLUMN_DEFAULT as '默认值',
    COLUMN_COMMENT as '注释'
FROM information_schema.COLUMNS 
WHERE table_schema = DATABASE() 
AND table_name = 'xg_student_info' 
AND COLUMN_NAME IN ('nation', 'school_system', 'training_type', 'source_region', 'middle_school', 'special_condition')
ORDER BY ORDINAL_POSITION;