你可以用触发器来阻止
对于最终添加DELIMITER所需的触发器，取决于gui和方法
在批量插入中，将出现1个错误，阻止插入该会话中的所有行

CREATE TABLE books
    (`id` int, `title` varchar(14), `author` varchar(5))
;
    
INSERT INTO books
    (`id`, `title`, `author`)
VALUES
    (1, 'my first book', 'sara'),
    (2, 'my second book', 'maman'),
    (3, 'my third book', 'baba')
;

CREATE TABLE users
    (`id` int, `full_name` varchar(4), `last_login` varchar(10))
;
    
INSERT INTO users
    (`id`, `full_name`, `last_login`)
VALUES
    (1, 'ala', '1973-07-03'),
    (2, 'badi', '1972-07-03'),
    (3, 'saly', '2015-10-18')
;

CREATE TABLE checkouts
    (`id` int, `user_id` int, `book_id` int, `checkout_date` Date, `return_date` date
  )
;

CREATE TRIGGER before_wcheckouts_insert
BEFORE INSERT
ON checkouts FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
    IF EXISTS( SELECT 1 FROM checkouts 
  WHERE  `book_id` = NEW.`book_id` AND (`return_date` > NEW.`checkout_date` OR `return_date` IS NULL))   THEN
        set msg = "INSERTTriggerError: Trying to checkout book, that isn't returned.";
        signal sqlstate '45000' set message_text = msg;
    END IF; 

END

INSERT INTO checkouts
    (`id`, `user_id`, `book_id`, `checkout_date`, `return_date`)
VALUES
    (1, 1, 1, '2000-10-18', '2017-10-18'),
    (2, 1, 2, '2015-11-18', NULL),
    (3, 2, 3, '2015-10-28', '2025-10-18')
;

INSERT INTO checkouts
    (`id`, `user_id`, `book_id`, `checkout_date`, `return_date`)
VALUES

  (4,   3 , 2   , '2016-10-18', NULL)

INSERTTriggerError: Trying to checkout book, that isn't returned.

SELECT * FROM checkouts

| id|用户id|图书ID|结帐日期|返回日期|
| - -----|- -----|- -----|- -----|- -----|
| 1| 1| 1| 2000年10月18日|2017-10-18 2017-10-18|
| 2| 1| 2| 2015-11-18 2015-11-18 2015-11-18|联系我们|
| 3| 2| 3| 2015-10-28 2015-10-28| 2025-10-18 2025-10-18 2025-10-18|
fiddle

您可以创建一个存储过程来为您执行 checkout 逻辑。它锁定表，以便检查书是否已被检出以及随后的插入将被视为“临界区”，一次只允许运行该过程的单个调用。除非检出率非常高，否则这不会对性能产生不利影响，并确保两个人不能检出同一本书。

DROP PROCEDURE IF EXISTS checkout;

DELIMITER $$
CREATE PROCEDURE checkout(IN checkout_user_id, IN checkout_book_id INT, OUT return_code INT)
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
    DECLARE row_count INT;

    START TRANSACTION;

    /* Was the book already checked out? */
    SELECT COUNT(*) INTO row_count FROM checkouts
    WHERE book_id = checkout_book_id AND return_date IS NULL
    FOR UPDATE; /* This essentially locks the table */
    
    IF row_count = 0 THEN
        INSERT INTO checkouts(user_id, book_id, checkout_date, return_date)
        VALUES(checkout_user_id, checkout_book_id, CURRENT_DATE(), NULL);
        COMMIT;
        SET return_code = 0;
    ELSE
        /* Book was already checked out. */
        ROLLBACK;
        set return_code = 1;
    END IF;
    
END$$
DELIMITER ;

创建存储过程后，您可以执行以下操作：

SET @return_code = 0;
/* user_id 21 wants to checkout book_id 37: */
CALL checkout(21, 37, @return_code);
/* 0 for success otherwise failure: */
SELECT @return_code;