Python + MySQL 图书管理系统：从表结构设计到界面交互的完整复盘

图书管理系统是我在 2025 年 9 月到 12 月做的一个课程项目，也是我第一次真正把“数据库设计”和“界面交互”这两件事绑在一起考虑。技术栈是 Python + MySQL，我在团队里负责前端开发和界面设计，但表结构和查询基本也是我写的。这篇文章把我当时做的决策和踩的坑完整记一遍。

需求拆解与表结构设计

需求本身不复杂：图书录入、借阅归还、读者管理、借阅记录查询。但把它翻译成表结构的时候，第一个分歧就出现了——“一本书现在在不在馆里”，这个状态存在哪儿？

第一种方案是给 books 表加一个 status 字段，借出改成 borrowed，归还改回 available。第二种方案是 books 表只存 total_copies，状态完全由 borrow_records 里未归还的记录数推导出来。

我最后选了偏向第一种、但加了一层冗余的混合方案：

CREATE TABLE books (
  book_id      INT PRIMARY KEY AUTO_INCREMENT,
  isbn         VARCHAR(20)  NOT NULL UNIQUE,
  title        VARCHAR(100) NOT NULL,
  author       VARCHAR(50)  NOT NULL,
  publisher    VARCHAR(50),
  total_copies INT NOT NULL DEFAULT 1,
  available_copies INT NOT NULL DEFAULT 1,
  INDEX idx_title (title)
);

CREATE TABLE readers (
  reader_id  INT PRIMARY KEY AUTO_INCREMENT,
  card_no    VARCHAR(20) NOT NULL UNIQUE,
  name       VARCHAR(30) NOT NULL,
  phone      VARCHAR(20),
  max_borrow INT NOT NULL DEFAULT 5
);

CREATE TABLE borrow_records (
  record_id   INT PRIMARY KEY AUTO_INCREMENT,
  reader_id   INT NOT NULL,
  book_id     INT NOT NULL,
  borrow_date DATE NOT NULL,
  due_date    DATE NOT NULL,
  return_date DATE DEFAULT NULL,
  status      TINYINT NOT NULL DEFAULT 0,  -- 0 借出中 1 已归还 2 逾期
  CONSTRAINT fk_br_reader FOREIGN KEY (reader_id) REFERENCES readers(reader_id),
  CONSTRAINT fk_br_book   FOREIGN KEY (book_id)   REFERENCES books(book_id)
);

保留 available_copies 是刻意做的冗余：列表页要展示“在馆 2/5 本”，如果每渲染一行都去 borrow_records 里 count 一次，页面拉二十本书就是二十次子查询。代价是这个字段必须和借阅记录同步更新，这一点后面用事务兜住了。

status 字段我没有存“逾期”这个状态。逾期是随时间自然发生的，今天不逾期明天可能就逾期了，靠定时任务去刷状态很容易出现“记录还是借出中，但实际已经超期三天”的不一致。正确的做法是查询的时候现算。

用 JOIN 而不是冗余字段

借阅记录列表页要同时显示书名、读者姓名、是否逾期、逾期几天。用一条关联查询就能全部拿到：

SELECT
  r.record_id,
  b.title,
  rd.name AS reader_name,
  r.borrow_date,
  r.due_date,
  r.return_date,
  DATEDIFF(CURDATE(), r.due_date) AS overdue_days
FROM borrow_records r
JOIN books   b  ON b.book_id  = r.book_id
JOIN readers rd ON rd.reader_id = r.reader_id
WHERE r.return_date IS NULL
  AND r.due_date < CURDATE()
ORDER BY overdue_days DESC;

WHERE r.return_date IS NULL 和 r.due_date < CURDATE() 这两个条件配合，就等价于“当前逾期未还”。没有冗余的 reader_name 字段，好处是读者改了名字，历史记录跟着变，不会出现两张表里名字不一致的尴尬。

借还操作的事务处理

借书这一步要同时做两件事：往 borrow_records 插一条记录，把 books.available_copies 减一。如果第一条成功第二条失败，就会出现“记录里有这笔借阅，但库存没减”的脏数据。所以必须放在一个事务里：

def borrow_book(conn, reader_id, book_id):
    with conn.cursor() as cur:
        conn.begin()
        try:
            # 加行锁，避免两个人同时借走最后一本
            cur.execute(
                "SELECT available_copies FROM books WHERE book_id=%s FOR UPDATE",
                (book_id,)
            )
            available = cur.fetchone()[0]
            if available <= 0:
                conn.rollback()
                return False, "该书已无可借副本"

            cur.execute(
                "UPDATE books SET available_copies = available_copies - 1 "
                "WHERE book_id = %s", (book_id,)
            )
            cur.execute(
                "INSERT INTO borrow_records "
                "(reader_id, book_id, borrow_date, due_date, status) "
                "VALUES (%s, %s, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY), 0)",
                (reader_id, book_id)
            )
            conn.commit()
            return True, "借阅成功"
        except Exception as e:
            conn.rollback()
            return False, f"借阅失败：{e}"

归还就是把方向反过来：先校验记录是否已归还，再更新 return_date 和 status，最后把库存加回去。

界面交互：为什么我把菜单砍掉了

原来的界面是典型的“先点借阅管理 → 再点借书 → 再选读者 → 再选图书”，四层下去才能完成一次借阅。图书馆的老师试用了两分钟就说“太慢了”。

我的改法是两条：

一是统一弹窗表单。 所有“新建/编辑”类的操作都用同一个模态框组件，只是字段配置不同。读者、图书、借阅记录三处复用同一套样式和交互逻辑，改一次样式三处生效，也不用再跳页面。

二是列表行内操作。 每行数据右侧直接放“借出/归还/编辑”按钮，点了就地对当前行操作。检索框放在列表上方，输入卡号或书名回车即筛选。

两件事做完，借书和还书都变成了“搜索 → 点按钮”两步。这也是我在简历里写的“把操作路径从多级菜单精简到 2 步以内”的实际含义。

踩过的坑

中文字段排序。 ORDER BY title 出来的顺序完全不讲道理，因为默认的 utf8mb4_general_ci 是按 Unicode 码点排的，不是拼音。后来改成按 title 建立排序规则为 utf8mb4_zh_0900_as_cs 的索引，或者干脆在应用层用 pypinyin 生成一个 title_pinyin 字段来排。

日期比较写成字符串。 我一开始用 WHERE borrow_date = '2025-11-3'，查不到任何结果，因为 MySQL 的 DATE 类型和字符串比较时会做转换，而 '2025-11-3' 这种不补零的写法在某些情况下不能正确匹配。统一用 DATE_FORMAT(borrow_date, '%Y-%m-%d') 或者干脆传 datetime.date 对象就稳了。

并发借阅。 上面代码里的 SELECT ... FOR UPDATE 是我后来补的。最初版本是“先查库存，够就减”，两个窗口同时操作时确实出现过库存减到 -1 的情况。加了行锁之后，第二个请求会等第一个事务提交，读到的就是已经减完的值。

如果重做会怎么改

第一，加索引。borrow_records 上按 (reader_id, borrow_date) 建联合索引，读者查自己的借阅历史能直接从全表扫描变成范围扫描；return_date 上单独建一个，因为逾期统计几乎每次都要用它过滤。

第二，抽出 DAO 层。现在 SQL 散落在各个界面文件里，改一个字段要全局搜索。应该把每个表的读写收进一个类，界面只调方法，不拼 SQL。

第三，把状态计算统一成视图。逾期、可借数量这些都有明确的计算规则，做成 VIEW 比散落在各处的手写 SQL 更不容易出错。