mysql搭建图书馆管理系统数据库架构

设计三张表时应避免冗余字段，只用book_id、reader_id作外键；借阅状态用ENUM而非布尔值；高频查询需建联合索引；统计借阅次数用LEFT JOIN+COUNT(*)；大数据量时按年分区。

设计图书、读者、借阅三张核心表时怎么避免字段冗余

直接用 book_id、reader_id 作主键，别在 borrow_records 表里重复存书名或读者姓名。冗余字段会导致更新不一致——比如修改一本书的标题，却漏改借阅记录里的副本，后续查历史借阅就显示旧书名。

正确做法是只存外键：

CREATE TABLE books (
  book_id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  isbn CHAR(13),
  author VARCHAR(100),
  pub_year YEAR
);

CREATE TABLE readers (
  reader_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  card_no CHAR(10) UNIQUE,
  reg_date DATE DEFAULT (CURRENT_DATE)
);

CREATE TABLE borrow_records (
  record_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  book_id INT NOT NULL,
  reader_id INT NOT NULL,
  borrow_date DATE DEFAULT (CURRENT_DATE),
  return_date DATE NULL,
  FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE RESTRICT,
  FOREIGN KEY (reader_id) REFERENCES readers(reader_id) ON DELETE RESTRICT
);

注意：ON DELETE RESTRICT 防止误删图书或读者导致借阅记录“悬空”；return_date 允许为 NULL，表示尚未归还。

借阅状态用布尔值还是枚举更合适

别用 TINYINT(1) 模拟布尔值存“是否已还”，MySQL 的 BOOLEAN 实际就是 TINYINT(1) 别名，语义弱、扩展差。一旦业务增加“已预约”“已挂失”等状态，就得改字段类型或加新列。

推荐用 ENUM 显式定义状态集：

ALTER TABLE borrow_records 
  ADD COLUMN status ENUM('borrowed', 'returned', 'overdue') DEFAULT 'borrowed';

好处是：数据库层强制校验、查询可读性强（WHERE status = 'overdue'）、应用层不用维护魔法数字映射。但注意 ENUM 值变更需 ALTER TABLE，线上表大时要评估锁表影响。

如何快速查某读者当前未还的全部图书

高频查询必须加联合索引，否则每次扫描全表 borrow_records，数据量一过万就明显卡顿。

针对这个查询：

SELECT b.title, br.borrow_date 
FROM borrow_records br 
JOIN books b ON br.book_id = b.book_id 
WHERE br.reader_id = 123 AND br.return_date IS NULL;

应该建索引：

CREATE INDEX idx_reader_borrow ON borrow_records (reader_id, return_date, book_id);

理由：reader_id 是等值条件，放最左；return_date IS NULL 是范围/空值判断，放第二位能利用索引下推；book_id 放最后，用于快速回表查书名，避免二次查询 books 表。

MySQL 8.0 以下版本不支持窗口函数，怎么统计每本书被借了多少次

用 GROUP BY + COUNT(*) 最直接：

SELECT 
  b.book_id,
  b.title,
  COUNT(br.record_id) AS borrow_times
FROM books b
LEFT JOIN borrow_records br ON b.book_id = br.book_id AND br.return_date IS NOT NULL
GROUP BY b.book_id, b.title
ORDER BY borrow_times DESC
LIMIT 10;

关键点：

• LEFT JOIN 保证没被借过的书也出现在结果里（borrow_times = 0）
• AND br.return_date IS NOT NULL 放在 ON 子句里，不是 WHERE，否则会把未借出的书过滤掉
• 如果只要“至少被借过一次”的热门书，改用 INNER JOIN 并去掉 IS NOT NULL 条件即可

真实部署时，borrow_records 表数据增长快，建议按年分区（如按 borrow_date RANGE 分区），否则单表超千万行后 GROUP BY 会变慢。