MySQL数据库课程实验工程化：建库脚本、选课事务与索引优化

简介：面向学习数据库系统课程的高校学生与自学者，这份资料对应悉尼大学 COMP9120 数据库管理系统课程，聚焦从概念建模、关系模型与关系代数到 SQL 编写、事务与并发控制、规范化设计、存储索引和查询处理等核心主题，适合课程同步学习、实验对照与期末复习。包内共 65 个文件，以 39 份 PDF 讲义和习题解答、7 个 SQL 脚本、4 个代码压缩包及 2 份 PPT 为主，整体约 17.04MB，目录按 week1 至 week13 展开，涵盖讲义、辅导与复习材料，便于快速定位薄弱环节。讲义与 tutorial 习题答案逐周配套，SQL 脚本可用于建库、插入数据并验证复杂查询，代码压缩包内含 Java、Python 数据库应用练习，能帮助读者把事务的 ACID 特性、完整性约束、三值逻辑等抽象概念落实到可运行代码中，也便于复盘 ER 建模与规范化步骤。已有 193 人学习，适合需要系统梳理课程知识、对照习题查漏补缺的读者。

1. 一份建库脚本跑三遍才成功：这套数据库管理系统课程资源到底给谁用

数据库管理系统课程的资料，最大的问题从来不是内容少，而是散。课件、ER 图、几段孤立的 SQL、一份实验要求文档，摊在三个文件夹里，想跑通得自己拼。这套资源是按工程方式组织的：一组能直接 source 的建库脚本，一套带主外键约束和种子数据的表结构，一批围绕选课场景写的查询、事务与索引实验，外加一层很薄的应用代码，把 SQL 从命令行搬到程序里。它的适用对象很明确——正在上数据库课、要交实验但不想在环境上耗掉一周的学生，以及已经工作、想把范式、隔离级别、执行计划这些概念重新落地一遍的后端开发。整套东西不依赖任何商业组件，一台装了 MySQL 的机器加一个 Python 解释器就能从头跑到尾，中间不需要联网下载数据。

2. 工程全景：技术栈选型与目录结构逐层拆解

2.1 技术栈组合：MySQL 打底，Python 收口

这套工程的技术栈没有做炫技式堆叠，三个组件各管一段。存储层用 MySQL 8.0，理由很直接：课程里要演示的东西它全有——InnoDB 的事务与四种隔离级别、行锁与间隙锁、存储过程与触发器、EXPLAIN执行计划、窗口函数。换成 SQLite，并发控制基本等于没有，锁实验做不出来；换成 PostgreSQL 又容易把注意力从课程概念引到方言差异上。应用层用 Python 3 加pymysql，依赖只有一条 pip 命令，跨平台不用折腾 JDK 和驱动包。工程里另外附带了一份 JDBC 的连接片段，留给课程要求必须用 Java 的场合，但主线代码不依赖它。

用版本对照表解释一下为什么卡 8.0：8.0 才原生支持窗口函数和EXPLAIN ANALYZE，而第 4 章的排名查询和 4.3 的执行计划验证都压在这两个特性上。如果你机器上是 5.7，工程能跑起来，但窗口函数那段会直接报语法错误，得改写成自连接版本，性能差一截。另外 8.0 默认字符集是utf8mb4，中文列不用再手动指定编码，这也是省事的一点。

连接层不放 ORM 是刻意的。课程的训练目标是让你手写 SQL 并理解它落到执行引擎后发生了什么，套一层 ORM 会把生成的 SQL 藏起来，索引失效、隐式类型转换这类问题就看不见了。等你把原生 SQL 写顺了，第 6 章会讲怎么平滑替换成 SQLAlchemy。

2.2 目录结构：从 sql/ 到 app/ 的一条单向依赖链

工程的目录组织遵循一条原则：脚本之间只允许单向依赖，不允许回指。sql/里的文件按编号顺序执行，后者只依赖前者产出的表；app/只依赖sql/执行完之后的库；docs/只描述前两者，不参与运行。这条规则听起来像教条，但它解决的是最常见的一类返工——改了表结构，忘了同步种子数据，脚本重跑一遍就崩在外键上。

