图书馆管理系统数据库设计：从ER模型到第三范式与SQL落地

简介这是一份图书馆管理系统数据库设计的课程设计文档适合高校数据库相关课程的学生完成课程设计、毕业设计或复习数据库建模时参考。文档以图书馆管理系统为业务背景系统梳理了需求分析、概念模型设计、逻辑设计与数据库表结构设计全流程明确了安全性管理、读者信息管理、图书管理、图书流通管理等模块的功能划分并详细给出了读者信息表、图书信息表、图书征订表、图书借阅表、图书归还表、图书丢失表、图书罚款表、图书注销表等10张核心表的字段定义与关联关系同时配有E-R图、系统总流程图及关系模型转换原则方便读者对照理解和二次开发。资源包为doc格式共1个文件压缩包大小约374KB内容紧凑、结构完整便于直接阅读与修改。目前已有729人学习下载可作为数据库课程设计报告撰写和数据建模实践的有效参考。1. 图书馆管理系统数据库设计为什么分水岭在表结构而不在界面很多人拿到“图书馆管理系统数据库设计”这个题目第一反应是去网上找一份现成的表结构抄上去再补几段截图就交差。但真正到了答辩追问环节最先翻车的恰恰是数据库设计借阅记录为什么没做外键、同一本书买了两本为什么没法入库、逾期罚款为什么是写死的数字。这些问题全部藏在表结构里。这篇笔记从一个课程设计的完整流程讲起先做需求分析和 ER 模型再落成第三范式关系模式最后给出建库建表 SQL、借书还书存储过程和常见坑位。适合正在做课设的学生也适合想把手里的数据库设计流程再捋一遍的从业者。2. 需求分析与 ER 模型先把借阅流程拆成实体和用例2.1 从「借书-还书-罚款」流程里圈出实体与属性图书馆管理系统数据库设计最容易被跳过的环节就是需求分析。很多人打开 MySQL 就开始建表建到一半发现“一本书对应多个副本”没地方放或者“借阅记录和罚款记录”关系说不清再回头补表表结构就越补越乱。我一般会先花一小时把业务流程走一遍再动 ER 图。这个系统至少涉及两类角色。普通读者的操作是检索图书、查看某本书是否有可借副本、借书、还书、续借、查自己的逾期记录和罚款金额。管理员的操作是维护读者信息和读者类型、录入新书和副本、办理借还、处理罚款缴纳、查看借阅统计。把这两类角色的操作列成用例清单实体就会自己冒出来。用名词法圈实体读者、读者类型、图书、馆藏副本、借阅记录、罚款、管理员、图书分类。这里最关键的判断是把“图书”和“馆藏副本”拆成两个实体。图书馆里《数据库系统概论》可能买了 5 本读者借走的是其中某一本而不是“这本书”整体被借走。如果不拆你只能用“总数”和“在馆数”两个数字字段去表示借还时做加减法数据很快就对不上。读者类型单独成实体也很有必要。学生、教师、校外读者的可借本数和借阅天数都不一样如果把“可借 5 本、借 30 天”写死在读者表里每个读者都要重复存一份改规则时全表更新属于典型的冗余设计。把它拆成读者类型表读者表里只存放外键。2.2 画 ER 图的四步走实体、属性、联系与基数画 ER 图时按这个顺序走图谱会比较干净后续转关系模式也不用返工。第一步先画强实体读者类型、图书分类、管理员、图书书目。第二步补依赖实体读者依赖读者类型馆藏副本依赖图书书目它们都是弱实体或从属实体的位置。第三步画借阅联系读者和馆藏副本之间是 M:N 联系但要注意借阅本身带借出日期、应还日期、实际还书日期、状态这些属性不能挂在读者上也不能挂在副本上必须把“借阅”升级为一个独立的联系实体也就是借阅记录表。第四步把罚款挂在借阅记录下面一张借阅记录在“逾期未还并产生罚金”时对应一条或多条罚款记录。实体属性用一张表整理清楚后续建表和写数据字典都能直接复用实体关键属性主键标识读者类型类型名称、最大可借本数、可借天数、续借次数reader_type_id读者姓名、电话、密码、状态、注册日期reader_id图书分类分类名称category_id图书书目ISBN、书名、作者、出版社、出版日期、总册数book_id馆藏副本副本编号、存放位置、状态copy_id借阅记录借出日期、应还日期、实际还书日期、状态borrow_id罚款逾期天数、每日罚金、总金额、缴纳状态fine_id管理员姓名、账号、角色admin_id实体间的基数关系在图上标注清楚读者类型 1:N 读者图书分类 1:N 图书书目图书书目 1:N 馆藏副本读者 1:N 借阅记录馆藏副本 1:N 借阅记录借阅记录 1:N 罚款。这张 ER 图完成后可以对照检查每个业务动作是否都能落到一条路径上。查书是图书书目到馆藏副本借书是读者到借阅记录到馆藏副本罚款是借阅记录到罚款。2.3 常见误用把「一本书是否可借」设计成单个布尔字段课程设计里最常出现的返工点就是副本状态设计。有人喜欢在图书书目表里放一个“是否在馆”或“可借状态”字段实体关系里根本不出现“馆藏副本”。这种做法在只有单本书的玩具系统里能跑但是只要涉及一个真实场景就露馅同一本书有三个副本其中一本被借走、一本在维修、一本在架你一个布尔字段怎么表达三种状态再往后同学同时借走《数据库系统概论》上册的两本副本借阅记录里只有 book_id没法知道还回来的具体是哪一本。另一个常见误用是把“在馆数量”做成图书表里的一个冗余字段每次借还都由程序去 update 这个数字。表面上省了一次联表查询实际上只要有一次并发或漏更新数量就对不上。我一般建议课程设计在 ER 图阶段就拆出馆藏副本实体用 copy 表的 status 字段表达“在馆、借出、维修、下架”四种状态图书表里只保留 ISBN、书名这类静态属性。这样做还有一个好处借阅记录的外键可以直接指向 copy_id还书时精确归还到具体副本数据语义清楚答辩时不心虚。3. 从 ER 图到关系模式用第三范式拆出 10 张表3.1 ER 转关系模式的三种映射规则ER 图画完转换关系模式有固定的套路。1:1 联系一般并入某一端比如管理员和他的工作账号基本可以合成一张表不需要单独建联系表。1:N 联系在 N 端加外键比如读者类型和读者是 1:N就在读者表里加 reader_type_id 外键而不是在读者类型表里存一堆读者 ID。M:N 联系必须拆中间表读者和馆藏副本是多对多一张读者表、一张副本表无论如何都不能直接表达“同一本书在不同时间被不同人借”必须通过借阅记录这张中间实体表把两个外键放进去。这里要讲清楚“借阅记录”是联系还是实体。它最初来源于“读者-图书”的 M:N 联系但它携带了借出日期、应还日期、实际还书日期、状态这些自己的属性所以在 ER 模型里就把它升级成了联系实体。转换时它就变成一张普通的关系表borrow_id 做主键reader_id 和 copy_id 做外键。课程设计答辩时老师很喜欢问这里答得出“联系升级为实体”就是加分项。3.2 逐表给出关系模式主键、外键与字段类型选型下面给出一个可以直接复用的关系模式集合覆盖 10 张表其中预约记录和管理员操作日志属于扩展表课程设计按需取舍。主键用下划线标出外键用“FK”标出。读者类型表(reader_type)reader_type_id, type_name, max_books, borrow_days, renew_times读者表(reader)reader_id, reader_type_id(FK), reader_name, reader_phone, password_hash, status, register_date图书分类表(category)category_id, category_name图书书目表(book)book_id, isbn, title, author, publisher, publish_date, category_id(FK), total_copies馆藏副本表(copy)copy_id, book_id(FK), copy_no, status, location借阅记录表(borrow_record)borrow_id, reader_id(FK), copy_id(FK), borrow_date, due_date, return_date, status罚款表(fine)fine_id, borrow_id(FK), overdue_days, unit_amount, total_amount, paid_flag, pay_date管理员表(admin)admin_id, admin_name, account, password_hash, role管理员操作日志表(operation_log)log_id, admin_id(FK), operation_type, operation_time, detail图书预约表(reservation)reservation_id, reader_id(FK), book_id(FK), reserve_date, status字段类型选型是一份可以直接抄的作业我一般按这几个原则来定。日期统一用 DATE借阅记录里的借出时间如果可能精确到时分用 DATETIME但课程设计里 DATE 足够还能避开时区问题。数量类字段用 TINYINT 或 SMALLINT加 UNSIGNED 约束可借本数 255 以内 TINYINT 就够不要用 INT 存“还差几本”这种小数字。金额必须用 DECIMAL(10,2)不要用 FLOAT浮点数算钱会出现 0.1 加不出 0.3 的尴尬。电话用 VARCHAR(20)不要用 BIGINT因为电话号码可能包含区号、分机而且前导零会被数字类型吃掉。状态字段用 TINYINT 配注释不要用 ENUMENUM 改枚举值时要改表结构程序里也不方便比较。3.3 做一次 3NF 检查借阅记录里不能存读者姓名关系模式列完后需要进行一次范式检查。第三范式的核心是非主键列不能依赖于另一个非主键列也就是消除传递依赖。拿借阅记录表来说如果我在 borrow_record 里直接存 reader_name这张表就违反 3NF。理由很简单读者姓名由 reader_id 决定借阅记录里每借一本书都重复存一遍读者改名字时所有借阅记录都要跟着改不改就产生不一致。正确的做法是借阅记录只存 reader_id需要姓名时 JOIN 读者表查。这一个点被老师问到的概率极高值得在设计说明里单独写一段。罚款表和借阅记录之间也存在类似的依赖关系。罚款金额由 overdue_days 乘以每日罚金 unit_amount 得到如果罚款表再手工存一个 total_amount 字段就形成了“total_amount 依赖 overdue_days而 overdue_days 依赖 borrow_id”的传递依赖严格说违反 3NF。MySQL 5.7 以上支持生成列可以直接让数据库维护这个计算字段不需要应用层每次算好再插入。这样既保留了查询性能又避免冗余不一致。馆藏副本表里的 status 字段是否冗余有人会说副本有没有被借出可以从借阅记录里 status 为“借出中”的记录推导出来。理论上可以但每次判断都要查借阅记录而且“检修中、下架”这些状态并不来自借阅记录。因此在副本表里保留 status 是合理的设计它表达的是“物理副本当前处于什么可用状态”和借阅记录是两个维度的信息。课程设计里抓住这个度完全按 3NF 推导不现实为状态字段保留冗余是可接受的但可借数量这类能算出来的聚合数字不要落表。4. 建库建表 SQL 落地字段长度、约束与字符集参数的取舍4.1 建库与字符集为什么全库统一 utf8mb4_unicode_ci关系模式定了进入建库阶段。第一个要确定的参数是字符集。很多课程设计在 Windows 本机 MySQL 里建表时直接沿用默认 latin1 或 gbk导入数据时中文乱码再手工改表改完还发现排序规则对不上。我建议建库时一次性指定位。utf8mb4 是 UTF-8 的完整实现能存中文和 Emoji而 utf8 在 MySQL 里是 utf8mb3遇到生僻字和部分表情符号会报错。排序规则我用 utf8mb4_unicode_ci它比 general_ci 的字符比较更准确代价是略慢一点课程设计的数据量根本感觉不到差别。CREATE DATABASE IF NOT EXISTS library DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE library;这段建库语句里有三个参数值得注意CHARACTER SET 指定全库默认字符集COLLATE 指定排序规则IF NOT EXISTS 是课程设计阶段的后悔药防止重建时报错。这里有个容易遗漏的点客户端连接也要用 utf8mb4否则建库是 utf8mb4、插入还是 gbk照样乱码。在后面插入测试数据前先执行 SET NAMES utf8mb4JDBC 连接串里加上 characterEncodingutf8mb4这两处和建库保持三方一致。4.2 六张核心表的创建 SQL 与建表顺序建表顺序要顺着外键依赖来先建没有外键的表再建被依赖的表最后建引用别人的表。下面从读者类型、读者、图书书目、馆藏副本到借阅记录、罚款依次创建注释里说明每个字段的取舍。CREATE TABLE reader_type ( reader_type_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 读者类型ID, type_name VARCHAR(20) NOT NULL COMMENT 类型名称学生/教师/校外, max_books TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT 最大可借本数, borrow_days TINYINT UNSIGNED NOT NULL DEFAULT 30 COMMENT 可借天数, renew_times TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 续借次数上限, PRIMARY KEY (reader_type_id) ) ENGINEInnoDB COMMENT读者类型表; CREATE TABLE reader ( reader_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 读者ID, reader_type_id TINYINT UNSIGNED NOT NULL COMMENT 读者类型外键, reader_name VARCHAR(50) NOT NULL COMMENT 姓名, reader_phone VARCHAR(20) DEFAULT NULL COMMENT 电话唯一, password_hash CHAR(64) NOT NULL COMMENT 密码SHA256, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 0停用, register_date DATE NOT NULL COMMENT 注册日期, PRIMARY KEY (reader_id), UNIQUE KEY uk_phone (reader_phone), CONSTRAINT fk_reader_type FOREIGN KEY (reader_type_id) REFERENCES reader_type (reader_type_id) ) ENGINEInnoDB COMMENT读者表;这段 SQL 里有几个参数值得解释。reader_type_id 用 TINYINT UNSIGNED取值范围 0 到 255读者类型不会超过这个数省空间。password_hash 用 CHAR(64)对应 SHA-256 哈希值长度固定不要用 VARCHAR(255) 存哈希纯浪费。status 用 TINYINT 而不是 ENUM便于后续扩展“黑名单”等状态。外键约束命名为 fk_reader_type方便后面根据约束名做删除和排错。引擎必须指定 InnoDB课程设计的库普遍启用了外键和事务MyISAM 不支持这些特性默认引擎也要在配置文件里确认。接着是图书相关两张表。book 表存书目信息isbn 是全国统一编码适合做唯一键copy 表存物理副本同一本书的不同副本通过 copy_no 区分。CREATE TABLE book ( book_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 书目ID, isbn VARCHAR(20) NOT NULL COMMENT ISBN长度不固定, title VARCHAR(100) NOT NULL COMMENT 书名, author VARCHAR(50) NOT NULL COMMENT 作者, publisher VARCHAR(50) DEFAULT NULL COMMENT 出版社, publish_date DATE DEFAULT NULL COMMENT 出版日期, category_id TINYINT UNSIGNED DEFAULT NULL COMMENT 分类外键, total_copies SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 馆藏总册数, PRIMARY KEY (book_id), UNIQUE KEY uk_isbn (isbn), CONSTRAINT fk_book_category FOREIGN KEY (category_id) REFERENCES category (category_id) ) ENGINEInnoDB COMMENT图书书目表; CREATE TABLE copy ( copy_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 副本ID每册一行, book_id INT UNSIGNED NOT NULL COMMENT 书目外键, copy_no VARCHAR(20) NOT NULL COMMENT 馆藏副本编号, status TINYINT NOT NULL DEFAULT 0 COMMENT 0在馆 1借出 2维修 3下架, location VARCHAR(30) DEFAULT NULL COMMENT 存放位置如A区3排, PRIMARY KEY (copy_id), UNIQUE KEY uk_book_copy (book_id, copy_no), CONSTRAINT fk_copy_book FOREIGN KEY (book_id) REFERENCES book (book_id) ) ENGINEInnoDB COMMENT馆藏副本表;isbn 用 VARCHAR(20) 而不是纯数字是因为 ISBN 含连字符长度也会变如果课程设计只用纯数字 ISBN-13改成 CHAR(13) 也没问题。total_copies 在业务上会被用于展示“共几册”它和 copy 表的实际行数理论上要一致这里属于允许存在的统计冗余。copy 表上加 (book_id, copy_no) 联合唯一键保证同一本书不会出现两个相同副本编号这是数据完整性的一道防线。借阅记录和罚款是整张设计里最见功力的两张表。借阅记录里外键指向 reader 和 copy而不是 book为什么因为读者借的是具体某一册副本还书时才能精确匹配。due_date 在借出时按读者类型的 borrow_days 动态计算并写入属于快照字段之后读者类型规则变了也不能影响已经发生的借阅。CREATE TABLE borrow_record ( borrow_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 借阅记录ID, reader_id INT UNSIGNED NOT NULL COMMENT 读者外键, copy_id INT UNSIGNED NOT NULL COMMENT 副本外键, borrow_date DATE NOT NULL COMMENT 借出日期, due_date DATE NOT NULL COMMENT 应还日期由借书过程计算, return_date DATE DEFAULT NULL COMMENT 实际还书日期未还时为空, status TINYINT NOT NULL DEFAULT 0 COMMENT 0借出中 1已归还 2逾期未还, PRIMARY KEY (borrow_id), KEY idx_reader (reader_id), KEY idx_copy_status (copy_id, status), KEY idx_due (due_date), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader (reader_id), CONSTRAINT fk_borrow_copy FOREIGN KEY (copy_id) REFERENCES copy (copy_id), CONSTRAINT chk_due CHECK (due_date borrow_date) ) ENGINEInnoDB COMMENT借阅记录表; CREATE TABLE fine ( fine_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 罚款ID, borrow_id INT UNSIGNED NOT NULL COMMENT 借阅记录外键, overdue_days SMALLINT UNSIGNED NOT NULL COMMENT 逾期天数, unit_amount DECIMAL(5,2) NOT NULL DEFAULT 0.10 COMMENT 每日罚金快照, total_amount DECIMAL(10,2) GENERATED ALWAYS AS (overdue_days * unit_amount) STORED COMMENT 生成列, paid_flag TINYINT NOT NULL DEFAULT 0 COMMENT 0未缴 1已缴, pay_date DATE DEFAULT NULL COMMENT 缴纳日期, PRIMARY KEY (fine_id), CONSTRAINT fk_fine_borrow FOREIGN KEY (borrow_id) REFERENCES borrow_record (borrow_id) ) ENGINEInnoDB COMMENT罚款表;借阅记录里我建了三个普通索引而不是只靠主键。idx_reader 覆盖“查某人借了哪些书”idx_copy_status 覆盖“查某副本是否在馆或借出”idx_due 覆盖“批量查逾期”。课程设计数据量小索引收益不明显但这是答辩会被追问的点你的查询走哪个索引。CHECK 约束如果用的是 MySQL 8.0建表时会被强制执行如果本机是 5.7这个约束会被创建但忽略程序层还得自己校验。罚款表里的 total_amount 是生成列MySQL 5.7 之后都支持它是靠数据库算出来的不存在应用层忘记更新的问题。unit_amount 存的是“当时”的每日罚金快照以后罚金调价也不能影响历史账单这就是它不搞 JOIN 到配置表的原因。4.3 测试数据与重置脚本给调试留一颗后悔药建完表先插几条最小测试数据把借书逻辑跑通。插入顺序同样受外键约束影响先读者类型再读者再书目和副本最后借阅记录。下面这段 SQL 演示了借书场景下的数据准备SET NAMES utf8mb4; INSERT INTO reader_type (type_name, max_books, borrow_days) VALUES (学生, 5, 30), (教师, 10, 60); INSERT INTO reader (reader_type_id, reader_name, reader_phone, password_hash, register_date) VALUES (1, 张明, 13800000001, SHA2(123456, 256), CURDATE()); INSERT INTO book (isbn, title, author, publisher, publish_date, total_copies) VALUES (978-7-302-00000-1, 数据库系统概论, 王珊, 清华大学出版社, 2020-01-01, 2); INSERT INTO copy (book_id, copy_no, status, location) VALUES (1, T001, 0, A区3排), (1, T002, 0, A区3排); INSERT INTO borrow_record (reader_id, copy_id, borrow_date, due_date, status) VALUES (1, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY), 0);这里的 SET NAMES utf8mb4 解决客户端字符集问题SHA2 函数直接生成密码摘要课程设计不需要搞复杂的注册接口建表时预留 password_hash 字段、插入时用 SHA2 即可。DATE_ADD 根据借出日期和可借天数算应还日期对应读者类型表里的 borrow_days。注意我这里借出的是 copy_id1也就是 T001 这一册另一册 T002 状态还是 0后续别人还能借这才是副本拆分的价值。调试期最需要的是一个一键重置脚本。我习惯维护一个 init.sql开头先禁用外键检查再按从子到父的顺序删表最后重新执行建表语句。删表顺序错了会报“外键约束阻止删除”这个坑下一章会展开。mysql -u root -p library init.sql重置逻辑在 init.sql 的开头SET FOREIGN_KEY_CHECKS 0; DROP TABLE IF EXISTS fine; DROP TABLE IF EXISTS borrow_record; DROP TABLE IF EXISTS copy; DROP TABLE IF EXISTS book; DROP TABLE IF EXISTS reader; DROP TABLE IF EXISTS reader_type; SET FOREIGN_KEY_CHECKS 1;SET FOREIGN_KEY_CHECKS 0 是调试期的后悔药上课设验收前一定把它恢复成 1否则外键约束形同虚设。init.sql 这个文件本身也是报告附件的一部分答辩现场重建整个库比截十张图更有说服力。5. 图书馆管理系统数据库设计的 4 个常见问题与排查5.1 中文字段乱码表结构与连接串字符集不一致现象插入中文后查询显示“???”或一堆乱码英文数据正常。原因分成两层建库时用了默认字符集或者表结构是 utf8、连接串却是 gbk前者导致数据落库时已经被转坏后者是同一套数据被客户端误读。解决方法是统一三处字符集建库语句带 DEFAULT CHARACTER SET utf8mb4建表时不单独指定继承库客户端连接串按对应驱动设置。排查时先看库、再看表、最后看连接SHOW CREATE DATABASE library; SHOW CREATE TABLE reader;如果库和表都是 utf8mb4问题就出在连接层。MySQL 命令行执行 SET NAMES utf8mb4JDBC 连接串加 characterEncodingutf8mb4 和 useUnicodetrue。乱码数据一旦落库改字符集不一定能修回来只能删掉重导。所以建库时把字符集写进 create 语句比事后补救省事得多。5.2 主表删不掉外键约束、删除顺序与重置技巧现象执行 DROP TABLE reader 时报 ERROR 1217提示 Cannot delete or update a parent row。原因borrow_record 表的外键引用了 reader 表MySQL 默认外键策略是 RESTRICT只要子表还有记录引用父表就不能删。解决分两种情况。日常调试重置时按子到父的顺序删也就是先删 fine 和 borrow_record再删 copy、book、reader最后删 reader_type。如果是在初始化脚本里整体重建直接 SET FOREIGN_KEY_CHECKS0删完再置 1省得每次数顺序。这是调试技巧不是逃避外键。课程设计里不建议把外键改成 ON DELETE CASCADE因为删除读者会连带删掉借阅历史业务上说不通答辩容易被追问。5.3 副本状态不一致冗余「可借数」字段引发的数据血案现象书目列表显示“可借 1 本”但进入详情发现所有副本都是借出状态或者还书成功后列表的可借数没有增加。原因设计时为了前端展示方便在 book 表里放了 available_copies 冗余字段借书时减一、还书时加一由应用层维护。只要有一次并发、一次异常退出字段和真实状态就对不上而且对不上之后没有任何手段能自动修复。解决思路是让数据库只保留一个事实源副本的 status。可借数量用查询实时计算不落字段。课程设计阶段数据量小这个查询开销可以忽略。如果确实要展示建一个视图CREATE VIEW v_book_available AS SELECT b.book_id, b.title, COUNT(c.copy_id) AS available_copies FROM book b LEFT JOIN copy c ON b.book_id c.book_id AND c.status 0 GROUP BY b.book_id, b.title;视图由数据库根据副本状态实时聚合不存在应用层加错数的问题。把“可借数”做成 SELECT 里的 COUNT而不是 UPDATE 里的减一这是从根上消除不一致。5.4 借书并发超借没有按物理副本锁行导致的翻车现象两个同学同时提交借同一本唯一在馆的副本系统都给通过最后副本表里 status 是借出可借阅记录却有两条。原因借书逻辑是先 SELECT 判断 status0再 INSERT 借阅记录再 UPDATE 副本状态。两个事务同时读到在馆判断都通过结果都执行了插入和更新。解决借书逻辑必须对副本行加锁或者干脆用“条件更新 受影响行数判断”的方式。我一般用后者因为 InnoDB 的 UPDATE 会锁行能天然避免并发穿透UPDATE copy SET status 1 WHERE copy_id 1 AND status 0; -- 如果 ROW_COUNT() 返回 0说明没有抢到这本副本如果 UPDATE 影响行数是 1说明这一册从“在馆”被原子地改成了“借出”再插入借阅记录如果影响行数是 0直接返回“该副本不可借”。整个过程要包在事务里见第 6 章的存储过程实现。这个坑在单人演示时完全暴露不出来但课设验收时老师常会模拟并发请求按行更新是稳妥的写法。6. 用视图、存储过程与三条验证 SQL 给设计加分视图和存储过程是课程设计里成本最低、收益最明显的加分项。视图把业务查询封装成“假表”存储过程把借书还书流程包成事务既展示了对数据库对象的掌握也顺手解决了上一章说的并发问题。借书存储过程的核心写法如下DELIMITER // CREATE PROCEDURE sp_borrow_book(IN p_reader_id INT, IN p_copy_id INT) BEGIN DECLARE v_due_days INT DEFAULT 30; START TRANSACTION; SELECT t.borrow_days INTO v_due_days FROM reader r JOIN reader_type t ON r.reader_type_id t.reader_type_id WHERE r.reader_id p_reader_id; UPDATE copy SET status 1 WHERE copy_id p_copy_id AND status 0; IF ROW_COUNT() 0 THEN ROLLBACK; SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 该副本不可借; END IF; INSERT INTO borrow_record (reader_id, copy_id, borrow_date, due_date, status) VALUES (p_reader_id, p_copy_id, CURDATE(), DATE_ADD(CURDATE(), INTERVAL v_due_days DAY), 0); COMMIT; END// DELIMITER ;这个存储过程把“查应还日期、锁副本行、写借阅记录”三件事放进一个事务要么全部成功要么全部回滚。SIGNAL 语句用于主动报错应用层捕获到异常就提示用户不需要再回表查状态。还书过程对称实现UPDATE copy SET status 0UPDATE borrow_record SET return_date CURDATE(), status 1插入罚款逻辑按需放在同一事务里。答辩时准备三条验证 SQL比口头讲设计更管用。第一条验证可借视图SELECT * FROM v_book_available第二条验证逾期统计SELECT r.reader_name, COUNT(*) FROM borrow_record b JOIN reader r ON b.reader_id r.reader_id WHERE b.status 2 GROUP BY r.reader_name第三条验证索引使用EXPLAIN SELECT * FROM borrow_record WHERE reader_id 1 AND status 0观察 explain 结果里 type 和 key 字段能说明查询是否走 idx_reader 或 idx_copy_status。我会把这三条查询连同初始化脚本整理成一个 sql 目录验收时现场重新建库、跑存储过程、执行查询让老师看比自己对着 PPT 念有效得多。这门课设计做到最后我做事的习惯是先画 ER 图再建表反过来做十有八九要返工脚本能一键重建调试才敢于大胆改表。希望帮到你。本文还有配套的精品资源点击获取