MySQL 数据库设计实战：四张核心表的 DDL 建表语句拆解与索引外键规划

接手一个学校信息管理系统的数据库设计任务时，我最先动手的往往不是业务代码，而是那一张张建表语句。今天要拆的这份 schoolDB 对应的四个表的 DDL，就是我从实际项目里沉淀出来的最小闭环方案：学生表、教师表、课程表、选课成绩表。这份 DDL 不是教科书里的标准答案，而是照着跑就能用的实践版本，每个字段为什么要这么设计、每个约束和索引背后的理由，都会掰开讲清楚。无论你是刚接触 MySQL、想把表结构设计弄明白的同学，还是需要快速给内部系统落地表结构的后端工程师，都可以直接参考甚至照抄，再根据自己业务做增减。

先说一句总结性的话：写 DDL 之前，一定要先把业务边界想清楚。很多朋友上来就写 CREATE TABLE，字段写了一大堆，建完才发现关联关系乱了、索引没覆盖查询、外键卡住导入。下面我按自己实际建库的顺序，分五个部分把完整链路拆开讲。

1. schoolDB 四张表的业务边界划分：先建模再写语句

1.1 为什么最小闭环恰好是四张表

聊 schoolDB 的 DDL 之前，必须先回答一个问题：为什么是这四张表？

一个学校的核心业务可以拆成四个问题：谁在教？谁在学？教什么？学得怎么样？对应到实体上就是教师、学生、课程，以及学生和课程之间的选课关联。很多人设计教务系统时容易犯一个毛病——一上来就枚举一堆表：学生表、班级表、教师表、课程表、选课表、成绩表、教室表、院系表……数量翻倍，外键关系变得复杂，动一个表要连带改五六个地方。而 schoolDB 这种轻量级教学管理场景，四张表就能跑通核心闭环：students 存学生，teachers 存教师，courses 存课程，enrollments 存学生与课程的选课关系并附带成绩。

这不是偷懒，而是刻意收缩边界。schoolDB 的定位是"选课、记录成绩、查名单"，不是完整的行政人事系统。班级合并、教师调岗、教室排期这些复杂业务，在当前场景里都属于低频事件，完全可以在应用层或后续版本单独处理。一开始就把表拆得过于细致，反而会让开发效率大幅下降，因为每一张新表都意味着新增 JOIN、新增事务边界、新增数据维护成本。

1.2 实体关系梳理与字段职责划分

四个实体之间的关系其实只有一条主线：teachers 和 courses 是一对多，一个教师可以教多门课；courses 和 enrollments 是一对多，一门课有多条选课记录；students 和 enrollments 也是一对多。所以四张表里真正承担"关系"职责的是 enrollments 这张关联表，它把学生和课程的多对多关系拆成了两条一对多。

字段职责划分也遵循一条原则：一张表只描述一个实体的属性。比如 students 表里存学号、姓名、性别、出生日期、班级、入学日期这些学生固有属性；不要因为业务上"经常要按班级统计成绩"，就把班级字段塞进 enrollments 表，否则数据冗余会让后续统计口径出现偏差。为了便于对照，我把四张表的职责整理如下：

1.3 关于班级表和第三范式的取舍

严格按照第三范式来设计，班级名称应该再拆一张 class 表，然后 students 通过 class_id 去关联。但在四张表的约束下，我选择了在 students 表里直接存放 class_name。为什么敢这么设计？因为班级属于低频变化数据，即使某天班级改名，也只需要 UPDATE students 表里对应的记录，无需级联修改多张表。这也是我在实际项目中反复验证过的结论：范式和性能之间不存在绝对的对错，只有适不适合当前场景。

如果今天要做的是全校级别的教务系统，涉及班级人数统计、班主任管理、班级升迁，那必须单独建 class 表。但 schoolDB 的定位是课程管理、选课记录和成绩查询，不会出现跨表统计班级人数的复杂报表需求，所以冗余一个班级名字段，换来的是查询少一次 JOIN，性价比很高。设计 DDL 之前，把这种取舍想清楚，后续动手写语句时就不会反复摇摆。

