一个库数据库，目的是防止多个用户尝试同时借出同一本书 Mysql Workbench 8.0

您可以使用触发器来防止这种情况发生

对于触发器，您最终需要添加 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

小提琴

您可以创建一个存储过程来执行签出逻辑。它锁定了表，以便检查书籍是否已签出，然后进行后续插入将被视为“关键部分”，一次只允许运行一次调用该过程。除非结账率非常高，否则这不会对性能产生不利影响，并确保两个人无法借出同一本书。

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;