course-registry/ ├── sql/ │ ├── 01_schema.sql # 建库建表，含主外键与注释 │ ├── 02_seed.sql # 种子数据，固定 id 便于断言 │ ├── 03_views.sql # 视图，屏蔽多表连接细节 │ ├── 04_procedures.sql # 存储过程与触发器 │ └── 05_indexes.sql # 索引单独放，方便做前后对比实验 ├── app/ │ ├── config.py # 连接参数集中管理，不硬编码在脚本里 │ ├── db.py # 连接工厂，全局唯一的建连入口 │ ├── dao/ │ │ ├── student_dao.py │ │ └── enroll_dao.py │ └── cli.py # 命令行入口，跑通链路用 ├── tests/ │ └── test_enroll.py # 针对选课事务的集成测试 ├── docs/ │ ├── er_diagram.png │ └── normalization.md # 范式推导过程，答辩时直接用 └── README.md

05_indexes.sql单独拎出来是有原因的。做索引实验时你需要一个「加索引前」的基线，如果索引和建表写在一起，每次对比都得先注释掉再重建库，来回十几分钟就没了。单独成文件后，加索引就是source一次，删索引就是DROP INDEX一条命令，做前后对比很顺手。

02_seed.sql里的主键全部用固定值（比如学号S0000001），不用自增。自增看着方便，但测试断言里没法写死期望值，每次跑完还得先去查一遍 id 才能写断言，这是纯粹给自己找事。

2.3 环境搭建：从零到跑通第一条查询

先把库建出来。建库这步最容易翻车的地方是字符集，MySQL 8.0 服务端的默认字符集虽然是utf8mb4，但如果你本地的my.cnf是从旧机器拷过来的，很可能还停在latin1，后面中文列一律变问号。所以建库时显式指定，不给它默认的机会。

# 建库并显式锁定字符集与排序规则，不依赖服务端默认值 mysql -u root -p -e " CREATE DATABASE IF NOT EXISTS course_registry DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;" # 建一个专用账号，别用 root 跑应用层代码 mysql -u root -p -e " CREATE USER IF NOT EXISTS 'course_app'@'127.0.0.1' IDENTIFIED BY 'ChangeMe_2024'; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON course_registry.* TO 'course_app'@'127.0.0.1'; FLUSH PRIVILEGES;"

utf8mb4_0900_ai_ci里的ai是不区分重音、ci是不区分大小写，做姓名、课程名这类模糊匹配时更符合直觉。专用账号只给增删改查和执行存储过程的权限，不给DROP，这样即使脚本写错也不会把库删掉——这个习惯我是吃过一次亏之后才养成的。

脚本必须按编号顺序source，顺序错了外键直接报 1215：

# 顺序执行，任何一步失败就停下，不要用 && 串成一行硬跑 for f in sql/01_schema.sql sql/02_seed.sql sql/03_views.sql \ sql/04_procedures.sql sql/05_indexes.sql; do echo ">>> $f" mysql -u course_app -p course_registry < "$f" || { echo "失败于 $f"; exit 1; } done

用for循环加|| exit 1而不是用&&连成一条长命令，是因为长命令失败后你只能看到一个笼统的报错行号，定位不到是哪个文件出的问题。逐步打印文件名，出错的第一现场一眼就能看到。

最后验证连接，这段代码后面所有脚本都会复用：

# app/db.py —— 全局唯一的建连入口，别在业务代码里到处 connect() import pymysql def get_conn(): return pymysql.connect( host="127.0.0.1", port=3306, user="course_app", password="ChangeMe_2024", database="course_registry", charset="utf8mb4", # 必须和库表一致，否则中文列读出乱码 autocommit=False, # 事务由代码显式 commit，避免隐式提交 cursorclass=pymysql.cursors.DictCursor, # 返回 dict，按列名取值 ) if __name__ == "__main__": with get_conn() as conn, conn.cursor() as cur: cur.execute("SELECT COUNT(*) AS n FROM student") print(cur.fetchone())

