schoolDB四表DDL实战：从设计到避坑的完整拆解

做后端开发的尤其是刚学Spring Boot加MyBatis这套东西的同学十有八九都写过校园管理系统练手。里面的数据库名字基本都叫schoolDB。今天不聊接口怎么写也不聊权限怎么配就想把最难写、也最容易被糊弄过去的四张表DDL拿出来一行一行过一遍。很多人建表就是一把梭想到哪个字段写哪个字段结果表是建出来了后面联表查询、加索引、改字段类型各种问题全冒出来。这篇博文就把schoolDB里最核心的四张表——学生表、教师表、课程表、成绩表——从设计思路到最终DDL语句完整拆解一遍加上我踩过的坑和排查经验。适合正在学SQL、准备面试、或者项目里数据库设计总被DBA怼的Java后端新手看也适合想把手写DDL功底补扎实的人。1. 先搞清楚DDL和DML避免从一开始就走偏1.1 schoolDB是什么四张表要解决什么问题schoolDB是典型的学校管理业务库。为什么会选这种业务来练手因为它的数据模型足够经典天然包含一对多和多对多关系一个教师可以教多门课程一个学生可以选多门课程每选一门课就产生一条成绩记录。这个边界想清楚之后四张表的职责就清晰了student学生基础信息是业务里的主体。teacher教师基础信息课程表的依赖方。course课程信息通过teacher_id关联教师。score学生与课程的成绩关联表也是典型的多对多拆解表。其中score表是整个模型的点睛之笔。如果只有学生和课程两张表你是没法记录“张三的Java成绩是88分”这种信息的因为多对多关系里天然缺一个“关系本身带属性”的载体。把关系拆成一张独立表才能真正承载成绩、考试时间、补考标记这些业务数据。1.2 DDL和DML的区别一句话讲透这不是什么高深概念但很多人真的会搞混。我见过有人问“TRUNCATE是不是DELETE”也见过有人把ALTER TABLE和UPDATE混在一起理解。这里直接把分界线划清楚DDL操作的是结构DML操作的是数据。DDLData Definition LanguageCREATE、ALTER、DROP、TRUNCATE。所有改变表结构、库结构、索引结构的语句都算。DMLData Manipulation LanguageINSERT、UPDATE、DELETE、SELECT。所有操作数据行内容、查询数据的语句都算。这里最容易被坑的是TRUNCATE。它看着像DML因为执行完表里的数据没了但它本质是DDL是直接重建表结构来达到清空效果的。所以TRUNCATE无法加WHERE条件也不走事务回滚MySQL里如果使用事务引擎实际行为有点复杂但设计语义上它不逐行删除。这个点在面试里经常被用来区分候选人是不是真懂SQL语义。还有一点平时我们用MyBatis写mapper里面全是INSERT、UPDATE、SELECT那是DML操作。而MyBatis Plus 3.5.3之后推出的“实体类自动建表”功能把CREATE TABLE搬到了Java注解里这其实就是在用代码写DDL。所以别被工具绕晕底层建表这个动作永远属于DDL范畴。2. 四张表的ER设计与DDL总体思路2.1 关系建模为什么是四张表而不是三张或五张这个问题值得想清楚。很多人拿到schoolDB题目第一反应是学生表、课程表就完了加上教师表也正常但最后一查成绩发现没法关联“某个学生选了某门课”。于是又临时加一张score表还叫student_course表字段随手写course_id和student_id完全没有考虑能不能存进考试成绩。其实从一开始画ER图就应该明确teacher到course是一对多一个教师教多门课课程表用teacher_id作为外键。student到score是一对多一个学生有多条成绩记录。course到score是一对多一门课程被多个学生选修产生多条成绩记录。student和course之间是多对多通过score表拆解成两个一对多。score表承担的是“关系表”角色但它和纯粹的关系表不一样它带着成绩、考试时间这类附加值。所以建模的时候不要把它当成工具表来看它就是业务表。字段设计要围绕成绩记录来展开而不是两个外键加个主键就完事。2.2 建表之前必须想清楚的设计决策这部分是我最想强调的。DDL不只是写代码它更像是在做一组设计决策。以下是schoolDB四个表在动手前我建议你先回答的问题第一个决策主键用自增ID还是用业务字段。我见过有人用学号当学生表主键用课程编号当课程表主键。短期内没问题但学号会被重新编排课程编号也可能因为培养方案调整而变化。一旦业务主键变了所有关联表都跟着遭殃。所以这里统一用BIGINT UNSIGNED自增ID作为代理主键学号、工号、课程编号全部作为普通唯一字段存在。第二个决策字符集用utf8mb4还是utf8。MySQL里的utf8最多只能存3个字节像emoji和一些生僻字直接存不进去。schoolDB虽然是教学项目但保不齐哪天学生姓名里有生僻字所以字符集直接上utf8mb4排序规则用utf8mb4_general_ci这是目前最稳妥的组合。第三个决策外键加还是不加。生产环境中很多团队刻意不用物理外键只做逻辑关联原因后面细说。但在练习项目里我建议你把外键加上让数据库自己维护约束这样你能直观感受到约束的作用以后去生产环境再自己决定要不要去掉物理外键。第四个决策时间字段都带上create_time和update_time。很多人建表时不加等后面写统计数据时才发现没有记录创建时间再回头补就是一次ALTER。建表时顺手把这两个字段默认值写好后面能省掉大量麻烦。3. 核心实操四张表的DDL完整实现与逐段解析下面给出四张表的完整DDL语句MySQL 8.0环境存储引擎InnoDB。我按建表顺序来写先写不依赖其他表的student和teacher再写依赖teacher的course最后写依赖student和course的score。3.1 学生表student建表语句CREATE TABLE student ( 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 1 COMMENT 性别1男 2女 0未知, birth_date DATE DEFAULT NULL COMMENT 出生日期, class_name VARCHAR(50) DEFAULT NULL COMMENT 班级名称, phone VARCHAR(20) DEFAULT NULL COMMENT 联系电话, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, enroll_date DATE DEFAULT NULL COMMENT 入学日期, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在读 0休学 2毕业, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no), KEY idx_class_name (class_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT学生表;这段DDL里我逐个说几个关键点。id字段用BIGINT UNSIGNED而不用INT有些人觉得学生表能有多大INT都够了。但我见过太多系统上线两三年后主键逼近上限的窘境而且自增ID删除后不会复用直接上BIGINT是你今天能做的最便宜的保险。gender字段用TINYINT而不是CHAR(1)存“男”/“女”也不是用ENUM枚举。TINYINT的优势在于扩展性和代码映射都方便Java里直接用Integer接收对应关系写在注释里。用ENUM的坑是后续如果加一个“保密”状态ALTER TABLE的代价远高于一个TINYINT。birth_date和enroll_date我特意用DATE类型不用VARCHAR。这是新手很容易犯的错直接用字符串存日期。后果是查询时没法用日期函数区间比较会按字典序排哪天想统计某年入学的人数你就得先写个字符串截断再CAST。日期就交给DATE时间就交给DATETIME这是最稳的。student_no加唯一索引命名uk_student_no这个命名规范很重要。约束类型缩写uk表示unique keyidx表示普通索引 字段名团队里看索引名就知道它干什么不用点开表再看。3.2 教师表teacher建表语句CREATE TABLE teacher ( 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 1 COMMENT 性别1男 2女 0未知, title VARCHAR(30) DEFAULT NULL COMMENT 职称教授、副教授、讲师等, department VARCHAR(50) DEFAULT NULL COMMENT 所属院系, phone VARCHAR(20) DEFAULT NULL COMMENT 联系电话, hire_date DATE DEFAULT NULL COMMENT 入职日期, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在职 0离职, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT教师表;teacher表整体的设计思路和student表保持一致。这里重点说两个地方一是title职称字段用VARCHAR(30)有人会想到用枚举但职称体系是动态变化的讲师、副教授、教授、研究员、助教每个学校叫法还不一样枚举值写死会让后续维护成本陡增VARCHAR配合业务层校验最灵活。二是department院系字段加了普通索引。实际业务中你经常会按院系统计教师人数或者按院系筛选教师列表这个字段非常容易被当作查询条件。给查询频繁的字段加索引是性价比很高的操作但也不用每个字段都加像phone这种几乎不会单独作为筛选项的字段就不需要索引。一张表的索引不是越多越好索引会占用空间还会拖慢写入速度所以只在查询热点上加。3.3 课程表course建表语句CREATE TABLE course ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, course_no VARCHAR(20) NOT NULL COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT 学分, hours INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 总学时, teacher_id BIGINT UNSIGNED DEFAULT NULL COMMENT 授课教师ID关联teacher表, course_type TINYINT NOT NULL DEFAULT 1 COMMENT 课程类型1必修 2选修 3实践, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time 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), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT课程表;credit字段这里要注意很多初学者会用FLOAT或者DOUBLE来存学分。这是一个经典错误。浮点数在计算机里是近似存储的0.1加0.2在二进制里会产生误差学分这种和数值计算强相关的字段必须用定点数DECIMAL。DECIMAL(3,1)表示总共3位有效数字1位小数最大能存99.9对学分来说完全够用。凡是涉及金额、学分、百分比这种对精度有严格要求的数值统统用DECIMAL这个习惯越早养成越好。teacher_id字段设计上允许为NULL意思是“课程可以暂时没有指定授课教师”这对排课业务是合理的。然后这里加了物理外键约束命名fk_course_teacher。外键命名按fk_子表名_父表名的规范来这样在报错信息里你一眼就能看出是哪个约束在起作用。InnoDB引擎里创建外键时如果关联列上没有索引MySQL会自动创建索引。但这里我显式写了KEY idx_teacher_id这并多余因为未来如果要去掉物理外键逻辑外键的查询性能依然有索引兜底。3.4 成绩表score建表语句CREATE TABLE score ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, student_id BIGINT UNSIGNED NOT NULL COMMENT 学生ID关联student表, course_id BIGINT UNSIGNED NOT NULL COMMENT 课程ID关联course表, score DECIMAL(5,2) DEFAULT NULL COMMENT 成绩如85.50, exam_time DATETIME DEFAULT NULL COMMENT 考试时间, remark VARCHAR(255) DEFAULT NULL COMMENT 备注, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_student_course (student_id, course_id), KEY idx_course_id (course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student (id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT成绩表;score表是整个schoolDB里最考验基本功的一张表。首先看联合唯一索引uk_student_course它约束了(student_id, course_id)的组合不能重复也就是说同一个人同一门课只能存在一条成绩记录。这个约束非常重要没有它业务层一个bug就能导致同一个人同一门课出现两条成绩查询时还得额外处理重复数据。score成绩字段用DECIMAL(5,2)最大能存999.99允许为NULL。允许NULL是有意为之因为缺考和0分有本质区别。如果默认给0你查询“所有参加了考试的人”时会把缺考的人也算进去。用NULL表示“未录入/缺考”业务层处理逻辑就非常清晰成绩为空的是缺考成绩为0的是真考了0分。外键指向student和course这是score表的核心依赖关系。注意这里我并没有加ON DELETE CASCADE而是保持默认的RESTRICT行为。为什么因为成绩是敏感数据如果因为删掉一个学生就级联删除所有成绩记录这个操作在真实业务里太危险了。删除学生前应该先单独处理成绩表里的数据做备份或转移再删父表记录。默认约束就是逼你想清楚删除逻辑而不是让数据库在背后偷偷帮你干了活。4. 建表之外索引、约束与MyBatis Plus自动建表玩法4.1 索引和外键的实战取舍建议DDL写完之后很多人以为建表工作就算结束了。实际上不是这样。索引和约束的设计需要在建表之初就想到因为它们决定了未来几年这张表的查询性能和写入成本。索引这块我建议遵循一个路子唯一约束和主键自动带索引不需要再多想普通索引只加在查询热点上。怎么判断查询热点对schoolDB来说学生表按班级查教师表按院系查课程表按教师查成绩表按课程查这些都是高频查询路径所以索引就该建在这几个字段上。反过来像student表的email字段虽然偶尔也查但这类场景完全可以先做联合查询过滤所以不建独立索引也没关系。外键这块我前面建议在练习项目里加上物理外键但到了真实生产环境你要认真考虑是否保留。物理外键的问题在于第一每次插入和更新都要做额外的一致性检查高并发下性能有损耗第二一旦系统做了分库分表外键约束基本就失效了第三物理外键让数据迁移、批量导入数据时很痛苦。这就是为什么很多大厂代码规范里明确禁止使用物理外键全部用逻辑外键配合业务层校验。所以理解外键的原理和使用场景比无脑外键更重要。4.2 MyBatis Plus能自动建表但你还是要懂原生DDLMyBatis Plus在3.5.3版本后支持了一种DDL玩法通过实体类上的注解让代码在启动时自动生成表结构。简单说就是你定义一个Java类字段上加TableField等注解然后调用DDL相关API框架就能帮你拼出CREATE TABLE语句并执行。这个东西在开发环境快速原型阶段确实很香省得自己写SQL脚本。但我的观点很明确原生DDL你必须先掌握。自动建表能做的场景比较有限字段注释、索引命名、外键策略这些细节很难通过注解完全表达清楚而且一旦表结构要调整自动建表工具未必能做出正确的ALTER操作。我的建议是在正式项目里DDL脚本永远以手写SQL管理放进版本控制走数据库变更流程。MyBatis Plus的自动建表功能适合本地联调、快速起一个临时库、或者写自动化测试时用。工具是加分项手写能力是保底项两者不可偏废。5. 常见问题排查与避坑实录5.1 字段类型选错后面哭了都来不及我见过最典型的失误有两个。第一个是用INT存手机号然后把手机号当成数字处理。手机号在Java里是String在数据库里应该用VARCHAR否则超过2^31就会溢出报错而且用户手机号可能包含特殊符号比如86这类前缀INT直接存不了。第二个是用DOUBLE存金额或成绩然后把各个分数一累加发现出现莫名的小数位误差。这两个问题在建表时多花两分钟选对类型就能完全避免。建表时还容易踩一个坑把字段长度定得太小家子气。比如name字段只给20个字符遇到“阿不都热合曼·买买提”就直接傻了VARCHAR(255)是MySQL里一个比较典型的边界值对姓名、课程名称这类字段给到50到100基本不会错。在建表阶段把长度放宽三到五倍成本几乎为零后面数据跑起来再改长度就要做在线DDL风险完全不同。5.2 字符集排序规则与外键操作里的冷门坑如果两张关联表的字符集或排序规则不一致联表查询时索引可能会失效甚至直接报“Illegal mix of collations”错误。所以建库时就要统一设置字符集建表时不要让MySQL用默认隐式配置。外键操作里最容易犯的错是删除顺序。假设你要删掉一门课程而这门课在score表里有成绩记录直接用DELETE FROM course WHERE id ?会报外键约束错误。正确顺序是先处理score表里的关联记录再删course表。这其实也是外键存在的意义之一它逼着你理清数据的依赖关系。在真正理解这些机制之后你再去决定是否用物理外键才是有经验的判断。5.3 MySQL 8.0与老版本DDL的语法差异现在新版的服务端基本都装MySQL 8.0但很多人的学习资料和习惯还是MySQL 5.7时代的。这里有一个关键差异MySQL 8.0默认字符集是utf8mb4而5.7默认是latin1如果你用5.7的库没指定字符集中文数据会有各种奇奇怪怪的问题。另一个变化是MySQL 8.0的sql_mode默认更严格比如开启了only_full_group_by以前那种select student_id, score from score group by course_id的写法在新版里直接报错因为它不符合SQL标准。还有一个很小但很实用的点CHECK约束。MySQL 5.7里CHECK约束写了会被忽略掉而8.0才开始真正支持。如果你在5.7上写过CHECK (score 0 AND score 100)千万不能以为数据库真的帮你校验了那只是形同虚设。到了8.0版本你可以放心使用CHECK但这个约束对MySQL本身而言依然是执行效率不高的东西重要约束我还是建议放到唯一索引和应用层双重保障。最后再分享一个我自己的小习惯。写完一套DDL之后用mysqldump只导表结构出来命令大概是mysqldump -u root -p -d schoolDB。然后打开导出的SQL文件和自己的脚本对照一遍。DBA写的脚本是什么风格索引怎么命名注释怎么写字段怎么排版对照几套之后你自然就会形成肌肉记忆。这套schoolDB的DDL我自己写写改改也不下十遍每一次重构都是对表结构和业务关系的一次重新理解这个功夫值得下。