从学校库4张表设计看MySQL DDL与表结构优化实践

1. 为什么一个学校库选4张表作为核心落点

先说个我自己的习惯：接到类似 SchoolDB 这种需求时，我第一反应不是“把能想到的字段全堆上、先把几十张表建出来再说”，而是先想清楚一件事——这个库最核心的业务闭环是什么。

SchoolDB 从名字看就是一个学校管理数据库，但它没有去覆盖图书借阅、宿舍管理、食堂消费这些外围场景，而是把落点放在“教学管理”这件事上。教学管理的最小闭环其实非常固定：有学生、有老师、有课程、有选课成绩。把这四件事沉淀成四张表，整个系统最主干的数据流就通了。

很多新手容易犯一个毛病，一上来就拆表：学生基本信息一张表、学生扩展信息一张表、老师基本信息一张表、老师职称一张表、课程信息一张表、课程学时一张表……最后光基础表就十几张，关联查询写得痛不欲生，数据录入更是灾难。真正的业务开发里，表的数量从来不是越多越好，而是“足够支撑当前业务模型”就好。SchoolDB 用4张表支撑起“学生-教师-课程-选课成绩”这个模型，反而是很务实的设计。

这4张表各自的角色是这样的：

• students：学生的静态档案，学号、姓名、班级、联系方式。
• teachers：教师的静态档案，工号、姓名、院系、职称。
• courses：课程的开课信息，课程编号、名称、学分、授课教师。
• enrollments：学生与课程的关系表，同时也是成绩的载体。

你会发现，前三张表都是“实体表”，第四张表是“关系表”。实体表存“有什么”，关系表存“发生了什么事”。选课表一出来，学生和课程之间的多对多关系就被表达清楚了：一个学生可以选多门课，一门课可以被多个学生选。而教师和课程的关系，是一对多——一个老师可以教多门课，一门课在简化模型里只有一个授课老师。

需要特别强调的是，这篇内容只给表结构，也就是 DDL，不给 INSERT 数据。原因很实际：不同学校、不同项目对初始数据的要求差别太大，结构是骨架，数据是血肉，骨架定了之后接入什么血，取决于具体业务场景。给结构 + 给造数据，反而容易让接手的人产生“这些数据是必须的”的错觉。所以标题里“仅有结构”这几个字，我理解为刻意为之的边界：先把结构定清楚，再谈数据填充。

2. 四张表的完整DDL脚本与逐列设计解读

这4张表我认为最合理的落库方式是一张选课表挂三张实体表，所有表统一使用 InnoDB 引擎、utf8mb4 字符集。下面直接给完整 DDL，我用的方言是 MySQL 8.0，如果你用 5.7，除了部分带CURRENT_TIMESTAMP ON UPDATE的字段语法需要微调外，整体没有问题。

2.1 学生表 students