2. 建库前的统一约定：字符集、存储引擎与命名规范

2.1 数据库级 DDL 与字符集选型

四张表的 DDL 看起来是 CREATE TABLE，但真正专业的做法是先定好数据库级别的约定。我建 schoolDB 时第一句写的是：

CREATE DATABASE IF NOT EXISTS schoolDB DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

为什么字符集必须用 utf8mb4 而不是 utf8？因为 MySQL 里的 utf8 最多支持 3 字节，存不了 emoji 和部分生僻字；学生姓名、地址这类字段完全可能包含特殊字符，万一录入了一个 4 字节的生僻字，字符集不对就会报错或者变成乱码。utf8mb4 是 utf8 的超集，兼容性更好。排序规则选择 utf8mb4_unicode_ci，是因为它基于 Unicode 的排序算法，在不同语言环境下比 utf8mb4_general_ci 更准确，虽然理论上稍微慢一点点，但对这个量级的系统毫无感知。

这里有一个坑：如果在建表时没有显式指定字符集，MySQL 会沿用数据库级别的配置，所以数据库这一层就要把 utf8mb4 定死，不要指望每张表都单独记得写。

2.2 存储引擎为什么选 InnoDB

四张表全部使用 InnoDB，这一点我在实际项目里从不妥协。虽然 MyISAM 在某些纯读场景下查询略快，但 schoolDB 涉及选课、成绩更新，写操作频繁且需要事务保障，InnoDB 的行级锁和事务支持是刚需。举个实际例子：一个学生选课的同时要往 enrollments 表插入记录，还要在业务侧扣减课程余量，如果没有事务，其中一个操作失败就会造成数据不一致。InnoDB 还得益于聚簇索引组织方式，按主键范围查询的效率很稳定。

另外，InnoDB 支持外键约束，虽然我们不一定在每个地方都用，但设计阶段保留这种能力，后续如果需要强一致性约束，不用改表引擎。MyISAM 还有一个致命问题：表级锁。一旦多个学生同时选课，写操作会串行排队，这对一个教务系统来说是难以接受的。

2.3 表名字段命名规范

命名规范是 DDL 里最容易被忽略但对后期维护影响巨大的部分。我在 schoolDB 里定了几条规矩：表名全部小写、复数形式，students、teachers、courses、enrollments 一眼能看出集合概念；字段名采用 snake_case，单词间用下划线分隔，student_id 而不是 studentId；所有主键统一带表名前缀，比如 student_id、course_id，这样在关联查询和代码映射时，字段语义不会混淆。时间字段统一成 created_at 和 updated_at，通过数据库默认值自动维护，不需要业务代码手动赋值。

这套命名规范一旦定下来，后续所有表都遵循，别人接手项目时不用猜，ORM 映射也顺滑。Java 的 MyBatis 不需要写一堆 @Column 注解去对齐，Python 的 SQLAlchemy 也能自动映射，这就是规范化的隐性收益。

3. 四张核心表的 DDL 语句逐段拆解

3.1 学生表 students

学生表的 DDL 我放在最前面写，因为它是整个系统的数据基座。实际执行的语句如下：

