MySQL Workbench 8.0图书馆数据库：如何避免同一本书重复借出？

针对你要阻止未归还图书重复借出的需求，以下两种MySQL原生方案可以直接实现，完全在数据库层面生效：

方案1：生成列+唯一索引（推荐，性能更优） #

利用MySQL的生成列将return_date为NULL的统一转换为一个固定的"未归还标记值"，再结合唯一索引来阻止重复：

1. 给checkouts表添加存储型生成列：

ALTER TABLE checkouts 
ADD COLUMN effective_return_date DATE GENERATED ALWAYS AS (
    CASE WHEN return_date IS NULL THEN '9999-12-31' ELSE return_date END
) STORED;

2. 创建唯一索引：

CREATE UNIQUE INDEX idx_book_effective_return ON checkouts (book_id, effective_return_date);

原理说明 #

• 当图书未归还（return_date为NULL），生成列effective_return_date会被设为9999-12-31（一个远未来的日期，确保不会和真实归还日期冲突）
• 同一本书的未归还记录会共享这个固定值，唯一索引会直接阻止重复插入
• 已归还的记录因为return_date各不相同，不会触发索引冲突

方案2：BEFORE INSERT触发器 #

通过触发器在插入前检查对应图书是否存在未归还记录，存在则抛出错误：

DELIMITER //
CREATE TRIGGER prevent_duplicate_checkout
BEFORE INSERT ON checkouts
FOR EACH ROW
BEGIN
    DECLARE unreturned_count INT;
    -- 查询该图书是否有未归还的借出记录
    SELECT COUNT(*) INTO unreturned_count
    FROM checkouts
    WHERE book_id = NEW.book_id AND return_date IS NULL;
    
    -- 如果存在未归还记录，抛出自定义错误
    IF unreturned_count > 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '该图书已有未归还的借出记录，无法重复借出';
    END IF;
END //
DELIMITER ;

补充说明 #

• 如果需要防止用户通过更新return_date将已归还记录改成未归还（导致重复），可以再创建一个BEFORE UPDATE触发器，逻辑类似，检查更新后的return_date是否为NULL，同时是否存在其他未归还记录
• 触发器的错误信息可以根据你的需求自定义

内容的提问来源于stack exchange，提问作者Setayesh