CREATE TABLE IF NOT EXISTS students ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', student_no VARCHAR(20) NOT NULL COMMENT '学号，业务唯一键', `name` VARCHAR(50) NOT NULL COMMENT '学生姓名', gender TINYINT NOT NULL DEFAULT 2 COMMENT '性别：0-未知 1-男 2-女', birth_date DATE DEFAULT NULL COMMENT '出生日期', class_no VARCHAR(20) NOT NULL COMMENT '班级编号，如CS2101', phone VARCHAR(20) DEFAULT NULL COMMENT '联系电话', email VARCHAR(100) DEFAULT NULL COMMENT '邮箱', address VARCHAR(255) DEFAULT NULL COMMENT '家庭住址', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-离校 1-在读', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no), KEY idx_class_no (class_no), KEY idx_name (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息表';

几个字段的考虑说明一下。

id用BIGINT UNSIGNED自增主键，这是绝大多数业务表的标准做法。可能有人问为什么不用学号直接当主键？学号是业务键，业务键是有可能变化的，比如转学、重新排号、历史数据修补，一旦业务键发生变化而它是主键，所有关联它的外键都要跟着改，代价非常大。所以主键用自增id，学号用UNIQUE KEY保证唯一，这是数据库设计的经典做法：主键不对业务开放。

gender用TINYINT加注释，而不是ENUM('男','女','未知')。原因是 ENUM 在 MySQL 里的扩展性不好，以后如果想加一个“保密”或者“其他”，ENUM 要改表结构，ALTER TABLE 的成本在数据量大时不可接受。用 TINYINT 加注释，业务层自己定义枚举映射，灵活得多。

class_no我直接放在了学生表上。有人会问：班级难道不应该单独一张表吗？如果是完整的教务系统，班级确实应该有独立表，但 SchoolDB 的定位是核心闭环，班级只作为一个字符串属性存在，已经足够支撑“按班级查学生”这个最常见的查询。真要扩展，第5张表加班级表也不迟。

status字段是关键，它支持了“学生离校但历史选课记录不删除”的场景。这是软删除思路的变体——不是物理删除记录，而是用状态字段控制可见性。

2.2 教师表 teachers

CREATE TABLE IF NOT EXISTS teachers ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', teacher_no VARCHAR(20) NOT NULL COMMENT '教师工号，业务唯一键', `name` VARCHAR(50) NOT NULL COMMENT '教师姓名', gender TINYINT NOT NULL DEFAULT 2 COMMENT '性别：0-未知 1-男 2-女', title VARCHAR(30) DEFAULT NULL COMMENT '职称：助教、讲师、副教授、教授', department VARCHAR(50) NOT NULL COMMENT '所属院系/教研室', phone VARCHAR(20) DEFAULT NULL COMMENT '联系电话', email VARCHAR(100) DEFAULT NULL COMMENT '邮箱', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-离职 1-在职', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_teacher_no (teacher_no), KEY idx_department (department), KEY idx_name (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='教师信息表';

教师表和学生表结构非常相似，这是合理的——两张表都是实体档案表，核心字段同构很正常。不同点在于教师表没有birth_date、address，但有title和department。这是因为教师的业务属性和学生不同，查询维度也不同：学生侧重班级维度，教师侧重院系和职称维度。

title我用了VARCHAR(30)而不是TINYINT。为什么这里不用枚举整数？职称的可扩展性比性别高得多，而且不同学校对职称的叫法有差异，有的叫“讲师/副教授/教授”，有的叫“一级教师/二级教师/高级教师”，用字符串反而省心，展示层直接输出，不需要再做一层类型映射。

2.3 课程表 courses

CREATE TABLE IF NOT EXISTS courses ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', course_no VARCHAR(20) NOT NULL COMMENT '课程编号，业务唯一键', course_name VARCHAR(100) NOT NULL COMMENT '课程名称', credits DECIMAL(3,1) NOT NULL DEFAULT 2.0 COMMENT '学分，如2.0、3.5', course_type TINYINT NOT NULL DEFAULT 0 COMMENT '课程类型：0-必修 1-选修', teacher_id BIGINT UNSIGNED NOT NULL COMMENT '授课教师ID，关联teachers.id', classroom VARCHAR(50) DEFAULT NULL COMMENT '上课教室', capacity INT UNSIGNED NOT NULL DEFAULT 60 COMMENT '课程容量上限，用于选课校验', course_time VARCHAR(100) DEFAULT NULL COMMENT '上课时间描述，如周一3-4节', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0-停开 1-开设', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_course_no (course_no), KEY idx_teacher_id (teacher_id), KEY idx_course_name (course_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程信息表';

课程表有意思的地方在于teacher_id这个外键字段。它是一对多关系的“多”方持有方：一门课对应一个老师，所以在课程表上放teacher_id指向教师表的主键。查询“某位老师这学期教了哪些课”，直接SELECT * FROM courses WHERE teacher_id = ?就能命中索引。

credits用DECIMAL(3,1)而不是FLOAT。学分有精度要求，2.0 和 2.1 不能出现 2.00000001 这种浮点误差。DECIMAL 是定点数，存储和计算都是精确的，财务和学分这类数据必须用 DECIMAL。

capacity是一个容易被人忽略的字段。有它在，选课业务才能做“名额校验”：选了这门课之后，可以用SELECT COUNT(*) FROM enrollments WHERE course_id = ? AND status = 1对比capacity，超过就提示“课程已满”。没有这个字段，限选逻辑就只能写死在业务代码里，很不灵活。

2.4 选课与成绩表 enrollments

CREATE TABLE IF NOT EXISTS enrollments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', student_id BIGINT UNSIGNED NOT NULL COMMENT '学生ID，关联students.id', course_id BIGINT UNSIGNED NOT NULL COMMENT '课程ID，关联courses.id', score DECIMAL(5,2) DEFAULT NULL COMMENT '成绩，NULL表示未出分', semester VARCHAR(20) NOT NULL COMMENT '学期，如2025-2026-1', status TINYINT NOT NULL DEFAULT 1 COMMENT '选课状态：0-退选 1-在修 2-已结课', enroll_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_student_course_semester (student_id, course_id, semester), KEY idx_course_id (course_id), KEY idx_semester (semester) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生选课与成绩表';

这张表是整个 SchoolDB 里真正的“心跳”。它把学生、课程、学期串在了一起。

score允许 NULL 是有意为之：学生刚选课的时候还没考试，成绩不存在，用 NULL 表示“未出分”比用 0 更准确。等录入成绩之后再更新。如果硬要用 0 表示未出分，统计平均分的时候还得WHERE score > 0，徒增麻烦，而且 0 分的同学会被错误排除。

semester字段非常关键。如果不带学期字段，选课表就只能记录“某个学生选了某门课”，一旦下学期这门课继续开设，同一个学生再次选课，唯一键就冲突了。带上(student_id, course_id, semester)联合唯一键之后，学生同一学期选同一门课只能有一条记录，跨学期则可以重复选，这完全符合真实教务场景。

status在这里的三个取值也值得琢磨：0-退选、1-在修、2-已结课。退选不是删除记录，而是标记状态，这样保留了完整的选课历史。比如之后要统计调课率、退课率，直接对状态做聚合就行。

3. DDL里的隐性决策：外键、索引与存储参数

一个只有 CREATE TABLE 的脚本看起来简单，但每一行其实都是一个决策。这一节讲几个 DDL 背后容易被忽略的选择，以及我为什么这样定。

3.1 为什么全部用逻辑外键，而不是物理外键

细心的读者会发现，我的 DDL 里没有出现一行FOREIGN KEY。courses.teacher_id、enrollments.student_id、enrollments.course_id都只是普通字段，靠索引和业务代码保证一致性。这是故意为之。

物理外键（数据库层面的 FOREIGN KEY 约束）的好处是数据一致性由数据库强制保证，但这个项目级别的代价是：

• 写入性能下降。每次插入子表记录，数据库都要去父表做存在性检查，这个检查是行锁级别的，高并发写入时会成为瓶颈。
• 数据迁移非常痛苦。做数据清洗、分库分表、从 MySQL 迁移到其他数据库时，物理外键会变成巨大的阻碍，你得先禁用约束再迁移，再重建约束。
• 删除和更新会被约束捆绑。比如要清理一条教师记录，如果课程表里还有引用，DELETE 直接在数据库层报错，而很多时候业务上的处理逻辑是“先停用再归档”，并不希望直接硬删。

所以我选择逻辑外键：字段语义上存在引用关系，索引上也做了支撑，但不建数据库层面的 FOREIGN KEY。数据完整性交给了业务层去控制，方法是事务 + 查询校验。以选课为例，业务上插入 enrollments 之前先确认 student_id 和 course_id 存在且状态正常，再 INSERT，这是在业务事务里完成的，一样能保证一致性，而且灵活得多。

如果你所在的项目规模很小、并发不高、开发团队不大，建物理外键能省心不少，数据库帮你看着数据，总好过业务层漏检。所以这里给一个补充写法，真想加物理外键的话，在 CREATE TABLE 末尾加：

CONSTRAINT fk_courses_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id), CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES students(id), CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses(id)

加之前确认对应的列已经有索引，InnoDB 要求外键列必须有索引，没有的话会自动帮你创建一个，等于白送一个你可能不需要的索引。

3.2 索引不是越多越好，每一把“刀”都要有用

我在这4张表里一共设计了这些二级索引：

这里有一个值得讲的点：联合唯一索引uk_student_course_semester (student_id, course_id, semester)不仅仅用来防重复，因为联合索引的最左前缀原则，它同时可以作为(student_id)的单列索引使用。也就是说，我并没有给学生ID再单独建一个索引，而是让联合索引一箭双雕。同理，idx_course_id是为了支撑按课程维度查询。

很多新手喜欢把每个字段都加索引，觉得“加了总比不加好”。实际不然，索引是存储空间换取查询速度，写入时每次都要更新索引树，索引越多写入越慢。而且 MySQL 优化器在一条查询里通常只会选择少数几个索引，建一堆排不上用场的索引纯属浪费。我的原则是：先想清楚查询场景，再定索引，不为未来可能根本不存在的查询买单。

3.3 字符集、排序规则、引擎和时区

DDL 里这几行看起来像标配，但选错会埋大坑。我统一用了：

• ENGINE=InnoDB：支持事务、行级锁、崩溃恢复。事务对于选课和成绩录入至关重要。选课要保证“扣名额”和“插入选课记录”要么一起成功，要么一起失败，MyISAM 没有事务，选课场景下绝对不能碰。
• DEFAULT CHARSET=utf8mb4：utf8mb4 是完整的 UTF-8 实现，能存四字节的 emoji 和生僻字。老旧的 utf8mb3 只支持最多三字节，碰到少量生僻字直接报错。中国学生姓名里的生僻字、表情符号，都是必须用 utf8mb4 的理由。
• COLLATE=utf8mb4_unicode_ci：这个排序规则按 Unicode 标准比较字符，大小写不敏感。如果和其他表排序规则不一致，JOIN 关联字符字段时，MySQL 会因为 collation 不一致而无法直接比较，必须用COLLATE强制转换，平白多一层开销和出错点。

还有一个不在 DDL 里但直接影响 DDL 的参数：time_zone。我遇到过服务器时区是 UTC，导致NOW()取出来的时间比北京时间慢8个小时的坑。建议在建库前把 MySQL 时区显式设置为系统时区，常用方式：

SET GLOBAL time_zone = '+08:00';

或者更简单，启动参数加--default-time-zone='+08:00'。这样CURRENT_TIMESTAMP生成的时间才是符合业务预期的时间。

4. 这套结构在真实使用中会遇到的问题与应对思路

结构设计只是一个起点，真正让人成长的是把结构扔进真实业务里跑一圈之后暴露出来的问题。这一节聊聊我基于这套 DDL 结构实际跑下来的一些体会，以及后续可以怎么扩展。

4.1 软删除与状态字段的关系

在4张表里我都设计了status字段。这是一种轻量级的软删除方案。为什么不用deleted_at加is_deleted？因为 SchoolDB 这个层面的业务并不需要区分“正常数据”和“已删除数据”进行复杂审计，一个状态字段足够：学生是“在读/离校”，教师是“在职/离职”，课程是“开设/停开”，选课是“在修/退选/已结课”。

把删除降级为状态变化，最大的好处是历史数据不丢。以选课表为例，学生退课了，我不会 DELETE 这条 record，而是把 status 从1改成0。学期结束，成绩记录还在，可以随时回溯“这个学生这个学期选过哪门课，退了哪门课，最后成绩是几分”。如果直接 DELETE，这种多维度的历史分析完全无从谈起。

当然，物理删除在有些场景是必要的。如果发现选课记录是误操作产生的脏数据，要彻底清理，那可以用真正的 DELETE FROM。但日常业务操作里，尽量走状态切换，这个思维习惯建议养成。

4.2 学分与成绩字段的边界

credits DECIMAL(3,1)和score DECIMAL(5,2)这两个精度是我在实际验算后定下来的。学分上限 99.9，实际上没有任何一门课能达到这个值，但 DECIMAL(3,1) 保证了小数点后一位的精度，2.5 学分的课可以精确存储。成绩上限 999.99，百分制、150分制、五级制转换成小数，全部覆盖。

比起浮点数，DECIMAL 才是钱和分数这类精确数值的正确选择。浮点数在二进制表示下天生存在舍入误差，用它存成绩，求平均分的时候可能出现 89.999999 这种诡异结果。这个坑在我刚做数据类项目的时候踩过，从那以后所有金额、分数、学分类字段一律 DECIMAL，没有例外。

4.3 从4张表到更多表的合理扩展方向

SchoolDB 用4张表完成核心闭环，但它留了两个明显的扩展口子。

第一，当系统要支持“一个班有班主任、一个班级有独立的核心属性”时，班级就会从学生表里的字符串字段升级成独立的 classes 表。这是个很自然的演进方向：初期表单书记录，后期表独立。学生的class_no可以平滑改成class_id去关联新表，不破坏已有数据。

第二，当成绩要支持“平时分、期中分、期末分、实验分”等分项时，成绩就不能只靠在 enrollments 表上加一列 score 来解决了。常见的方案是拆一张 score_items 表，用一条记录存一个分项：

CREATE TABLE IF NOT EXISTS score_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', enrollment_id BIGINT UNSIGNED NOT NULL COMMENT '选课记录ID，关联enrollments.id', item_name VARCHAR(30) NOT NULL COMMENT '分项名称：平时、期中、期末、实验', item_score DECIMAL(5,2) NOT NULL COMMENT '分项得分', item_weight DECIMAL(3,2) NOT NULL COMMENT '分项权重，如0.4表示40%', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (id), KEY idx_enrollment_id (enrollment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成绩分项表';

总成绩由各分项合计加权求出，而不是直接存一个总分。这样做的好处是成绩可解释、可复核，也支持老师只录入平时分而期末分稍后补录的分阶段需求。这个扩展基于原表结构完全不用改动 enrollments 的既有字段，只新增一张子表即可，说明当初把score设计成可空字段的决策是正确的。

4.4 建表时顺手做掉的几件事

最后分享几个我在项目里固定会做的事，希望对你有帮助。

第一，建表之前先用CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;把库的默认字符集和排序规则定死，这样以后在这个库里建表，就算不显式写 CHARSET 和 COLLATE，也不会出现字符集不一致的情况。

第二，每一列的 COMMENT 都要写得让后来接手的人能直接看懂。gender TINYINT NOT NULL DEFAULT 2 COMMENT '性别：0-未知 1-男 2-女'和gender TINYINT NOT NULL DEFAULT 2是两种完全不同的沟通成本。COMMENT 是代码里的文档，项目交接的救星。

第三，建表之后马上用SHOW CREATE TABLE students;确认 DDL 的执行结果，而不是只看 CREATE TABLE 是否成功。SHOW CREATE TABLE 会显示 MySQL 实际执行的完整定义，包括它自动补充的存储引擎、字符集细节，有经验的人一眼就能发现问题。这个习惯帮我抓住了好几次“我以为我建的是 utf8mb4，实际上数据库给默认成了 latin1”之类的错误。

这套 SchoolDB 的4张表结构，说不上多复杂，但每一处字段、索引、默认值的设定都是基于“真实业务里会被怎么查、怎么改”推演出来的。数据库结构这种事，最怕的就是拍脑袋建表、上线之后再靠一堆 ALTER TABLE 打补丁。把结构想清楚再动手，后面能省掉很多本来不该有的麻烦。