CREATE TABLE students ( student_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生ID，主键', student_no VARCHAR(20) NOT NULL COMMENT '学号，业务唯一标识', student_name VARCHAR(50) NOT NULL COMMENT '学生姓名', gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女', birth_date DATE NOT NULL COMMENT '出生日期', class_name VARCHAR(50) NOT NULL COMMENT '班级名称', phone VARCHAR(20) DEFAULT NULL COMMENT '联系电话，允许为空', email VARCHAR(100) DEFAULT NULL COMMENT '邮箱', address VARCHAR(255) DEFAULT NULL COMMENT '家庭住址', enrollment_date DATE NOT NULL COMMENT '入学日期', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1-在读，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 (student_id), UNIQUE KEY uk_student_no (student_no), KEY idx_class_name (class_name), KEY idx_student_name (student_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息表';

逐条说几个关键设计。student_id 用 BIGINT UNSIGNED AUTO_INCREMENT，主键自增在写入时性能好，UNSIGNED 把可用正数范围翻了一倍，对一张学生表来说完全够用。student_no 学号单独建 UNIQUE KEY，这里的逻辑是：主键是数据库内部的代理键，学号才是业务层面的唯一标识，同一学号不能出现两条记录。gender 用 TINYINT 而不是 CHAR(1) 存"男"、"女"，因为整型做条件过滤更快，也方便前后端做码表映射。

phone、email、address 全部允许 NULL，不要用空字符串去表示"没有"，NULL 和空字符串在语义上是完全不同的。status 字段虽然看起来简单，但在实际查询中出场率极高，"显示在读学生"就是在 WHERE 条件里加 status=1，所以给它建了普通索引。created_at 和 updated_at 直接用数据库默认值维护，从源头避免业务代码漏写时间字段导致的数据不完整。

3.2 教师表 teachers

教师表的 DDL 如下：

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

teachers 的字段和学生表高度对称，这其实是我刻意为之。对称的结构更容易记忆、更容易写通用代码，比如前端做列表展示时，学生列表和教师列表的接口逻辑可以大量复用。这里我想重点聊 department 字段。很多需求文档里院系会被设计成字典表，但那是针对大型系统的做法，schoolDB 场景下把 department 直接设计成 VARCHAR，配合 idx_department 索引，已经能覆盖"按院系筛选教师"的全部需求。如果你预感到未来要做院系维度的复杂报表，再升级成 department_id 也不迟，DDL 从来不是一锤子买卖。

title 职称字段我用了 VARCHAR 而不是 TINYINT 码表，因为职称体系在不同学校差异太大，助教、讲师、副教授、教授之外还可能有"特聘教授"这类自定义头衔，VARCHAR 的灵活性更高，代价是查询时需要按字符串匹配，但这个字段很少作为高频筛选条件，所以值得。

3.3 课程表 courses

课程表的 DDL 如下：

CREATE TABLE courses ( course_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程ID，主键', course_code VARCHAR(20) NOT NULL COMMENT '课程编号，如CS101', course_name VARCHAR(100) NOT NULL COMMENT '课程名称', credit DECIMAL(3,1) NOT NULL DEFAULT 2.0 COMMENT '学分', course_type TINYINT NOT NULL DEFAULT 0 COMMENT '课程类型：0-必修，1-选修', teacher_id BIGINT UNSIGNED NOT NULL COMMENT '授课教师ID，关联teachers表', semester VARCHAR(20) NOT NULL COMMENT '开课学期，如2024-2025-1', class_time VARCHAR(100) DEFAULT NULL COMMENT '上课时间描述，如周一3-4节', location VARCHAR(100) DEFAULT NULL COMMENT '上课地点', capacity INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '选课容量，0表示不限制', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1-开放，0-关闭', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (course_id), UNIQUE KEY uk_course_code (course_code), KEY idx_teacher_id (teacher_id), KEY idx_semester (semester), KEY idx_course_type (course_type), CONSTRAINT fk_courses_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程信息表';

courses 表里有一个容易被忽略但极其关键的字段组合：course_code 和 semester。单独看 course_code 唯一并没有覆盖真实业务，因为同一门课在不同学期会重复开设，比如"数据库原理"这门课，2024 年春季和 2024 年秋季都是 CS201 这个编号，但它们是两个独立的开课记录。所以严格来说，业务唯一性应该考虑复合唯一键 (course_code, semester)。我在 DDL 里保留了 uk_course_code，是假设当前 schoolDB 场景一个学期只跑一轮课程数据，如果你要支持多学期并行，建议把唯一键改成UNIQUE KEY uk_course_semester (course_code, semester)。

另外，course_type 建了索引，因为"查所有选修课"是教务管理里的高频操作。teacher_id 这里给了外键约束，目的是防止课程指向一个不存在的教师——这种业务错误一旦发生，在报表里极难排查。

3.4 选课成绩表 enrollments

选课成绩表是整个系统的核心关联表，DDL 如下：

CREATE TABLE enrollments ( enrollment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '选课记录ID，主键', student_id BIGINT UNSIGNED NOT NULL COMMENT '学生ID，关联students表', course_id BIGINT UNSIGNED NOT NULL COMMENT '课程ID，关联courses表', score DECIMAL(5,2) DEFAULT NULL COMMENT '期末成绩，百分制，NULL表示未出分', grade_point DECIMAL(3,1) DEFAULT NULL COMMENT '绩点，由成绩换算而来', status TINYINT NOT NULL DEFAULT 0 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 (enrollment_id), UNIQUE KEY uk_student_course (student_id, course_id), KEY idx_course_id (course_id), CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses(course_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='选课成绩表';

这张表是四张表里承载业务逻辑最多的地方。先说 score 字段为什么用 DECIMAL(5,2) 而不是 FLOAT。浮点数在二进制存储下天然存在精度误差，0.1+0.2 可能等于 0.30000000000000004，成绩计算、绩点换算对精度敏感，必须用定点数。DECIMAL(5,2) 表示最多 5 位数字，其中 2 位小数，最大能存 999.99，对百分制成绩来说绰绰有余。

score 为什么允许 NULL？因为选课发生在学期初，成绩出分在学期末，NULL 精准表达"还没出分"这一中间状态，比用 0 表示更符合业务语义。uk_student_course 这个复合唯一键是整张表的灵魂：它确保一个学生对同一门课最多只有一条选课记录，从数据库层面杜绝重复选课。这是一个在应用层靠代码很难 100% 做好的约束，因为并发环境下两个请求可能同时通过校验，但到了数据库层，唯一索引是原子性的，谁先提交谁成功。

4. 主键策略、索引设计与外键约束：每一个选择的理由

4.1 自增主键为什么够用，以及什么情况要换

四张表的主键全部采用 BIGINT UNSIGNED AUTO_INCREMENT，这在大多数场景下是最省心、性能也最好的选择。自增主键的好处在于：写入时主键单调递增，InnoDB 的聚簇索引可以顺序写入，避免了随机 I/O 导致的页分裂，批量导入数据时优势尤其明显。

但自增主键也有两个被说烂了的缺点：一是 id 会被遍历泄露业务量，比如从一个学生的 student_id=10086 能猜出这个系统已有一万多个学生；二是分布式场景下多个节点同时生成自增 id 会冲突。所以如果是对外开放的 SaaS 系统，或者表数据要跨库合并，我会考虑换成 UUID 或雪花 ID。但 schoolDB 定位是校内系统，数据量级最多几万行，用自增主键简单可靠，这是当前场景下的最优解。

4.2 复合唯一键与索引设计的细节

再聊聊索引设计。enrollments 表里我建了 UNIQUE KEY uk_student_course (student_id, course_id)，这个复合唯一索引本身就是一个覆盖索引，也就是说按 student_id 查某学生所有选课记录时，可以直接从索引里拿到 course_id 字段，不需要回表。这正好呼应了我在建表时的一个取舍：没有单独建 idx_student_id。因为复合索引的最左前缀规则已经覆盖了以 student_id 为条件的查询，再建一个单列索引完全是浪费空间和写入成本。

而 idx_course_id 则是必要的，因为按 course_id 查"哪些人选了这门课"同样高频，而复合索引的最左前缀从 student_id 开始，无法直接服务于 course_id 条件。这里建议大家在设计阶段就模拟两条最频繁的查询语句，看哪些索引被真正用到，避免建一堆用不上的索引拖慢写入性能。

4.3 外键约束的用与不用

schoolDB 里 courses 和 enrollments 都加了外键约束，这其实是很多人争论的点。支持不用外键的一方会说：外键在删除和更新时要检查参照完整性，影响性能，而且分布式场景外键无法跨库。我自己的看法是：在轻量级单库系统里，外键的可靠性价值远大于那点性能损耗。

外键最大的价值不是约束程序员，而是给数据兜底，它防止两类错误：一是插入了指向不存在教师或学生的记录；二是删除了仍被引用的教师或课程。这两类错误在业务代码里往往隐藏在很深的逻辑分支中，排查成本极高。当然，如果你确定以后要把系统拆成微服务、分库分表，那么外键确实会成为迁移的绊脚石，这种情况下就应该在设计阶段主动放弃外键，只在应用层做校验。这个决定没有标准答案，关键是你对系统未来演化路径要有预判。

5. 建表实操里的经典坑位与处理经验

5.1 关于 int(11) 的经典误会

第一个坑和显示宽度有关。很多老 MySQL 习惯把 id 定义成 int(11)，其中 11 代表显示宽度，不是取值范围。int 类型无论写成 int(3) 还是 int(11)，存储范围都是 -2147483648 到 2147483647，区别只在客户端展示时是否补零。所以在 MySQL 8.0 里 int(11) 这种写法已经被官方废弃，直接写 INT 就够了。

更进一步，我很推荐四张表的主键统一用 BIGINT UNSIGNED 而不是 INT，因为一旦业务增长超出 INT 上限，改主键类型既要重建索引又要处理外键约束，是一次涉及全部表的灾难性操作，与其后期折腾，不如一开始就留足余量。别觉得学生表几万条数据 INT 绰绰有余，系统要跑好几年，再加上历史归档、删改记录，膨胀速度比想象中快。

5.2 默认值、CURRENT_TIMESTAMP 与 ON UPDATE 的细节

第二个坑在时间字段的默认值。建表时我写了 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 和 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。ON UPDATE CURRENT_TIMESTAMP 的含义是：只要这行的任意字段被 UPDATE，数据库自动把 updated_at 刷新为当前时间，不需要应用程序额外赋值。

这个特性虽然方便，但也有需要注意的地方：如果某些业务场景要求"手动指定更新时间"，程序里显式传入的 updated_at 值会覆盖自动行为，一旦代码忘了传值，字段又会变成当前时间，可能掩盖真实数据变更时间。此外，MySQL 5.6 以前 DATETIME 类型不支持 DEFAULT CURRENT_TIMESTAMP，如果你维护的是老版本库，升级到 5.6 之后再执行上述 DDL 会更稳妥。

5.3 命名、外键冲突与迁移中的常见问题

命名类的问题我在实际评审中遇到过很多次。有人把表名写成 Student，有人把字段写成 userName，还有人在字段里混用中英文注释。这些命名问题带来的后果是：ORM 映射要写一堆额外注解去对齐，跨团队协作时沟通成本直线上升。

外键相关的坑更要留意。一是外键字段和关联字段的类型必须完全匹配，students 表主键是 BIGINT UNSIGNED，enrollments 表的 student_id 也必须是 BIGINT UNSIGNED，不能一个大一个小，否则建表直接报错；二是外键约束要求被关联字段必须有索引，MySQL 会自动为外键字段建索引，但如果你在已有大表上新增外键，这个自动加索引的操作会锁表，需要选择业务低峰期执行。迁移场景下还有个常见的坑：导出的 SQL 文件字符集可能在传输过程中被改写，导致中文注释变成乱码。我一般导出后用编辑器检查文件头部的 CHARSET 声明，再配合 mysql 命令的--default-character-set=utf8mb4参数导入，确保万无一失。

下面把这些常见问题的应对整理成一个速查表：

我自己在建这套 DDL 时，其实也反复改了很多轮。最开始学生表里放了整整十五个字段，连"学生血型"和"紧急联系人关系"都写进去了，后来真到跑业务才发现，一半字段从来没用过，还白白增加了每行存储的开销。所以后来我给自己定了一条规矩：每一个字段都要能回答"它支撑了哪条具体业务"，回答不上来的，先砍掉，等有需求再加。DDL 看起来只是几十行文本，但它是整个系统最早定下的技术决策之一，改表结构的成本远高于改业务代码里的一行逻辑。schoolDB 这套四表结构，胜在边界清晰、取舍明确、贴近真实使用场景，你可以把它当成起点，然后根据自己学校的业务往下扩展——比如增加班级表、教室表、教学计划表，每一层扩展都会比从零开始清晰得多。