autocommit=False这一项最关键。Python 驱动默认就是关闭自动提交的，但不少人从别的语言转过来，下意识以为每次execute都已经落库，结果测试里数据查不到，排查半天。DictCursor是为了让下游代码按列名取值，避免表结构加了一列之后所有按下标取值的代码全部错位。跑通这段输出学生总数，环境就算通了。

3. 数据模型落地：从 ER 图到能直接 source 的建表脚本

3.1 实体、关系与范式取舍

这套工程用的是选课场景，五个实体：系、学生、课程、开课班、选课记录。核心关系有两处需要仔细想。第一处是「课程」和「开课班」必须拆开——一门课可以在不同学期、由不同老师、在不同教室反复开，如果把学期、年份直接塞进课程表，同一门课每开一次就要复制一条课程记录，课程标题、学分这些属性跟着重复，这是典型的部分依赖，违反 2NF。拆成course和section之后，section用(course_id, sec_id, semester, year)四列复合主键唯一确定一个班。

第二处是选课记录表。严格按 3NF 来看，takes表里存semester、year是冗余的，因为这两个值可以通过(course_id, sec_id)反查section得到。但这里做了有意的反范式——takes的主键必须包含学期和年份才能唯一约束「同一个学生同一学期只能选同一个班一次」，如果只存(student_id, course_id, sec_id)，学生跨学期重修同一门课时就会主键冲突。这个取舍在docs/normalization.md里有完整推导，答辩时直接引用就行。

第三个决策是删除策略。系被删了，下面的学生怎么办？这里选ON DELETE SET NULL，把学生的dept_id置空而不是连人一起删。反过来说，学生被删了，他的选课记录用ON DELETE CASCADE一起清掉，因为孤儿选课记录没有任何查询价值，留着只会让统计口径出现偏差。

3.2 建表脚本逐段拆解

先看头部，删表顺序和建表顺序必须相反，中间的FOREIGN_KEY_CHECKS开关是给重复执行准备的：

-- sql/01_schema.sql SET NAMES utf8mb4; -- 告诉服务端本次会话用什么编码解析脚本 SET FOREIGN_KEY_CHECKS = 0; -- 暂时关掉外键检查，允许乱序删表 DROP TABLE IF EXISTS takes; DROP TABLE IF EXISTS section; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS department; SET FOREIGN_KEY_CHECKS = 1; -- 立刻打开，后面的建表必须受约束保护

SET NAMES utf8mb4这一句不能省。命令行客户端读取.sql文件时，如果会话编码和服务端不一致，文件里的中文注释和中文数据会在解析阶段就被转坏，报错信息还很难懂。开关外键检查只在这五行删表语句期间有效，紧接着就打开，保证后面的建表和外键定义仍然受约束校验。

父表先建，字段类型必须和子表里的引用列严格一致，否则外键创建失败：

