很多人一听SchoolDB对应的DDL就觉得是学生作业实际上这种只有四张表的结构脚本在真实项目里的出场率比想象中高得多新系统立项做原型验证、培训环境初始化、把老库的表结构对齐到测试环境甚至写技术文档配图都离不开一份干净的、只包含结构的DDL。最近后台也一直收到类似的搜索比如神通数据库DBStudio工具怎么只备份表结构Navicat怎么把表结构导出为表格这些问题其实都在问同一件事一份能完整还原建表信息的仅结构脚本到底该怎么准备、怎么导出、怎么验证。那就以SchoolDB为例把学生、课程、教师、选课这四张表的DDL从设计到落地完整过一遍字段类型怎么定、索引怎么建、外键到底要不要工具导出的脚本哪些能留哪些必须删我在实际初始化和迁移过程中踩过的坑也一并写出来。这篇内容适合刚入门的同学直接抄作业也适合做后端开发或数据库维护的朋友拿来当表结构评审的参考。1. 学校数据库的表边界为什么定成4张而不是更多1.1 先看业务关系再看字段清单SchoolDB这个名字经常出现在课程设计、培训班作业和中小型系统的原型里。四张表听上去简单但业务关系其实很明确学生要选课课程要有教师负责选完课之后还得记录成绩。从ER模型来看学生和课程是多对多关系多对多在关系型数据库里不能直接塞进一个字段必须拆出一张中间表。于是这四张表的自然分工就出来了student学生基本信息course课程基本信息teacher教师基本信息course_selection学生选课与成绩记录课程和教师的关系是一对多一门课有一个主讲的教师一个教师可以教多门课程所以course表里用一个teacher_id字段来引用教师。没有单独建教师授课表是因为四张表的场景里只要拿到课程就能知道是谁教的反过来按教师查课程只需要对course.teacher_id做一次过滤性能在中小规模下完全够用。这是典型的根据范围做取舍不是设计缺陷。1.2 为什么学号、工号、课程编号不能直接当主键这是我在评审别人的建表脚本时最常看到的问题有人喜欢把学号直接设为PRIMARY KEY理由是这个字段本身唯一。表面上没毛病但实际业务里学号是业务标识不是存储标识。学生在校期间学号一般不变但万一遇到转专业重编学号、数据合并、系统对接后需要修正学号主键一旦变动所有关联表的外键都要跟着改代价非常大。用自增ID做主键学号只是加了一个UNIQUE约束改学号时只需要更新学生表这一行关联关系完全不受影响。同理teacher表的工号和course表的课程编号也都保留UNIQUE约束但主键用自增ID。四张表统一这一套逻辑之后后面不管加日志表还是扩展其他业务写法都是连贯的。2. 四份建表语句从DDL反推业务规则下面的DDL以MySQL 8.0为基准同时兼容5.7。如果你后续要切到PostgreSQL或达梦、神通这类国产库我单独在第3.5节里说方言差异。2.1 student学生表基础信息别乱加冗余登记字段CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 学生主键ID, stu_no VARCHAR(20) NOT NULL COMMENT 学号, stu_name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender TINYINT NOT NULL DEFAULT 2 COMMENT 性别0-女1-男2-未提供, birth_date DATE DEFAULT NULL COMMENT 出生日期, enroll_date DATE NOT NULL COMMENT 入学日期, phone VARCHAR(20) 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_stu_no (stu_no), KEY idx_enroll_date (enroll_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT学生表;逐字段说几个关键点id INT UNSIGNED主键用无符号整数比普通INT的取值范围大一倍避免以后数据量上来以后INT有符号上限不够用的尴尬。加UNSIGNED就是主动声明这个字段不允许负数语义上更适合主键。stu_no VARCHAR(20)学号用字符串不用数字因为学号可能带字母或前导零如果按数字存20250101和20250101在导入Excel时会出现各种奇怪问题。gender TINYINT性别不要用CHAR(2)存男女编码值更稳中文字段值在跨系统对接时会遇到编码不一致问题。status TINYINT DEFAULT 1学籍状态用0/1表示离校/在读这个字段在实际查询中出现频率极高所以单独提出来不加索引都没关系但必须要有。create_time和update_time两个字段是审计标配UPDATE CURRENT_TIMESTAMP的写法让更新行时自动刷新时间省掉Java端手动setTime。KEY idx_enroll_date这个索引是我故意加的按入学年份统计在校生数量是学校系统里非常常见的报表查询这个索引能让范围查询走索引而不是全表扫描。如果你确定系统没有这类统计需求这个索引可以删不要盲目追求每个字段都建索引。2.2 course课程表学分和学时的类型很容易选错CREATE TABLE course ( id INT 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 2.0 COMMENT 学分如2.0、3.5, course_hours SMALLINT UNSIGNED NOT NULL DEFAULT 32 COMMENT 计划学时, teacher_id INT UNSIGNED DEFAULT NULL COMMENT 主讲教师ID对应teacher.id, term VARCHAR(20) DEFAULT NULL COMMENT 计划开课学期如2025-2026-1, 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_course_no (course_no), KEY idx_teacher_id (teacher_id), KEY idx_term (term) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT课程表;学分用DECIMAL(3,1)意思是总长度3位小数点后1位像2.0、3.5、4.5都能存最大99.9。这里不要用FLOATFLOAT是浮点数2.0在极端运算下会存成2.0000000001这种学分这种精确数必须用定点数。学时用SMALLINT UNSIGNED就够了一门课通常不超过255个课时。teacher_id是逻辑外键我没有写成FOREIGN KEY先只加了一个普通索引。索引的作用是让SELECT * FROM course WHERE teacher_id ?或者JOIN teacher的时候能快速定位。至于为什么不用物理外键第3.1节专门展开说。course_name没有建索引因为课程名称一般不带精确匹配查询更多是LIKE %关键词%这种查询即使建了索引也用不上纯属浪费。2.3 teacher教师表不要在四张表的模型里硬造部门表CREATE TABLE teacher ( id INT 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 2 COMMENT 性别0-女1-男2-未提供, title VARCHAR(20) DEFAULT NULL COMMENT 职称教授/副教授/讲师等, dept_name 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT教师表;教师的职称字段用VARCHAR而不是ENUM是我反复权衡后的决定。ENUM看上去省空间、约束强但改一个选项就要ALTER TABLE比如原来只有教授/副教授/讲师后来要加助教数据库就得锁表重建麻烦远大于收益。VARCHAR(20)配合应用端校验既灵活又够用。dept_name直接存院系名称不单独建学院表这在四表范围内是合理的。如果你后续要按学院统计教师数、按学院排课必须拆出独立的部门表但是当前DDL就严格解决学生-课程-教师-选课这条主线不做超范围设计。2.4 course_selection选课表成绩字段必须允许NULLCREATE TABLE course_selection ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 选课记录主键ID, student_id INT UNSIGNED NOT NULL COMMENT 学生ID对应student.id, course_id INT UNSIGNED NOT NULL COMMENT 课程ID对应course.id, term VARCHAR(20) NOT NULL COMMENT 选课学期如2025-2026-1, score DECIMAL(5,2) DEFAULT NULL COMMENT 成绩未录入时为NULL实际范围一般0-100, score_level VARCHAR(10) DEFAULT NULL COMMENT 等级制成绩如优秀/良好/及格/不及格, select_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 选课时间, PRIMARY KEY (id), UNIQUE KEY uk_stu_course_term (student_id, course_id, term), KEY idx_course_id (course_id), KEY idx_term (term) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT选课成绩表;这条表是整个SchoolDB里业务语义最重的一张。score DECIMAL(5,2)定义为可空。这绝对不是疏忽学生选课之后成绩还没出来此时成绩字段就是没有值的状态NULL语义上表示尚未录入如果用0表示查询成绩为0和还没成绩就会混在一起。等成绩录入后再UPDATE。score_level是等级制字段和百分制分数并存很多教务系统两套成绩单都要能出。VARCHAR(10)而不是VARCHAR(2)因为不及格是三个汉字VARCHAR(2)会截断这个坑我见人踩过好多回。选课表的双保险是UNIQUE KEY uk_stu_course_term (student_id, course_id, term)。这一组合索引防止同一学生同一学期重复选同一门课。如果这里没有唯一约束光靠应用端判断高并发下几乎必然出现重复记录。select_time存在这里而不是用student表的create_time是为了记录什么时候选的课这个信息和什么时候缴费什么时候退课一样是独立的业务流程节点不该混在基础表里。3. 索引、外键、默认值与字符集这些结构比字段本身更能害人3.1 物理外键和逻辑外键我推荐逻辑外键但要接受两个后果物理外键就是直接写在DDL里的FOREIGN KEY (student_id) REFERENCES student(id)。它的优点是数据库层面强制引用完整性删不掉被引用的学生记录。听起来很好但实际项目里我大多数时候选择逻辑外键也就是不写外键约束只建普通索引。原因有三第一SchoolDB这种场景里选课表会保留大量历史选课数据如果学生毕业被删或者课程因排课错误被清理物理外键会让你先处理一堆关联记录一个不小心就报外键冲突错误。第二导入数据时物理外键对顺序极其敏感必须先导学生、教师、课程最后才能导选课批量初始化数据时非常痛苦。第三分布式或分库场景下物理外键基本不可用。接受逻辑外键就意味着你必须在应用层保证student_id、course_id、teacher_id传进来的一定是真实存在的ID。说白了把完整性托付给程序。我的习惯是表结构文档里注明字段的引用关系比如course.teacher_id逻辑引用teacher.id并在接口层做存在性校验。如果这是课程作业又明确要求外键那就按需求来。真要用物理外键DDL顺序就必须先建student、teacher再建course最后course_selection否则建表阶段就会报ERROR 1215 (HY000): Cannot add foreign key constraint这种错误。3.2 组合索引和单列索引的取舍course_selection表里有三个索引UNIQUE KEY uk_stu_course_term (student_id, course_id, term), KEY idx_course_id (course_id), KEY idx_term (term)很多人不理解已经有了以student_id开头的组合索引为什么还要单独给course_id建索引这是因为最左前缀原则组合索引(student_id, course_id, term)能高效支持按学生查询、按学生课程查询但是按课程反查选课名单时这个组合索引帮不上忙必须单独建idx_course_id。经常按学期筛选数据所以也给term单独建了索引。反过来course表里的idx_teacher_id和idx_term这两个索引也要控制好。索引不是越多越好每个索引都会拖慢INSERT和UPDATE的速度因为它们需要同步维护B树。建索引的原则是先感受实际查询再决定加不加。3.3 字符集直接决定你会不会看到乱码DDL里写DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci很多人觉得这是默认配置偷懒。实际上MySQL的utf8字符集最多只能存3个字节像这种生僻字或者部分emoji符号会直接报错或变成乱码。utf8mb4才是完整4字节字符集现在新建表就没有理由再选utf8。排序规则里的_general_ci表示不区分大小写的通用排序对拼音和英文排序足够。如果你要精确处理中文拼音排序、大小写敏感可以考虑utf8mb4_0900_ai_ci或utf8mb4_bin但一般业务用不上。3.4 同一套SchoolDB结构换到其他数据库方言时要改什么很多人写完MySQL的DDL想在PostgreSQL或者神通数据库、达梦上跑一遍复现。各数据库的建表语法差异比想象中大我列一个最常用的对照表能力MySQLPostgreSQLSQL Server神通数据库自增主键AUTO_INCREMENTGENERATED ALWAYS AS IDENTITY / SERIALIDENTITY(1,1)序列触发器或内置自增字段表注释COMMENT注释COMMENT ON TABLE 表名 IS 注释MS_Description扩展属性COMMENT ON TABLE 表名 IS 注释列注释COMMENT 注释COMMENT ON COLUMN 表名.列名 IS 注释MS_Description扩展属性COMMENT ON COLUMN 表名.列名 IS 注释字符串类型VARCHAR(n)VARCHAR(n)VARCHAR(n)/NVARCHAR(n)VARCHAR(n)/VARCHAR2(n)布尔表示TINYINT(1)BOOLEANBITNUMBER(1)日期时间默认DEFAULT CURRENT_TIMESTAMPDEFAULT CURRENT_TIMESTAMPDEFAULT GETDATE()DEFAULT SYSDATE / CURRENT_TIMESTAMP比如把id INT UNSIGNED NOT NULL AUTO_INCREMENT拿到PostgreSQL里直接改成id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY拿到Oracle体系里要么用12c以后的IDENTITY要么用序列套触发器。注释语法更是完全不同。所以不要指望一份MySQL的建表脚本能在所有数据库里零修改跑通。4. 三种只要表结构的导出方式DBStudio、Navicat、手工SQL4.1 Navicat里怎么只导出结构Navicat导出表结构有两种常见做法。第一种右键数据库选择导出SQL文件不同版本可能叫转储SQL文件在弹出的窗口里对象类型勾选表然后在数据区域明确选择仅结构。这样生成的.sql文件里全部是CREATE TABLE语句不含任何INSERT数据。第二种使用工具菜单里的结构同步。源数据库和目标数据库都选同一个库点对比Navicat会生成一段ALTER TABLE脚本。这个做法更适合把本地库的表结构变更同步到测试库因为生成的就是增量修改语句。无论哪一种导出的脚本里经常带着原库的AUTO_INCREMENTxxxx和DEFAULT CHARSET等附加信息。这些信息在目标库上不一定需要尤其是自增值从原始库带过来会导致新库插入数据时跳号建议导出后检查一下把AUTO_INCREMENT那一段删掉。4.2 神通数据库DBStudio工具怎么只备份/导出表结构神通数据库是国内知名的数据库产品DBStudio是它的图形化管理工具。不同版本菜单名略有差异但操作逻辑是通用的在左侧对象导航树里找到目标数据库展开表节点选中要导出的表右键菜单里找生成DDL或者建表脚本之类选项。部分版本在备份数据库向导里会有一个是否备份数据的开关只勾选备份结构备份出来的脚本就只有DDL。如果右键菜单没找到生成DDL也可以用工具里自带的查询分析器执行一条系统表查询把建表语句拼出来。这类数据库通常有类似Oracle的USER_TAB_COLUMNS或DBMS_METADATA.GET_DDL的系统视图/函数。具体函数名每个版本不一样建议直接看产品手册里关于元数据视图的章节。需要提醒的是工具自动生成的DDL往往夹带很多系统内部参数比如物理存储路径、表空间名称、分区参数。这类信息在备份恢复场景是必需的但如果你想拿它到另一个环境做结构初始化必须清理掉表空间和路径相关的子句否则换个环境执行就报错。4.3 把表结构导出成可读的表格文档网上搜Navicat怎么把表结构导出为表格通常是希望把字段名、类型、是否为空、注释这些信息整理成Excel或Word形式的表结构说明书。Navicat本身没有一键导出Word的功能但可以用元数据查询把结果拿出来SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_COMMENT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA schooldb ORDER BY TABLE_NAME, ORDINAL_POSITION;把查询结果复制到Excel或者用Navicat的导出向导导出成CSV再在Excel里做格式调整。这样做出来的表结构文档比截屏清晰得多也方便交给产品经理或甲方评审。如果还想更省事可以直接在Navicat里右键表选择设计表把底部各字段信息复制出来但多张表一起导出时还是元数据查询最高效。5. 执行DDL的先后顺序与交付前的验证环节5.1 为什么我建议你保留一套可重复执行的初始化脚本SchoolDB这种小型数据库交付时通常要给别人在干净环境里一键建出来。我习惯在DDL文件开头放一段DROP语句DROP TABLE IF EXISTS course_selection; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS teacher; DROP TABLE IF EXISTS student;然后再放CREATE TABLE顺序是student、teacher、course、course_selection。这里有个细节先DROP子表再DROP父表。选课表依赖课程和学生课程依赖教师所以删除顺序是先删选课再删课程再删教师和学生。如果顺序反过来在物理外键存在时连DROP都会报外键错误。虽然前面推荐了逻辑外键但保留这套顺序能让你以后随时把脚本改成物理外键版本也是好习惯。5.2 建表之后不要急着结束跑一遍验证建完表我会执行几条快速校验SHOW CREATE TABLE student; SHOW CREATE TABLE course_selection;SHOW CREATE TABLE是MySQL返回实际建表语句最直接的方式你可以对照看有没有字段丢失、字符集是不是utf8mb4、自增主键还在不在。另外建议插入一条伪造记录测试选课表唯一约束INSERT INTO course_selection (student_id, course_id, term) VALUES (1, 1, 2025-2026-1), (1, 1, 2025-2026-1);第二条应该报ERROR 1062 (23000): Duplicate entry如果没报说明唯一索引没建上。测试完把数据回滚只留空表结构。5.3 注释这一关懒不得我在交付DDL时最后做的事是通读一遍所有COMMENT。因为表结构文档、接口文档、甚至自动生成代码的Swagger注释很多都依赖数据库注释来源。我见过太多系统建表时注释写得潦草三个月后自己都看不懂gender字段的0和1是什么意思。像gender TINYINT DEFAULT 2 COMMENT 性别0-女1-男2-未提供这种写法等于把枚举值也写进了注释后续维护的人不用翻代码就能明白字段语义。
