简介这份资源面向西北工业大学软件学院数据库课程学习者聚焦实验五的E-Commerce数据库ER模式设计任务适合正在完成实验、复习概念模型建模或需要对照示例查漏补缺的同学。压缩包共20个文件约282KB以14个GIF操作演示、2个TXT文本和2个DOC说明文档为主并配有CDM概念模型文件与HTM实验说明页能较完整地呈现从需求理解、实体联系分析到ER图绘制的参考过程。已有911人学习说明该实验在课程学习中具有较高参考需求。读者可结合ER图、文字说明与分步界面演示理解用户注册、商品浏览、购物车、结算登录、配送信息、支付与订单确认等业务对象之间的实体、联系、属性和基数约束核对实验五的建模思路与结果也可将其作为E-Commerce项目概念设计的示例材料提升数据库ER建模与结果校验能力。1. 从一份 SQL 脚本包说起数据库实验五到底在练什么解压压缩包双击那个建库脚本屏幕上刷出一屏红色报错——这是很多人跑数据库实验五时的第一幕。它不是能双击运行的软件而是一套围绕「学生选课」场景组织起来的 SQL 脚本集合把完整性约束、触发器、存储过程、事务与并发这几件事串成一条线。它适合两类人正在做数据库实验课作业、需要一份能跑通的参照实现的学生以及想把业务规则下沉到数据库层、又不愿意啃官方手册的开发者。下面按「是什么 → 怎么跑 → 怎么改」的顺序把这套脚本拆开看。2. 环境与目录摸底把压缩包还原成一个能跑的实验工程2.1 目录里都有什么哪些能直接执行这类实验包的组织方式大同小异展开之后基本是「建库建表 → 逻辑对象 → 验证脚本 → 文档」四层。我一般先不急着执行而是把文件夹按序号排一遍确认哪些脚本是幂等的、哪些必须在前面跑完才能跑。下面是我拆包时会先画出来的一张目录地图文件名可能带不同前缀层级和依赖顺序基本不会变db-lab5/ ├── 01_schema/ │ ├── 01_database.sql -- 建库 字符集、排序规则 │ ├── 02_tables.sql -- 四张核心表 主外键 CHECK │ ├── 03_index.sql -- 辅助索引、唯一索引 │ └── 04_seed.sql -- 种子数据几十行 INSERT ├── 02_logic/ │ ├── 05_triggers.sql -- 成绩校验触发器 审计日志触发器 │ ├── 06_procedures.sql -- 选课、退课、成绩统计存储过程 │ └── 07_views.sql -- 成绩单视图、课表视图 ├── 03_verify/ │ ├── 08_concurrency_a.sql -- 并发会话 A │ ├── 09_concurrency_b.sql -- 并发会话 B │ └── 10_check_result.sql -- 结果校验查询 ├── docs/ │ └── ER 图与实验报告模板 └── README.md01_schema下的四个脚本是纯 DDL 加 DML彼此只依赖顺序不依赖环境属于「扔进任何一台装好 MySQL 的机器都能跑」的部分。02_logic里的触发器、存储过程、视图必须等表建完才能创建顺序反了会直接报「表不存在」。03_verify是给并发实验准备的里面通常是两个会话分别执行的语句块不能一次性全选执行必须开两个客户端窗口交替跑。提示先把01_schema整套跑通再动后面的脚本。建表阶段的问题最好在种子数据还没灌进去的时候暴露出来否则清库重来要多花一倍时间。2.2 建库建表脚本的执行顺序与字符集设置执行顺序上我习惯用命令行按文件名循环跑比在图形化工具里一个个点开靠谱得多。跑之前先确认一件事字符集到底在哪一层被定死。MySQL 的字符集有四层——服务端默认、库、表、列上层没写就继承下层继承链一断中文姓名就会变成问号。所以01_database.sql里最该看的是这几行-- 建库时一次性把字符集钉死避免后面建表继承到默认的 latin1 DROP DATABASE IF EXISTS lab5; CREATE DATABASE lab5 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE lab5;CHARACTER SET utf8mb4决定这层能存哪些字符中文、生僻字、emoji 都能进COLLATE utf8mb4_0900_ai_ci决定比较和排序规则ai是重音不敏感、ci是大小写不敏感。MySQL 8.0 默认就是这套写出来是给自己留后悔药如果目标机是 5.7把排序规则换成utf8mb4_general_ci5.7 根本没有0900这个排序规则照抄会报 1273 未知排序规则。然后按顺序灌进去# 按文件名顺序执行建库、建表、索引、种子数据 for f in 01_schema/0*.sql; do echo running $f mysql -h 127.0.0.1 -P 3306 -u root -p \ --default-character-setutf8mb4 \ --show-warnings \ $f || { echo failed at $f; break; } done--default-character-setutf8mb4管的是客户端这一层漏了它哪怕库里是utf8mb4终端里的中文照样可能变成乱码。--show-warnings是个血泪经验MySQL 很多语句失败是「警告级」的比如字段被截断、字符串超长不加这个参数你会以为脚本跑成功了直到查数据才发现少了几条。2.3 客户端连接命令行、图形化工具与连接串跑实验五至少要准备两个能独立提交事务的会话。图形化工具里开两个查询窗口未必是两个连接有些工具的同一个标签页会复用连接这会让并发实验的结论完全失真。稳妥的做法是一个命令行窗口加一个图形化工具窗口或者两个独立的终端。如果是用代码去调存储过程验证连接参数要比平时多留意几项# PyMySQL 连接参数并发实验必须关掉自动提交 import pymysql conn pymysql.connect( host127.0.0.1, port3306, userlab5, password******, databaselab5, charsetutf8mb4, autocommitFalse, # 关键手动控制事务边界 cursorclasspymysql.cursors.DictCursor, )autocommitFalse是并发验证的前提开成True的话每条语句自己就提交了你根本构造不出「事务 A 还没提交、事务 B 已经在读」的场景跑出来的结果会让人以为隔离级别设置没生效。charsetutf8mb4必须和库一致DictCursor只是让结果按字典取省得记列下标。3. 表结构与约束设计把选课场景落成一组能跑的 DDL3.1 四张核心表的主外键与级联策略整个实验包的地基是学生、课程、选课三张表加一张教师表选课表是全篇的枢纽——触发器挂它身上存储过程改它并发实验锁它。建表的第一个决策是存储引擎必须显式写ENGINEInnoDBMyISAM 不支持事务和外键后面的事务与并发实验会直接作废。-- 教师表被课程表弱引用删除教师不应连带删课 CREATE TABLE teacher ( tno CHAR(10) NOT NULL COMMENT 教师工号, tname VARCHAR(20) NOT NULL COMMENT 姓名, title VARCHAR(20) NULL COMMENT 职称, PRIMARY KEY (tno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课程表容量字段给存储过程判满用 CREATE TABLE course ( cno CHAR(8) NOT NULL, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0, tno CHAR(10) NULL, capacity SMALLINT UNSIGNED NOT NULL DEFAULT 60, PRIMARY KEY (cno), CONSTRAINT ck_course_credit CHECK (credit 0 AND credit 10), CONSTRAINT fk_course_teacher FOREIGN KEY (tno) REFERENCES teacher(tno) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;ON DELETE SET NULL和ON DELETE CASCADE的选择反映的是业务语义教师调走课程还在所以把tno置空学生退学他的选课记录没有保留价值直接级联删掉。很多同学在选课表上无脑写CASCADE结果删一门课把所有人的成绩一起带走这种翻车在验收时特别难看。-- 选课表成绩允许为空先选课后录成绩 CREATE TABLE sc ( sno CHAR(10) NOT NULL, cno CHAR(8) NOT NULL, term CHAR(11) NOT NULL DEFAULT 2024-2025-1, grade DECIMAL(5,2) NULL, PRIMARY KEY (sno, cno, term), KEY idx_sc_cno (cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT ck_sc_grade CHECK (grade IS NULL OR (grade 0 AND grade 100)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;课程这一侧用RESTRICT而不是CASCADE效果是「有成绩的课程删不掉」等于给数据加了一道保险。这个约束在实验报告里经常被老师追问答不出来就说明没理解外键动作的实际含义。3.2 CHECK、UNIQUE、DEFAULT 与索引的配合CHECK约束是实验五里最容易被忽略的一个点。它在 MySQL 8.0.16 之前只是被解析然后默默丢掉写上去不报错但也不生效插一条 200 分的成绩照样成功。如果目标环境的版本低于 8.0.16就得靠BEFORE触发器兜底这个坑放在第 5 章细说。索引这块联合主键(sno, cno, term)自带一个索引但遵循最左前缀按sno查、按sno cno查都能走索引单独按cno查就走不了了。而实验里「查某门课有多少人选」是高频操作所以idx_sc_cno不能省。同理student表上按院系统计的查询很多sdept上补一个普通索引几十万行数据时差别很明显。DEFAULT用得多的是时间和院系。created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP这类默认值能省掉应用层赋值但要注意DEFAULT CURRENT_TIMESTAMP在一张表里可以有多个ON UPDATE CURRENT_TIMESTAMP最好只给真正需要自动更新的字段否则一次无关的更新会把审计时间冲掉。3.3 建表脚本逐段拆解与执行验证表建完不能只看「Query OK」要主动构造几个非法数据把约束试出来。我习惯用三段式验证约束拦不拦得住、索引走不走得上、级联删得对不对。-- 1) 越界成绩看 CHECK 有没有真生效 INSERT INTO sc (sno, cno, grade) VALUES (2021000001, C0001, 200); -- 预期ERROR 3819: Check constraint ck_sc_grade is violated. -- 2) 主键重复看联合主键是否起作用 INSERT INTO sc (sno, cno, term) VALUES (2021000001, C0001, 2024-2025-1); INSERT INTO sc (sno, cno, term) VALUES (2021000001, C0001, 2024-2025-1); -- 预期ERROR 1062: Duplicate entry -- 3) 索引验证看 type 是不是 ref、key 是不是 idx_sc_cno EXPLAIN SELECT sno FROM sc WHERE cno C0001;第 3 段里EXPLAIN的type列是关键观察点ALL是全表扫ref是走了普通索引range是范围扫描const是主键或唯一索引等值命中。如果按cno查出来是ALL说明索引没建上或者被优化器放弃了这时候先SHOW INDEX FROM sc确认索引存在再考虑是不是数据量太小导致优化器认为全表扫更便宜——后者在种子数据只有几十行时非常常见属于正常现象不用急着改语句。4. 触发器与存储过程把业务规则下沉到数据库层4.1 BEFORE 触发器做数据校验AFTER 触发器写审计日志触发器分两个时机用BEFORE用来改数据或拦住数据AFTER用来记录既成事实。成绩越界这种事BEFORE里直接抛异常最干净抛出去之后应用层拿到的是一条明确的错误信息而不是静默失败。DELIMITER $$ CREATE TRIGGER trg_sc_grade_bi BEFORE INSERT ON sc FOR EACH ROW BEGIN -- 成绩允许为空但一旦有值就必须落在 0-100 IF NEW.grade IS NOT NULL AND (NEW.grade 0 OR NEW.grade 100) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 成绩必须落在 0-100 之间; END IF; END$$ DELIMITER ;DELIMITER $$是客户端指令不是 SQL 语句作用是临时把语句结束符从分号改成$$否则 MySQL 读到触发器体里第一个分号就以为语句结束了直接 1064 语法错误。NEW.grade指代即将写入的那行数据SIGNAL SQLSTATE 45000是用户自定义异常的通用写法45000表示「未处理的用户定义异常」应用层捕获后能拿到MESSAGE_TEXT里的中文提示。审计日志要记录「谁在什么时候把成绩从多少改成了多少」这类信息在UPDATE之后才成立所以用AFTERCREATE TABLE sc_audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sno CHAR(10) NOT NULL, cno CHAR(8) NOT NULL, old_grade DECIMAL(5,2) NULL, new_grade DECIMAL(5,2) NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_audit_sno (sno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; DELIMITER $$ CREATE TRIGGER trg_sc_grade_au AFTER UPDATE ON sc FOR EACH ROW BEGIN -- 是 NULL 安全等于NULL 改成 60 这种也要记一笔 IF NOT (OLD.grade NEW.grade) THEN INSERT INTO sc_audit_log (sno, cno, old_grade, new_grade) VALUES (OLD.sno, OLD.cno, OLD.grade, NEW.grade); END IF; END$$ DELIMITER ;这里的值得多说一句。普通等号在任一侧为NULL时结果是NULLIF NULL THEN走的是「假」分支于是「成绩从空改成 60」这条最需要审计的变更反而被漏掉了。是 NULL 安全等于两侧都为NULL时返回真一侧为空一侧有值时返回假正好符合「值真的变了才记录」的语义。4.2 存储过程的参数设计、事务边界与异常处理选课逻辑是这批脚本里唯一同时涉及读、判断、写的地方也是事务边界最该画清楚的地方。我一般把它做成三个输入参数加两个输出参数输入是学号、课程号、学期输出是一个状态码和一句人话提示调用方不需要解析错误文本。DELIMITER $$ CREATE PROCEDURE sp_enroll( IN p_sno CHAR(10), IN p_cno CHAR(8), IN p_term CHAR(11), OUT p_code INT, OUT p_msg VARCHAR(128) ) BEGIN DECLARE v_capacity SMALLINT UNSIGNED DEFAULT 0; DECLARE v_taken INT DEFAULT 0; DECLARE v_exists INT DEFAULT 0; -- 任何未捕获异常统一回滚并把原始错误信息带回调用方 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; GET DIAGNOSTICS CONDITION 1 p_msg MESSAGE_TEXT; SET p_code -1; END; START TRANSACTION; SELECT COUNT(*) INTO v_exists FROM sc WHERE sno p_sno AND cno p_cno AND term p_term; IF v_exists 0 THEN ROLLBACK; SET p_code 1, p_msg 已选过该课程; ELSE -- FOR UPDATE 锁住课程行防止两个会话读到同一个余量 SELECT capacity INTO v_capacity FROM course WHERE cno p_cno FOR UPDATE; SELECT COUNT(*) INTO v_taken FROM sc WHERE cno p_cno AND term p_term; IF v_taken v_capacity THEN ROLLBACK; SET p_code 2, p_msg 课程已满; ELSE INSERT INTO sc (sno, cno, term) VALUES (p_sno, p_cno, p_term); COMMIT; SET p_code 0, p_msg 选课成功; END IF; END IF; END$$ DELIMITER ;几个参数的含义要提前约定死p_code取0成功、1重复选课、2课程已满、-1未知异常这样应用层只判断数字就行。DECLARE EXIT HANDLER FOR SQLEXCEPTION必须写在START TRANSACTION之前它是声明不是语句写后面会报语法错误。GET DIAGNOSTICS CONDITION 1取的是最近一次异常的详细信息比手写「操作失败」有用得多。真正防超选的是那句FOR UPDATE。不加它两个会话可能同时读到「已选 59 人、容量 60」然后各自插入一条最终 61 人。加上行锁后后到的会话会阻塞在SELECT ... FOR UPDATE上等前一个事务提交再读读到的是 60走「课程已满」分支。调用方式CALL sp_enroll(2021000001, C0001, 2024-2025-1, code, msg); SELECT code AS code, msg AS msg;4.3 事务与并发验证两个会话同时选同一门课并发实验是这套脚本里最容易被跑成「看起来对但其实没验证」的部分。核心是要分清快照读和当前读。开两个会话都设成可重复读-- 会话 A SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT COUNT(*) FROM sc WHERE cno C0001; -- 假设得到 10-- 会话 B插入一条并提交 INSERT INTO sc (sno, cno, term) VALUES (2021000002, C0001, 2024-2025-1); COMMIT;此时回到会话 A 再查一次结果仍然是 10——这是快照读事务开始时的 Read View 已经固定别急着怀疑隔离级别没生效。换一种读法-- 会话 A当前读会看到会话 B 已提交的数据 SELECT COUNT(*) FROM sc WHERE cno C0001 FOR UPDATE; -- 得到 11这就是快照读和当前读的区别。做并发实验时必须在报告里写清楚用的是哪种读只贴一个数字说明不了任何问题。真正想验证隔离级别可以再跑一遍「不可重复读」把会话 A 换成READ COMMITTED同样两条SELECT第二条就能看到 11差异一目了然。5. 排查与避坑实验五最容易翻车的五个地方5.1 成绩插到 200 分也成功CHECK 约束形同虚设现象建表时明明写了CHECK (grade BETWEEN 0 AND 100)INSERT一条 200 分的记录照样成功SHOW CREATE TABLE里约束还在。原因MySQL 在 8.0.16 之前会解析CHECK子句但直接忽略不报错也不执行部分 MariaDB 版本行为也不一致。用图形化工具连的时候更容易踩到因为工具本身不提示版本差异。解决先SELECT VERSION();确认版本。8.0.16 及以上直接可用低于这个版本必须用BEFORE INSERT和BEFORE UPDATE两个触发器补上并且注意触发器里的SIGNAL要写清楚提示语否则调试时只能看到一个 1644 错误码。跨版本交付的脚本最稳的做法是「CHECK 触发器」双写升级后把触发器删掉即可。5.2 触发器里更新本表报 1442 错误现象想写一个「成绩更新后自动重算班级平均分并写回sc表」的触发器创建成功一执行UPDATE就报ERROR 1442: Cant update table sc in stored function/trigger because it is already used by statement which invoked this stored function/trigger。原因MySQL 不允许在触发器里对本表做INSERT/UPDATE/DELETE因为这会引发递归和不可预期的执行顺序属于引擎层的硬限制不是权限问题。解决改数据的场景用BEFORE触发器加SET NEW.字段 ...只改当前行的新值不触发额外 DML需要落到别的表的场景写到独立的统计表或日志表里再由定时任务或应用层汇总。真要做级联统计交给存储过程显式调用别塞进触发器。5.3 DELIMITER 没改存储过程报 1064 语法错误现象把整段CREATE PROCEDURE粘进客户端执行报ERROR 1064 (42000): You have an error in your SQL syntax ... near at line N指向的位置往往在BEGIN之后第一行。原因MySQL 默认用分号作为语句结束符存储过程和触发器体内大量使用分号服务端读到第一个分号就认为语句结束剩下的部分自然成了语法错误。解决创建前用DELIMITER $$改结束符创建完再DELIMITER ;改回来。用图形化工具时要注意有些工具的「执行当前语句」按钮会自己切分语句把DELIMITER指令吃掉表现是命令行能跑、工具里跑不了。这种情况改用「执行脚本」而不是「执行语句」或者干脆回到命令行。5.4 外键建不上报 3780 或 1215现象ALTER TABLE sc ADD CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno);执行失败8.0 报ERROR 3780: Referencing column cno and referenced column cno in foreign key constraint are incompatible5.7 报ERROR 1215: Cannot add foreign key constraint。原因1215是个大黑匣子背后可能是四种情况——两边字段类型不完全一致CHAR(8)对VARCHAR(8)算不一致、字符集或排序规则不同、被引用列上没有索引、存储引擎不是 InnoDB。3780是 8.0 对类型不兼容的更精确报错。解决按顺序排查。SHOW CREATE TABLE对比两边字段定义字符集必须一致SHOW INDEX FROM course确认cno是主键或唯一索引SHOW TABLE STATUS看引擎。还有一种隐蔽情况表里已经有脏数据子表某行的cno在父表里不存在加外键时校验失败报错信息和类型问题长得一样先SELECT cno FROM sc WHERE cno NOT IN (SELECT cno FROM course)清一遍再试。5.5 中文姓名变成问号字符集三层不一致现象INSERT一个中文姓名查出来是???或者一串乱码SHOW CREATE TABLE显示utf8mb4看起来完全正常。原因字符集有四层服务端默认、库、表、列另外还有连接层三个变量character_set_client、character_set_connection、character_set_results。表和列对了但客户端连进来时用的是latin1数据在传输阶段就已经损坏存进库里的本来就是错的。解决先SHOW VARIABLES LIKE character_set%;看六个变量。连接层不一致就在连接串里加charsetutf8mb4命令行加--default-character-setutf8mb4。已经存进去的乱码改不回来只能清表重灌所以种子数据的导入脚本一定要带上字符集参数。另外utf8和utf8mb4在 MySQL 里不是一回事前者最多三字节存不了 emoji 和部分生僻字建库时别偷懒。6. 二次开发把实验脚本改造成可演示的选课服务6.1 用 Compose 固化环境脚本一键重建实验脚本最大的问题是「换台机器就跑不起来」版本、字符集、排序规则任何一个对不上都要重来。我一般会加一个docker-compose.yml把环境钉死顺便让建库脚本在容器首次启动时自动执行services: mysql: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: lab5root MYSQL_DATABASE: lab5 TZ: Asia/Shanghai command: - --character-set-serverutf8mb4 - --collation-serverutf8mb4_0900_ai_ci - --transaction-isolationREPEATABLE-READ ports: - 3306:3306 volumes: # 该目录下的 .sql 会按文件名排序自动执行序号前缀在这里很关键 - ./01_schema:/docker-entrypoint-initdb.d:rodocker-entrypoint-initdb.d只在数据目录为空时执行一次想重跑必须docker compose down -v把卷删掉。这个机制天然适合实验场景每次改完建表脚本删卷重启就是一次干净重建比手动DROP DATABASE可靠。6.2 把存储过程包成接口补一条冒烟测试存储过程本身不方便被前端调用我一般会用一层薄薄的应用代码包一下把CALL的四个参数转成 JSON 返回# 调用选课存储过程把 OUT 参数翻译成结构化结果 def enroll(cur, sno, cno, term): cur.execute( CALL sp_enroll(%s, %s, %s, code, msg), (sno, cno, term), ) cur.execute(SELECT code AS code, msg AS msg) row cur.fetchone() return {ok: row[code] 0, code: row[code], msg: row[msg]}配合一条冒烟测试先给某门课把容量改成 1连续调两次enroll第一次必须返回code0第二次必须返回code2。这条用例能同时验证存储过程的分支逻辑和FOR UPDATE的行锁是否生效比手工点界面快得多也是改完脚本后我必跑的第一条。从那以后我每次改完这套脚本都会先删卷重建、跑一遍约束验证、再跑这条选课冒烟测试三步全绿才敢提交。数据库实验里那些看起来玄学的报错九成都能归结到版本、字符集、事务边界这三件事上提前把环境固化住能省掉大半天排查。希望帮到你。本文还有配套的精品资源点击获取
简介这份资源面向西北工业大学软件学院数据库课程学习者聚焦实验五的E-Commerce数据库ER模式设计任务适合正在完成实验、复习概念模型建模或需要对照示例查漏补缺的同学。压缩包共20个文件约282KB以14个GIF操作演示、2个TXT文本和2个DOC说明文档为主并配有CDM概念模型文件与HTM实验说明页能较完整地呈现从需求理解、实体联系分析到ER图绘制的参考过程。已有911人学习说明该实验在课程学习中具有较高参考需求。读者可结合ER图、文字说明与分步界面演示理解用户注册、商品浏览、购物车、结算登录、配送信息、支付与订单确认等业务对象之间的实体、联系、属性和基数约束核对实验五的建模思路与结果也可将其作为E-Commerce项目概念设计的示例材料提升数据库ER建模与结果校验能力。1. 从一份 SQL 脚本包说起数据库实验五到底在练什么解压压缩包双击那个建库脚本屏幕上刷出一屏红色报错——这是很多人跑数据库实验五时的第一幕。它不是能双击运行的软件而是一套围绕「学生选课」场景组织起来的 SQL 脚本集合把完整性约束、触发器、存储过程、事务与并发这几件事串成一条线。它适合两类人正在做数据库实验课作业、需要一份能跑通的参照实现的学生以及想把业务规则下沉到数据库层、又不愿意啃官方手册的开发者。下面按「是什么 → 怎么跑 → 怎么改」的顺序把这套脚本拆开看。2. 环境与目录摸底把压缩包还原成一个能跑的实验工程2.1 目录里都有什么哪些能直接执行这类实验包的组织方式大同小异展开之后基本是「建库建表 → 逻辑对象 → 验证脚本 → 文档」四层。我一般先不急着执行而是把文件夹按序号排一遍确认哪些脚本是幂等的、哪些必须在前面跑完才能跑。下面是我拆包时会先画出来的一张目录地图文件名可能带不同前缀层级和依赖顺序基本不会变db-lab5/ ├── 01_schema/ │ ├── 01_database.sql -- 建库 字符集、排序规则 │ ├── 02_tables.sql -- 四张核心表 主外键 CHECK │ ├── 03_index.sql -- 辅助索引、唯一索引 │ └── 04_seed.sql -- 种子数据几十行 INSERT ├── 02_logic/ │ ├── 05_triggers.sql -- 成绩校验触发器 审计日志触发器 │ ├── 06_procedures.sql -- 选课、退课、成绩统计存储过程 │ └── 07_views.sql -- 成绩单视图、课表视图 ├── 03_verify/ │ ├── 08_concurrency_a.sql -- 并发会话 A │ ├── 09_concurrency_b.sql -- 并发会话 B │ └── 10_check_result.sql -- 结果校验查询 ├── docs/ │ └── ER 图与实验报告模板 └── README.md01_schema下的四个脚本是纯 DDL 加 DML彼此只依赖顺序不依赖环境属于「扔进任何一台装好 MySQL 的机器都能跑」的部分。02_logic里的触发器、存储过程、视图必须等表建完才能创建顺序反了会直接报「表不存在」。03_verify是给并发实验准备的里面通常是两个会话分别执行的语句块不能一次性全选执行必须开两个客户端窗口交替跑。提示先把01_schema整套跑通再动后面的脚本。建表阶段的问题最好在种子数据还没灌进去的时候暴露出来否则清库重来要多花一倍时间。2.2 建库建表脚本的执行顺序与字符集设置执行顺序上我习惯用命令行按文件名循环跑比在图形化工具里一个个点开靠谱得多。跑之前先确认一件事字符集到底在哪一层被定死。MySQL 的字符集有四层——服务端默认、库、表、列上层没写就继承下层继承链一断中文姓名就会变成问号。所以01_database.sql里最该看的是这几行-- 建库时一次性把字符集钉死避免后面建表继承到默认的 latin1 DROP DATABASE IF EXISTS lab5; CREATE DATABASE lab5 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE lab5;CHARACTER SET utf8mb4决定这层能存哪些字符中文、生僻字、emoji 都能进COLLATE utf8mb4_0900_ai_ci决定比较和排序规则ai是重音不敏感、ci是大小写不敏感。MySQL 8.0 默认就是这套写出来是给自己留后悔药如果目标机是 5.7把排序规则换成utf8mb4_general_ci5.7 根本没有0900这个排序规则照抄会报 1273 未知排序规则。然后按顺序灌进去# 按文件名顺序执行建库、建表、索引、种子数据 for f in 01_schema/0*.sql; do echo running $f mysql -h 127.0.0.1 -P 3306 -u root -p \ --default-character-setutf8mb4 \ --show-warnings \ $f || { echo failed at $f; break; } done--default-character-setutf8mb4管的是客户端这一层漏了它哪怕库里是utf8mb4终端里的中文照样可能变成乱码。--show-warnings是个血泪经验MySQL 很多语句失败是「警告级」的比如字段被截断、字符串超长不加这个参数你会以为脚本跑成功了直到查数据才发现少了几条。2.3 客户端连接命令行、图形化工具与连接串跑实验五至少要准备两个能独立提交事务的会话。图形化工具里开两个查询窗口未必是两个连接有些工具的同一个标签页会复用连接这会让并发实验的结论完全失真。稳妥的做法是一个命令行窗口加一个图形化工具窗口或者两个独立的终端。如果是用代码去调存储过程验证连接参数要比平时多留意几项# PyMySQL 连接参数并发实验必须关掉自动提交 import pymysql conn pymysql.connect( host127.0.0.1, port3306, userlab5, password******, databaselab5, charsetutf8mb4, autocommitFalse, # 关键手动控制事务边界 cursorclasspymysql.cursors.DictCursor, )autocommitFalse是并发验证的前提开成True的话每条语句自己就提交了你根本构造不出「事务 A 还没提交、事务 B 已经在读」的场景跑出来的结果会让人以为隔离级别设置没生效。charsetutf8mb4必须和库一致DictCursor只是让结果按字典取省得记列下标。3. 表结构与约束设计把选课场景落成一组能跑的 DDL3.1 四张核心表的主外键与级联策略整个实验包的地基是学生、课程、选课三张表加一张教师表选课表是全篇的枢纽——触发器挂它身上存储过程改它并发实验锁它。建表的第一个决策是存储引擎必须显式写ENGINEInnoDBMyISAM 不支持事务和外键后面的事务与并发实验会直接作废。-- 教师表被课程表弱引用删除教师不应连带删课 CREATE TABLE teacher ( tno CHAR(10) NOT NULL COMMENT 教师工号, tname VARCHAR(20) NOT NULL COMMENT 姓名, title VARCHAR(20) NULL COMMENT 职称, PRIMARY KEY (tno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课程表容量字段给存储过程判满用 CREATE TABLE course ( cno CHAR(8) NOT NULL, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0, tno CHAR(10) NULL, capacity SMALLINT UNSIGNED NOT NULL DEFAULT 60, PRIMARY KEY (cno), CONSTRAINT ck_course_credit CHECK (credit 0 AND credit 10), CONSTRAINT fk_course_teacher FOREIGN KEY (tno) REFERENCES teacher(tno) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;ON DELETE SET NULL和ON DELETE CASCADE的选择反映的是业务语义教师调走课程还在所以把tno置空学生退学他的选课记录没有保留价值直接级联删掉。很多同学在选课表上无脑写CASCADE结果删一门课把所有人的成绩一起带走这种翻车在验收时特别难看。-- 选课表成绩允许为空先选课后录成绩 CREATE TABLE sc ( sno CHAR(10) NOT NULL, cno CHAR(8) NOT NULL, term CHAR(11) NOT NULL DEFAULT 2024-2025-1, grade DECIMAL(5,2) NULL, PRIMARY KEY (sno, cno, term), KEY idx_sc_cno (cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT ck_sc_grade CHECK (grade IS NULL OR (grade 0 AND grade 100)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;课程这一侧用RESTRICT而不是CASCADE效果是「有成绩的课程删不掉」等于给数据加了一道保险。这个约束在实验报告里经常被老师追问答不出来就说明没理解外键动作的实际含义。3.2 CHECK、UNIQUE、DEFAULT 与索引的配合CHECK约束是实验五里最容易被忽略的一个点。它在 MySQL 8.0.16 之前只是被解析然后默默丢掉写上去不报错但也不生效插一条 200 分的成绩照样成功。如果目标环境的版本低于 8.0.16就得靠BEFORE触发器兜底这个坑放在第 5 章细说。索引这块联合主键(sno, cno, term)自带一个索引但遵循最左前缀按sno查、按sno cno查都能走索引单独按cno查就走不了了。而实验里「查某门课有多少人选」是高频操作所以idx_sc_cno不能省。同理student表上按院系统计的查询很多sdept上补一个普通索引几十万行数据时差别很明显。DEFAULT用得多的是时间和院系。created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP这类默认值能省掉应用层赋值但要注意DEFAULT CURRENT_TIMESTAMP在一张表里可以有多个ON UPDATE CURRENT_TIMESTAMP最好只给真正需要自动更新的字段否则一次无关的更新会把审计时间冲掉。3.3 建表脚本逐段拆解与执行验证表建完不能只看「Query OK」要主动构造几个非法数据把约束试出来。我习惯用三段式验证约束拦不拦得住、索引走不走得上、级联删得对不对。-- 1) 越界成绩看 CHECK 有没有真生效 INSERT INTO sc (sno, cno, grade) VALUES (2021000001, C0001, 200); -- 预期ERROR 3819: Check constraint ck_sc_grade is violated. -- 2) 主键重复看联合主键是否起作用 INSERT INTO sc (sno, cno, term) VALUES (2021000001, C0001, 2024-2025-1); INSERT INTO sc (sno, cno, term) VALUES (2021000001, C0001, 2024-2025-1); -- 预期ERROR 1062: Duplicate entry -- 3) 索引验证看 type 是不是 ref、key 是不是 idx_sc_cno EXPLAIN SELECT sno FROM sc WHERE cno C0001;第 3 段里EXPLAIN的type列是关键观察点ALL是全表扫ref是走了普通索引range是范围扫描const是主键或唯一索引等值命中。如果按cno查出来是ALL说明索引没建上或者被优化器放弃了这时候先SHOW INDEX FROM sc确认索引存在再考虑是不是数据量太小导致优化器认为全表扫更便宜——后者在种子数据只有几十行时非常常见属于正常现象不用急着改语句。4. 触发器与存储过程把业务规则下沉到数据库层4.1 BEFORE 触发器做数据校验AFTER 触发器写审计日志触发器分两个时机用BEFORE用来改数据或拦住数据AFTER用来记录既成事实。成绩越界这种事BEFORE里直接抛异常最干净抛出去之后应用层拿到的是一条明确的错误信息而不是静默失败。DELIMITER $$ CREATE TRIGGER trg_sc_grade_bi BEFORE INSERT ON sc FOR EACH ROW BEGIN -- 成绩允许为空但一旦有值就必须落在 0-100 IF NEW.grade IS NOT NULL AND (NEW.grade 0 OR NEW.grade 100) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 成绩必须落在 0-100 之间; END IF; END$$ DELIMITER ;DELIMITER $$是客户端指令不是 SQL 语句作用是临时把语句结束符从分号改成$$否则 MySQL 读到触发器体里第一个分号就以为语句结束了直接 1064 语法错误。NEW.grade指代即将写入的那行数据SIGNAL SQLSTATE 45000是用户自定义异常的通用写法45000表示「未处理的用户定义异常」应用层捕获后能拿到MESSAGE_TEXT里的中文提示。审计日志要记录「谁在什么时候把成绩从多少改成了多少」这类信息在UPDATE之后才成立所以用AFTERCREATE TABLE sc_audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sno CHAR(10) NOT NULL, cno CHAR(8) NOT NULL, old_grade DECIMAL(5,2) NULL, new_grade DECIMAL(5,2) NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_audit_sno (sno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; DELIMITER $$ CREATE TRIGGER trg_sc_grade_au AFTER UPDATE ON sc FOR EACH ROW BEGIN -- 是 NULL 安全等于NULL 改成 60 这种也要记一笔 IF NOT (OLD.grade NEW.grade) THEN INSERT INTO sc_audit_log (sno, cno, old_grade, new_grade) VALUES (OLD.sno, OLD.cno, OLD.grade, NEW.grade); END IF; END$$ DELIMITER ;这里的值得多说一句。普通等号在任一侧为NULL时结果是NULLIF NULL THEN走的是「假」分支于是「成绩从空改成 60」这条最需要审计的变更反而被漏掉了。是 NULL 安全等于两侧都为NULL时返回真一侧为空一侧有值时返回假正好符合「值真的变了才记录」的语义。4.2 存储过程的参数设计、事务边界与异常处理选课逻辑是这批脚本里唯一同时涉及读、判断、写的地方也是事务边界最该画清楚的地方。我一般把它做成三个输入参数加两个输出参数输入是学号、课程号、学期输出是一个状态码和一句人话提示调用方不需要解析错误文本。DELIMITER $$ CREATE PROCEDURE sp_enroll( IN p_sno CHAR(10), IN p_cno CHAR(8), IN p_term CHAR(11), OUT p_code INT, OUT p_msg VARCHAR(128) ) BEGIN DECLARE v_capacity SMALLINT UNSIGNED DEFAULT 0; DECLARE v_taken INT DEFAULT 0; DECLARE v_exists INT DEFAULT 0; -- 任何未捕获异常统一回滚并把原始错误信息带回调用方 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; GET DIAGNOSTICS CONDITION 1 p_msg MESSAGE_TEXT; SET p_code -1; END; START TRANSACTION; SELECT COUNT(*) INTO v_exists FROM sc WHERE sno p_sno AND cno p_cno AND term p_term; IF v_exists 0 THEN ROLLBACK; SET p_code 1, p_msg 已选过该课程; ELSE -- FOR UPDATE 锁住课程行防止两个会话读到同一个余量 SELECT capacity INTO v_capacity FROM course WHERE cno p_cno FOR UPDATE; SELECT COUNT(*) INTO v_taken FROM sc WHERE cno p_cno AND term p_term; IF v_taken v_capacity THEN ROLLBACK; SET p_code 2, p_msg 课程已满; ELSE INSERT INTO sc (sno, cno, term) VALUES (p_sno, p_cno, p_term); COMMIT; SET p_code 0, p_msg 选课成功; END IF; END IF; END$$ DELIMITER ;几个参数的含义要提前约定死p_code取0成功、1重复选课、2课程已满、-1未知异常这样应用层只判断数字就行。DECLARE EXIT HANDLER FOR SQLEXCEPTION必须写在START TRANSACTION之前它是声明不是语句写后面会报语法错误。GET DIAGNOSTICS CONDITION 1取的是最近一次异常的详细信息比手写「操作失败」有用得多。真正防超选的是那句FOR UPDATE。不加它两个会话可能同时读到「已选 59 人、容量 60」然后各自插入一条最终 61 人。加上行锁后后到的会话会阻塞在SELECT ... FOR UPDATE上等前一个事务提交再读读到的是 60走「课程已满」分支。调用方式CALL sp_enroll(2021000001, C0001, 2024-2025-1, code, msg); SELECT code AS code, msg AS msg;4.3 事务与并发验证两个会话同时选同一门课并发实验是这套脚本里最容易被跑成「看起来对但其实没验证」的部分。核心是要分清快照读和当前读。开两个会话都设成可重复读-- 会话 A SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT COUNT(*) FROM sc WHERE cno C0001; -- 假设得到 10-- 会话 B插入一条并提交 INSERT INTO sc (sno, cno, term) VALUES (2021000002, C0001, 2024-2025-1); COMMIT;此时回到会话 A 再查一次结果仍然是 10——这是快照读事务开始时的 Read View 已经固定别急着怀疑隔离级别没生效。换一种读法-- 会话 A当前读会看到会话 B 已提交的数据 SELECT COUNT(*) FROM sc WHERE cno C0001 FOR UPDATE; -- 得到 11这就是快照读和当前读的区别。做并发实验时必须在报告里写清楚用的是哪种读只贴一个数字说明不了任何问题。真正想验证隔离级别可以再跑一遍「不可重复读」把会话 A 换成READ COMMITTED同样两条SELECT第二条就能看到 11差异一目了然。5. 排查与避坑实验五最容易翻车的五个地方5.1 成绩插到 200 分也成功CHECK 约束形同虚设现象建表时明明写了CHECK (grade BETWEEN 0 AND 100)INSERT一条 200 分的记录照样成功SHOW CREATE TABLE里约束还在。原因MySQL 在 8.0.16 之前会解析CHECK子句但直接忽略不报错也不执行部分 MariaDB 版本行为也不一致。用图形化工具连的时候更容易踩到因为工具本身不提示版本差异。解决先SELECT VERSION();确认版本。8.0.16 及以上直接可用低于这个版本必须用BEFORE INSERT和BEFORE UPDATE两个触发器补上并且注意触发器里的SIGNAL要写清楚提示语否则调试时只能看到一个 1644 错误码。跨版本交付的脚本最稳的做法是「CHECK 触发器」双写升级后把触发器删掉即可。5.2 触发器里更新本表报 1442 错误现象想写一个「成绩更新后自动重算班级平均分并写回sc表」的触发器创建成功一执行UPDATE就报ERROR 1442: Cant update table sc in stored function/trigger because it is already used by statement which invoked this stored function/trigger。原因MySQL 不允许在触发器里对本表做INSERT/UPDATE/DELETE因为这会引发递归和不可预期的执行顺序属于引擎层的硬限制不是权限问题。解决改数据的场景用BEFORE触发器加SET NEW.字段 ...只改当前行的新值不触发额外 DML需要落到别的表的场景写到独立的统计表或日志表里再由定时任务或应用层汇总。真要做级联统计交给存储过程显式调用别塞进触发器。5.3 DELIMITER 没改存储过程报 1064 语法错误现象把整段CREATE PROCEDURE粘进客户端执行报ERROR 1064 (42000): You have an error in your SQL syntax ... near at line N指向的位置往往在BEGIN之后第一行。原因MySQL 默认用分号作为语句结束符存储过程和触发器体内大量使用分号服务端读到第一个分号就认为语句结束剩下的部分自然成了语法错误。解决创建前用DELIMITER $$改结束符创建完再DELIMITER ;改回来。用图形化工具时要注意有些工具的「执行当前语句」按钮会自己切分语句把DELIMITER指令吃掉表现是命令行能跑、工具里跑不了。这种情况改用「执行脚本」而不是「执行语句」或者干脆回到命令行。5.4 外键建不上报 3780 或 1215现象ALTER TABLE sc ADD CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno);执行失败8.0 报ERROR 3780: Referencing column cno and referenced column cno in foreign key constraint are incompatible5.7 报ERROR 1215: Cannot add foreign key constraint。原因1215是个大黑匣子背后可能是四种情况——两边字段类型不完全一致CHAR(8)对VARCHAR(8)算不一致、字符集或排序规则不同、被引用列上没有索引、存储引擎不是 InnoDB。3780是 8.0 对类型不兼容的更精确报错。解决按顺序排查。SHOW CREATE TABLE对比两边字段定义字符集必须一致SHOW INDEX FROM course确认cno是主键或唯一索引SHOW TABLE STATUS看引擎。还有一种隐蔽情况表里已经有脏数据子表某行的cno在父表里不存在加外键时校验失败报错信息和类型问题长得一样先SELECT cno FROM sc WHERE cno NOT IN (SELECT cno FROM course)清一遍再试。5.5 中文姓名变成问号字符集三层不一致现象INSERT一个中文姓名查出来是???或者一串乱码SHOW CREATE TABLE显示utf8mb4看起来完全正常。原因字符集有四层服务端默认、库、表、列另外还有连接层三个变量character_set_client、character_set_connection、character_set_results。表和列对了但客户端连进来时用的是latin1数据在传输阶段就已经损坏存进库里的本来就是错的。解决先SHOW VARIABLES LIKE character_set%;看六个变量。连接层不一致就在连接串里加charsetutf8mb4命令行加--default-character-setutf8mb4。已经存进去的乱码改不回来只能清表重灌所以种子数据的导入脚本一定要带上字符集参数。另外utf8和utf8mb4在 MySQL 里不是一回事前者最多三字节存不了 emoji 和部分生僻字建库时别偷懒。6. 二次开发把实验脚本改造成可演示的选课服务6.1 用 Compose 固化环境脚本一键重建实验脚本最大的问题是「换台机器就跑不起来」版本、字符集、排序规则任何一个对不上都要重来。我一般会加一个docker-compose.yml把环境钉死顺便让建库脚本在容器首次启动时自动执行services: mysql: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: lab5root MYSQL_DATABASE: lab5 TZ: Asia/Shanghai command: - --character-set-serverutf8mb4 - --collation-serverutf8mb4_0900_ai_ci - --transaction-isolationREPEATABLE-READ ports: - 3306:3306 volumes: # 该目录下的 .sql 会按文件名排序自动执行序号前缀在这里很关键 - ./01_schema:/docker-entrypoint-initdb.d:rodocker-entrypoint-initdb.d只在数据目录为空时执行一次想重跑必须docker compose down -v把卷删掉。这个机制天然适合实验场景每次改完建表脚本删卷重启就是一次干净重建比手动DROP DATABASE可靠。6.2 把存储过程包成接口补一条冒烟测试存储过程本身不方便被前端调用我一般会用一层薄薄的应用代码包一下把CALL的四个参数转成 JSON 返回# 调用选课存储过程把 OUT 参数翻译成结构化结果 def enroll(cur, sno, cno, term): cur.execute( CALL sp_enroll(%s, %s, %s, code, msg), (sno, cno, term), ) cur.execute(SELECT code AS code, msg AS msg) row cur.fetchone() return {ok: row[code] 0, code: row[code], msg: row[msg]}配合一条冒烟测试先给某门课把容量改成 1连续调两次enroll第一次必须返回code0第二次必须返回code2。这条用例能同时验证存储过程的分支逻辑和FOR UPDATE的行锁是否生效比手工点界面快得多也是改完脚本后我必跑的第一条。从那以后我每次改完这套脚本都会先删卷重建、跑一遍约束验证、再跑这条选课冒烟测试三步全绿才敢提交。数据库实验里那些看起来玄学的报错九成都能归结到版本、字符集、事务边界这三件事上提前把环境固化住能省掉大半天排查。希望帮到你。本文还有配套的精品资源点击获取