CREATE TABLE department ( dept_id CHAR(4) NOT NULL COMMENT '系编号，固定4位', dept_name VARCHAR(60) NOT NULL COMMENT '系名，全库唯一', building VARCHAR(20) DEFAULT NULL, budget DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '用定点数，不用FLOAT', PRIMARY KEY (dept_id), UNIQUE KEY uk_dept_name (dept_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系'; CREATE TABLE student ( student_id CHAR(8) NOT NULL, name VARCHAR(40) NOT NULL, dept_id CHAR(4) DEFAULT NULL, tot_cred SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (student_id), KEY idx_student_dept (dept_id), -- 外键列必须建索引，否则删父表会锁全表 CONSTRAINT fk_student_dept FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生';

budget用DECIMAL不用FLOAT是硬要求。浮点数存金额，做汇总统计时会出现0.30000000000000004这种尾数，学生答辩演示时被问到很尴尬。CHAR(8)存学号而不用VARCHAR，是因为定长类型在 InnoDB 里占位固定，做主键和连接时比较开销更小，而且学号本来就长度固定。

idx_student_dept这个索引容易被忽略。MySQL 建外键时会自动给子表的引用列建索引，但如果你自己写了KEY，它会复用。显式写出来的好处是可读——后面做慢查询优化时能一眼看出哪些列已经有索引，不用去SHOW CREATE TABLE里翻。

接着是开课班和选课记录，复合主键和复合外键都在这里：

CREATE TABLE section ( course_id CHAR(8) NOT NULL, sec_id CHAR(4) NOT NULL COMMENT '同一课程同一学期的班号，如01', semester ENUM('Fall','Spring','Summer') NOT NULL, year SMALLINT UNSIGNED NOT NULL, building VARCHAR(20) DEFAULT NULL, room VARCHAR(10) DEFAULT NULL, capacity SMALLINT UNSIGNED NOT NULL DEFAULT 60 COMMENT '容量上限，事务实验要用', PRIMARY KEY (course_id, sec_id, semester, year), CONSTRAINT fk_section_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开课班'; CREATE TABLE takes ( student_id CHAR(8) NOT NULL, course_id CHAR(8) NOT NULL, sec_id CHAR(4) NOT NULL, semester ENUM('Fall','Spring','Summer') NOT NULL, year SMALLINT UNSIGNED NOT NULL, grade VARCHAR(2) DEFAULT NULL COMMENT 'NULL表示在修，未出分', PRIMARY KEY (student_id, course_id, sec_id, semester, year), KEY idx_takes_section (course_id, sec_id, semester, year), CONSTRAINT fk_takes_student FOREIGN KEY (student_id) REFERENCES student (student_id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_takes_section FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='选课记录';

takes的主键顺序(student_id, ...)决定了两件事。InnoDB 的主键就是聚簇索引，数据行按主键顺序物理排列，「查某个学生选了哪些课」这类查询走的是顺序读，非常快；反过来「查某个班有哪些学生」用不上这个主键，所以补了idx_takes_section做反向覆盖。这就是为什么两个索引都要有，只留一个必然有一半的查询要全表扫。

semester用ENUM而不是VARCHAR，一方面省空间（内部存 1 字节整数），另一方面把非法值挡在存储层——写'Autumn'进去会直接报错，不用在应用层再写一遍校验。代价是以后要加学期就得ALTER TABLE，这个场景下学期的集合是稳定的，所以可以接受。

3.3 约束校验：写完数据先跑几条自检 SQL

种子数据灌完不要急着写业务查询，先跑自检。最有价值的一条是校验学生表里的累计学分和选课记录算出来的学分是否一致——这是典型的冗余字段，很容易写歪：

-- 自检 1：累计学分与明细是否对得上，返回空集才算通过 SELECT s.student_id, s.tot_cred, COALESCE(SUM(c.credits), 0) AS calc_cred FROM student s LEFT JOIN takes t ON t.student_id = s.student_id AND t.grade IS NOT NULL AND t.grade <> 'F' LEFT JOIN course c ON c.course_id = t.course_id GROUP BY s.student_id, s.tot_cred HAVING calc_cred <> s.tot_cred;

注意两个条件的位置。grade IS NOT NULL AND grade <> 'F'必须写在ON里，不能挪到WHERE。挪过去之后，没出分的学生会因为LEFT JOIN产生的 NULL 被WHERE过滤掉，整行消失，聚合结果直接变成 0，自检就失去意义了。这个坑在第 4 章还会再遇到一次，属于写多表连接时最高频的错误。

-- 自检 2：有没有选课记录指向不存在的开课班（外键正常时必为空） SELECT t.* FROM takes t LEFT JOIN section s ON s.course_id = t.course_id AND s.sec_id = t.sec_id AND s.semester = t.semester AND s.year = t.year WHERE s.course_id IS NULL;

这两条返回空集，说明数据模型和种子数据都对得上，可以往下走。测试文件里可以把它们包成断言，每次改完数据跑一遍，比肉眼看强得多。

4. 查询与事务：把课程实验写成可复用的 SQL 与 Python 模块

4.1 多表连接与聚合：ON 和 WHERE 的位置差异

课程实验里最常见的题型是「查每个学生已修学分」和「查每门课的选课人数排名」。第一个查询前面已经写过骨架，这里重点讲第二种，因为它同时用到分组和窗口函数：

-- 每个学期内，按选课人数给各开课班排名，人数相同并列 SELECT course_id, sec_id, semester, year, cnt, RANK() OVER (PARTITION BY semester, year ORDER BY cnt DESC) AS rk FROM ( SELECT course_id, sec_id, semester, year, COUNT(*) AS cnt FROM takes GROUP BY course_id, sec_id, semester, year ) AS agg ORDER BY semester, year, rk;

内层子查询先做分组聚合，把每个班的人数压成一行；外层用RANK()在「学期 + 年份」这个分区内按人数倒序排名。用RANK而不是ROW_NUMBER，是因为人数相同的班应该并列名次，ROW_NUMBER会强行排出先后，结果不具业务意义。PARTITION BY里的列顺序要和ORDER BY的筛选条件对齐，否则跨学期的数据会被混在一个分区里排名。

分组统计还有一个容易踩的点：COUNT(*)和COUNT(列名)结果不同。统计「有多少学生选了这门课」用COUNT(*)，统计「有多少学生出了成绩」用COUNT(grade)，后者会自动跳过 NULL。实验报告里经常因为这两个写混导致数字对不上，检查时先看这一处。

4.2 事务与隔离级别：容量校验为什么必须加 FOR UPDATE

选课是这套工程里唯一一个真正需要事务的操作，它同时满足三个条件：先读后写、读的结果影响写的判断、并发下会互相干扰。如果只是「读一下容量，再插一条记录」，两个学生同时选最后一个名额时，双方都读到剩余 1，都判定可以选，都插入成功，班级超员。

# app/dao/enroll_dao.py —— 选课事务，带容量校验 import pymysql def enroll(conn, student_id, course_id, sec_id, semester, year): with conn.cursor() as cur: try: conn.begin() # 1) 加行锁读容量，锁住这一行直到事务结束 cur.execute( """SELECT capacity FROM section WHERE course_id=%s AND sec_id=%s AND semester=%s AND year=%s FOR UPDATE""", (course_id, sec_id, semester, year), ) row = cur.fetchone() if row is None: raise ValueError("开课班不存在") # 2) 在锁的保护下统计已选人数 cur.execute( """SELECT COUNT(*) AS n FROM takes WHERE course_id=%s AND sec_id=%s AND semester=%s AND year=%s""", (course_id, sec_id, semester, year), ) taken = cur.fetchone()["n"] if taken >= row["capacity"]: raise ValueError("容量已满") # 3) 插入选课记录，主键冲突由数据库兜底防重复选课 cur.execute( """INSERT INTO takes(student_id, course_id, sec_id, semester, year) VALUES (%s, %s, %s, %s, %s)""", (student_id, course_id, sec_id, semester, year), ) conn.commit() return True except pymysql.err.IntegrityError: conn.rollback() # 主键冲突：同一学生重复选同一个班 return False except Exception: conn.rollback() # 任何异常都回滚，不留半截数据 raise

FOR UPDATE是关键。它给section里那一行加排他锁，第二个并发事务执行到同一句时会阻塞，直到第一个事务提交或回滚。没有它，两个事务读到的是同一份快照，在REPEATABLE READ隔离级别下彼此看不见对方的插入，超员就成了必然。

except分两层的理由是错误语义不同。IntegrityError是预期内的业务冲突（重复选课），吞掉并返回False让上层提示用户；其他异常说明是代码或环境问题，回滚后继续往外抛，别让它静默失败。很多课程作业在这里图省事写一个裸except: pass，结果事务回滚了但程序假装成功，调试时完全找不到数据去哪了。

隔离级别本身也值得在实验里跑一遍对比。用两个终端开两个会话，都执行SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED，然后一个会话改数据不提交，另一个会话去读，能清楚看到「不可重复读」；换成REPEATABLE READ再跑一次，同样的操作读到的值就不变了。用SELECT @@transaction_isolation确认当前级别，别凭记忆猜。

4.3 索引与执行计划：用 EXPLAIN 判断改得对不对

索引文件单独放着，就是为了做前后对比。挑一条最典型的查询：按学生查他的全部选课及课程名。

-- 加索引前先看执行计划，重点看 type 和 rows 两列 EXPLAIN ANALYZE SELECT t.course_id, c.title, t.semester, t.year, t.grade FROM takes t JOIN course c ON c.course_id = t.course_id WHERE t.student_id = 'S0000007';

EXPLAIN ANALYZE是 8.0 才有的，和传统EXPLAIN的区别是它真的执行一遍，给出实际耗时和实际行数，而EXPLAIN只给优化器的估算。估算和实际差得远，通常意味着统计信息过期，跑一次ANALYZE TABLE takes就恢复了。

判断标准看三处。type列出现ALL说明全表扫，ref或range才算走上索引。rows是预估扫描行数，和表总行数接近就是没走索引。Extra里出现Using filesort说明排序没吃到索引，数据量一大就慢。加索引就是针对这三处下药：

-- 覆盖索引：把查询要的列都放进索引，避免回表 CREATE INDEX idx_takes_student_cover ON takes (student_id, course_id, semester, year, grade);

前导列必须是student_id，这由最左前缀原则决定——索引按(student_id, course_id, ...)的顺序排列，查询条件里没有student_id就用不上这个索引。这里把grade也放进索引，是因为它是定长小字段，加上之后这条查询完全可以从索引里取数，不需要回表到聚簇索引，Extra里会显示Using index。

代价也要说清楚：每个索引都是一份额外的 B+ 树，插入和更新时要同步维护。takes表写入频繁，索引加太多会让选课变慢。工程里的原则是只给真实出现过慢查询的语句加索引，不给「以后可能会查」的列加。加完索引再跑一遍EXPLAIN ANALYZE对比实际耗时，数字下去了才算有效，凭感觉判断基本都是错的。

5. 排查手册：五类把课程实验卡到通宵的翻车现场

5.1 现象：source 脚本报 1215，外键建不起来

现象很具体：执行01_schema.sql，前面几张表都过了，到takes或section时抛出ERROR 1215 (HY000): Cannot add foreign key constraint，但报错信息里不告诉你是哪一列的问题。

原因通常有三个。一是建表顺序反了，子表先于父表创建，父表还不存在。二是引用列和被引用列的类型不完全一致，比如一边是CHAR(8)另一边是VARCHAR(8)，或者一边带UNSIGNED另一边不带——字符长度和符号属性都算类型的一部分，差一点就建不上。三是被引用的列上没有索引，复合外键引用的列组合必须和父表的某个索引前缀完全匹配。

解决方法是拿到准确错误。执行SHOW ENGINE INNODB STATUS\G，翻到LATEST FOREIGN KEY ERROR那一段，它会明确写出是哪张表的哪个约束、哪一列对不上。先按建表顺序核对一遍父表是否都在，再逐列对比类型，最后检查复合外键的列组合是不是和父表主键的列顺序一致。这三个查完基本就解决了。

5.2 现象：中文全变问号或乱码

现象是插入时看着正常，SELECT出来一堆???，或者显示成æ ¡å·这种拉丁字母混排。

原因分三层。第一层是库表字符集，建库时没显式指定，跟着服务端默认走了latin1。第二层是连接字符集，Python 驱动建连时charset参数没写或者写成了utf8（这是utf8mb3的别名，存不下四字节字符）。第三层是导入脚本时的会话编码，source之前没有SET NAMES utf8mb4，文件内容在解析阶段就被转坏了。

解决要三层一起查。先SHOW CREATE DATABASE course_registry确认库字符集，再SHOW CREATE TABLE student确认表字符集，然后用SHOW VARIABLES LIKE 'character_set%'看连接层的几个变量。应用层的charset="utf8mb4"必须显式写死，不能依赖默认值。三层统一之后重灌一遍数据，问题基本绝迹。

5.3 现象：查出来的时间和本地差 8 小时

现象是程序里datetime.now()写进去，数据库里看是对的，但用别的客户端查出来早了 8 小时，或者反过来。

原因是DATETIME和TIMESTAMP的语义完全不同。DATETIME存的是字面值，不做时区转换，你写什么读什么；TIMESTAMP存的是 UTC 时间戳，写入和读取时都会按会话的time_zone做一次转换。如果建表时随手用了TIMESTAMP，而应用连接和客户端工具的time_zone设置不一样，两边看到的就永远对不上。

解决方法是先定语义再定类型。业务时间（选课时间、出分时间）如果需要跨时区正确展示，用TIMESTAMP，但必须在连接串里统一time_zone='+08:00'；如果只是记录一个本地时刻、不打算跨时区，用DATETIME更省心，不会有意外的转换。工程里统一用DATETIME，并在db.py里执行SET time_zone = '+08:00'，把不确定性压到最低。

5.4 现象：明明有索引，查询还是慢得离谱

现象是EXPLAIN显示type=ALL，或者key列是 NULL，但SHOW INDEX明明能看到那个索引。

最常见的原因有两类。一类是隐式类型转换：索引建在VARCHAR列上，查询条件写成WHERE student_id = 20240001不带引号，MySQL 会把列值转成数字再比较，索引直接失效。另一类是前导模糊匹配：WHERE title LIKE '%数据库%'用不上索引，因为 B+ 树只能从左往右匹配，模式串开头是通配符就无从下手。

解决办法是先对齐类型，字符串条件一定要加引号，参数化查询天然避免这个问题。模糊查询如果确实要中缀匹配，考虑建全文索引或者把匹配交给专门的检索组件，不要在LIKE上硬扛。改完之后用EXPLAIN ANALYZE对比实际耗时，type从ALL变成ref才算真的改对了。

5.5 现象：事务看起来没生效，异常了数据还在

现象是代码里写了rollback，但出错后去查库，数据仍然在。

第一个怀疑对象是表引擎。SHOW TABLE STATUS看Engine列，如果是MyISAM，它根本不支持事务，rollback是空操作。历史遗留库或者从别处拷来的建表语句很容易带出这个问题。第二个怀疑对象是autocommit，某些驱动默认开着自动提交，begin()之前每条语句都已经独立提交了。第三个是存储过程里的COMMIT，过程内部如果自己提交了，外层的回滚也管不到。

解决方法是先确认引擎全部是InnoDB，这一点在建表脚本里显式写ENGINE=InnoDB就能避免。然后确认连接的autocommit=False，并且所有事务都走同一套begin / commit / rollback封装，不在业务代码里散着写。存储过程内部坚决不写COMMIT，把事务边界留给调用方，这个约定要在 README 里写清楚。

6. 二次开发：把这套课程工程改成自己的项目骨架

课程工程跑通之后，多数人下一步是拿它当自己项目的起点。改造有轻重之分，先看三条最值得做的。

第一条是加迁移管理。现在靠01_schema.sql全量重建，数据一多就不能用了。换成 Alembic 或者自建的migrations/目录，每个变更一个带时间戳的文件，正向和回滚脚本成对写。判断标准很简单：能不能在不丢数据的前提下把线上库从版本 A 升到版本 B，能就对了。

第二条是给takes加软删除和审计。课程作业里选课记录删了就没了，真实业务需要保留「谁在什么时候退的课」。加status和deleted_at两列，配上视图把软删的行过滤掉，业务代码不用改。

第三条是补集成测试。现在的tests/test_enroll.py是跑在真实库上的，换机器就废。改成用 Docker 起一个临时 MySQL 容器，测试开始时建库灌数据，结束时销毁，pytest一条命令跑完全部用例。第 3 章那两条自检 SQL 直接包成断言搬进去，改完表结构跑一遍，比手动核对靠谱得多。

有一件事不建议做：不要为了「看起来专业」上读写分离和分库分表。这套表的数据量在单机 MySQL 上跑几百万行毫无压力，提前拆分只会让选课事务的跨库一致性问题变成新的坑，收益为零。

我自己的习惯是，任何一次表结构变更，先在一个干净的库上从头source一遍全部脚本，再用EXPLAIN ANALYZE抽查三条最热的查询，两步都过才允许提交。这个流程救过我好几次——有一次改字段类型忘了同步外键列，干净库一跑就炸了，本地那个「已经改过」的库反倒看不出来。希望帮到你。

本文还有配套的精品资源，点击获取