如何在item表数量为0时阻止向issue表插入数据？

嘿，我来给你几个靠谱的解决方案，从数据库层面到应用层都覆盖了，你可以根据自己的技术栈和实际场景来选：

直接在数据库层面添加触发器，在插入issue记录前自动检查对应item的库存，一旦库存为0就阻止插入。这种方案的好处是不管哪个应用端操作数据库，都会被拦截，不会出现漏网之鱼。

以MySQL为例，触发器代码如下：

DELIMITER //
CREATE TRIGGER check_item_stock_before_insert_issue
BEFORE INSERT ON issue
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    -- 查询对应物品的当前库存
    SELECT quantity INTO current_stock FROM item WHERE id = NEW.item_id;
    -- 如果库存<=0，抛出自定义错误阻止插入
    IF current_stock <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '物品库存为0，无法发放';
    END IF;
END //
DELIMITER ;

触发器会在每次向issue表插入记录前执行：先获取对应item的库存，若库存不足则抛出错误，中断插入操作。

如果不想在数据库端加触发器，可以在应用代码里用事务包裹库存检查、插入发放记录、扣减库存这三个操作，确保原子性，同时防止并发场景下的超卖问题。

这里给个Java JDBC的示例（其他语言思路类似）：

try (Connection conn = getDatabaseConnection()) {
    conn.setAutoCommit(false); // 开启事务

    // 1. 查询库存并加行锁，防止并发修改
    String checkStockSql = "SELECT quantity FROM item WHERE id = ? FOR UPDATE";
    try (PreparedStatement checkStmt = conn.prepareStatement(checkStockSql)) {
        checkStmt.setInt(1, targetItemId);
        ResultSet rs = checkStmt.executeQuery();
        if (rs.next()) {
            int stock = rs.getInt("quantity");
            if (stock <= 0) {
                throw new RuntimeException("该物品库存已耗尽，无法发放");
            }

            // 2. 插入发放记录到issue表
            String insertIssueSql = "INSERT INTO issue (item_id, employee_id, issue_time) VALUES (?, ?, NOW())";
            try (PreparedStatement insertStmt = conn.prepareStatement(insertIssueSql)) {
                insertStmt.setInt(1, targetItemId);
                insertStmt.setInt(2, employeeId);
                insertStmt.executeUpdate();
            }

            // 3. 扣减item表的库存
            String updateStockSql = "UPDATE item SET quantity = quantity - 1 WHERE id = ?";
            try (PreparedStatement updateStmt = conn.prepareStatement(updateStockSql)) {
                updateStmt.setInt(1, targetItemId);
                updateStmt.executeUpdate();
            }
        }
        conn.commit(); // 事务提交
    } catch (Exception e) {
        conn.rollback(); // 出错回滚
        throw e;
    }
}

这里的FOR UPDATE很关键，它会给查询到的item行加锁，避免多个请求同时查询到库存为1，然后都插入记录导致库存变成-1的情况。

把库存检查、插入发放记录、扣减库存的逻辑全部封装到存储过程里，应用端只需要调用存储过程即可。这种方案适合需要统一管理业务逻辑的场景。

还是以MySQL为例，存储过程代码：

DELIMITER //
CREATE PROCEDURE issue_item_to_employee(IN p_item_id INT, IN p_employee_id INT)
BEGIN
    DECLARE current_stock INT;
    START TRANSACTION;

    -- 1. 查询库存并加锁
    SELECT quantity INTO current_stock FROM item WHERE id = p_item_id FOR UPDATE;
    IF current_stock <= 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '物品库存为0，无法发放';
    END IF;

    -- 2. 插入发放记录
    INSERT INTO issue (item_id, employee_id, issue_time) VALUES (p_item_id, p_employee_id, NOW());

    -- 3. 扣减库存
    UPDATE item SET quantity = quantity - 1 WHERE id = p_item_id;

    COMMIT;
END //
DELIMITER ;

应用端调用时只需要执行：

CALL issue_item_to_employee(1, 1001); -- 传入物品ID和员工ID

方案对比与注意事项 #

• 触发器：优点是全局生效，无需修改应用代码；缺点是逻辑在数据库端，排查问题需要查看触发器代码，不同数据库语法有差异。
• 应用层事务：优点是逻辑在应用端，调试方便；缺点是如果有多个应用操作数据库，需要每个应用都实现相同逻辑，容易遗漏。
• 存储过程：优点是业务逻辑集中管理，应用端调用简单；缺点是存储过程的维护和调试相对复杂，跨数据库兼容性差。

不管选哪种方案，都要确保操作的原子性（用事务），同时处理并发场景下的库存竞争问题（比如加行锁），避免出现库存负数或者超发的情况。

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