适用环境MySQL 8.0示例按 MySQL 8.0.39 编写1. 数据库设计数据库设计首先考虑的是以下问题业务中有哪些需要长期保存的对象每个对象应该保存哪些属性一行数据代表什么即表的粒度是什么哪些字段能够唯一标识一行对象之间是一对一、一对多还是多对多哪些数据会重复修改时是否可能产生不一致常用查询是什么需要怎样的索引1.1 类、实体与表分析层次含义学生系统示例概念类从现实业务中抽象出的对象学生、班级、课程实体需要长期保存的数据对象某个具体学生表实体在关系数据库中的存储结构student_design2属性实体具有的特征学号、姓名、出生日期列属性在表中的实现sno、name、birthday类、实体和表经常近似一一对应但不能机械地认为“一个 Java 类必须是一张表”。DTO、VO 通常不需要建表集合属性往往要设计为子表或中间表。2. 部分概念2.1 表的粒度粒度指“一行数据到底描述什么”例如成绩表的一行可以定义为一名学生对一门课程的最终成绩。于是(student_id, course_id)可以唯一确定一行。如果业务改为“一名学生同一门课程可以补考多次”原粒度就不够了应增加attempt_no或单独设计考试场次表。2.2 候选键、主键与业务键候选键能够唯一标识一行的最小字段集合。主键从候选键中选出的主要行标识。业务键业务本身具有唯一性的字段如学号、课程编号。代理键没有业务含义、专门用于标识行的字段如自增id。学生表可以使用自增id作为主键同时给sno添加唯一约束。这样连接时主键稳定数据库又能保证学号不重复。2.3 函数依赖若已知 A 的值就能唯一确定 B 的值可以写成A → B例如学号 → 学生姓名 课程编号 → 课程名、学分 (学号, 课程编号) → 成绩范式的核心就是检查每个非关键字段究竟依赖谁。3. 数据库范式范式是一组减少重复和异常的设计规则。常见范式包括 1NF、2NF、3NF、BCNF、4NF 和 5NF。一般业务系统先把表设计到第三范式再根据明确的性能证据决定是否反范式。3.1 第一范式一列保存一个业务意义上的值第一范式1NF要求每个字段在当前业务语义下是原子的不能在一列中保存一组可独立查询和维护的值。反例student_idnamecourse_ids1张三1,2,5course_ids保存了多个课程编号难以使用外键、连接和普通索引也容易在增删编号时破坏格式。正确方式是把选课关系拆成多行student_idcourse_id111215“原子”与业务语义有关。地址在只展示的场景中可以是一个字符串若要按省、市统计就应拆成独立字段或关联地区表。因此字段使用了 MySQL 基本数据类型并不代表设计天然合理。3.2 第二范式消除对复合候选键的部份依赖第二范式2NF建立在 1NF 之上要求每个非关键字段依赖候选键的全部字段而不是只依赖其中一部分。假设把所有数据放进一张表student_course( sno, course_code, student_name, birthday, course_name, credit, score )候选键是(sno, course_code)但实际依赖关系为sno → student_name, birthday course_code → course_name, credit (sno, course_code) → score学生信息只依赖sno课程信息只依赖course_code因此发生了部分依赖应拆为学生表保存sno → 学生属性。课程表保存course_code → 课程属性。成绩表保存(sno, course_code) → score。不满足 2NF 容易产生四类异常数据冗余学生姓名和课程学分反复保存。更新异常修改学分时必须更新多行可能出现不同值。插入异常还没有学生选课时无法单独录入新课程。删除异常删除最后一条成绩时课程信息也被误删。如果所有候选键都是单列就不存在“只依赖键的一部分”因此不会违反 2NF但仍可能违反 3NF。3.3 第三范式消除非关键字段的传递依赖第三范式3NF建立在 2NF 之上重点消除非关键字段通过另一个非关键字段间接依赖候选键的情况。反例student( id, sno, name, college_id, college_name, college_phone )依赖链为id → college_id → college_name, college_phone学院名称和电话描述的是学院不是学生。若每个学生都保存一份学院电话修改时就会产生大量重复更新。应拆成student(id, sno, name, college_id) college(id, college_name, college_phone)记忆方式非关键字段应依赖于键、依赖于整个键并且只依赖于键。3.4 范式不是拆表越多越好范式主要保证一致性和可维护性不直接保证查询最快。拆表过多会增加连接查询和维护成本反范式则会增加重复数据及同步成本。合理顺序是先按 3NF 建立正确模型再用真实慢查询和执行计划确认瓶颈。只有收益明确时才增加汇总表或缓存列并设计可靠的同步机制不要仅凭“以后可能更快”就提前反范式。4. E-R 图与关系落地E-R 图用于在写 SQL 前表达实体、属性、关系、基数和可选性。矩形通常表示实体属性描述实体特征连线表示关系。4.1 一对一关系1:1一对一关系必须同时保证“能够引用”和“不能重复引用”。只添加普通account_id并不能阻止多个用户指向同一个账户。最严格的实现是让从表主键同时充当外键createtableuser_profile(user_idbigintunsignedprimarykey,nicknamevarchar(30),constraintfk_user_profile_userforeignkey(user_id)referencesusers(id));因为user_id是主键所以同一用户最多只有一份档案。若使用独立主键则关联列必须再加unique。外键放在哪一侧应根据生命周期和访问方向决定。4.2 一对多关系1:N把“一”方主键保存到“多”方class_design2.id ← student_design2.class_id原因是一个班级对应多名学生而每名学生只需要保存一个所属班级编号。不要在班级表中保存用逗号拼接的学生编号。4.3 多对多关系M:N关系数据库不能用一个普通外键直接表达多对多需要建立中间表student_design2 ↓ 1:N score_design2 ↑ N:1 course_design2中间表不仅保存两端主键还可以保存关系自身的属性如成绩、选课时间、状态。若业务规定同一学生只能选同一课程一次应使用复合主键或联合唯一约束防止组合重复。5. 从需求到建表的设计过程操作顺序不能颠倒先澄清能否退课、重修和多次考试等规则才能确定实体粒度再用候选键和基数表达关系通过范式检查消除错误依赖最后根据数据范围选择类型与约束并用真实查询设计索引。建表后还要主动测试重复学号、无效班级、重复选课和越界成绩。6. 学生选课系统完整设计下面是我练习用的四张练习表。createdatabaseifnotexistsdesign_studycharactersetutf8mb4collateutf8mb4_0900_ai_ci;usedesign_study;droptableifexistsscore_design2;droptableifexistsstudent_design2;droptableifexistscourse_design2;droptableifexistsclass_design2;createtableclass_design2(idbigintunsignedprimarykeyauto_increment,class_namevarchar(30)notnull,constraintuk_class_design2_nameunique(class_name))engineInnoDB;createtablecourse_design2(idbigintunsignedprimarykeyauto_increment,course_codevarchar(20)notnull,course_namevarchar(50)notnull,creditdecimal(3,1)notnull,constraintuk_course_design2_codeunique(course_code),constraintck_course_design2_creditcheck(creditbetween0.5and20.0))engineInnoDB;createtablestudent_design2(idbigintunsignedprimarykeyauto_increment,snovarchar(20)notnull,namevarchar(30)notnull,birthdaydate,gendertinyintunsigned,enroll_datedatenotnull,class_idbigintunsignednotnull,constraintuk_student_design2_snounique(sno),constraintck_student_design2_gendercheck(genderin(0,1,2)),constraintfk_student_design2_classforeignkey(class_id)referencesclass_design2(id)ondeleterestrict)engineInnoDB;createtablescore_design2(student_idbigintunsignednotnull,course_idbigintunsignednotnull,scoredecimal(5,2),selected_atdatetimenotnulldefaultcurrent_timestamp,constraintpk_score_design2primarykey(student_id,course_id),constraintck_score_design2_scorecheck(scoreisnullorscorebetween0and100),constraintfk_score_design2_studentforeignkey(student_id)referencesstudent_design2(id)ondeleterestrict,constraintfk_score_design2_courseforeignkey(course_id)referencescourse_design2(id)ondeleterestrict)engineInnoDB;6.1 关键设计原因学号使用varchar因为编号可能有前导 0也不参与数学运算。保存birthday而不是age因为年龄会随时间变化可在查询时计算。学分和成绩使用decimal避免float的近似值误差。score允许为null用于表示已经选课但尚未出分0表示实际得了 0 分。成绩表使用(student_id, course_id)复合主键直接表达“一名学生一门课程一条记录”。on delete restrict防止误删学生或课程后连带丢失成绩。是否级联删除必须由业务规则决定。班级表不保存student_count因为人数可从学生表统计重复保存会产生同步问题。如果需要记录补考或多次考试应将主键改为(student_id, course_id, attempt_no)或增加考试场次表不能继续假设一门课程只有一个成绩。7. 常用验证与查询7.1 检查实际表结构showcreatetablescore_design2;showindexfromscore_design2;SHOW CREATE TABLE用于检查主键、外键、检查约束和引擎SHOW INDEX用于检查索引列顺序。设计文档写了什么不重要数据库实际创建出的结构才是最终结果。7.2 统计班级人数selectc.id,c.class_name,count(s.id)asstudent_countfromclass_design2 cleftjoinstudent_design2 sons.class_idc.idgroupbyc.id,c.class_name;使用left join是为了让暂时没有学生的班级也能显示为 0 人。参考资料MySQL 8.0 Reference ManualCHECK ConstraintsMySQL 8.0 Reference ManualFOREIGN KEY ConstraintsMySQL 8.0 Reference ManualDECIMAL and NUMERICMySQL 8.0 Reference ManualNumeric Data Type Syntax以上是我关于MySQL的笔记分享也可以关注关注我的Sirens-Blog感谢你读到这里这也是我学习路上的一个小小记录。希望以后回头看时能看到自己的成长