如何实现SQL批量库存更新：仅当所有指定批次库存充足时执行

需求说明：

• 执行库存扣除操作前，需检查所有指定lot_number的库存
• 仅当所有指定批次库存均≥要扣除的数值时，才统一执行扣除操作
• 若有任意一个批次库存不足，不修改任何批次的库存

现有问题：当前代码会更新部分库存充足的批次，无法实现"全满足才更新"的原子性要求

现有代码 #

$ids = Array(0 => 998, 1 => 989, 4 => 899);
$count = "20";

// Attempt update query execution
$sql = 'UPDATE tbl_numbers_stock SET lot_stock= lot_stock - '.$count.' WHERE lot_stock >= '.$count.'  AND lot_number IN (' . implode( ',', $ids ) . ' );';
if(mysqli_query($link, $sql)){
    $affectedrows = mysqli_affected_rows($link);
    if ($affectedrows == "0") { 
        echo "nothing updated";
    } else { 
        echo "all updated";
    }
}

表结构示例 #

CREATE TABLE IF NOT EXISTS `tbl_stock` (
  `id` int(6) unsigned NOT NULL,
  `lot_number` varchar(200) NOT NULL,
  `stock` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `tbl_stock` (`id`, `lot_number`, `stock`) VALUES
  ('1', '100', '1'),
  ('2', '998', '100'),
  ('3', '899', '10'),
  ('4', '999', '100'),
  ('5', '888', '100'),
  ('6', '833', '100'),
  ('7', '989', '100'),
  ('8', '101', '100'),
  ('9', '777', '100'),
  ('10', '104', '100');

解决方案 #

核心思路：先校验所有指定批次的库存是否全部达标，再执行更新操作，同时使用预处理语句避免SQL注入风险。

修改后的代码 #

$ids = [998, 989, 899];
$count = 20;
$totalSpecified = count($ids);

// 1. 校验所有指定批次库存是否都≥$count
$placeholders = implode(',', array_fill(0, $totalSpecified, '?'));
$checkSql = "SELECT COUNT(*) as invalid_count FROM tbl_stock 
             WHERE lot_number IN ($placeholders) AND stock < ?";

$stmt = mysqli_prepare($link, $checkSql);
// 绑定参数：先绑定所有lot_number，再绑定count
$types = str_repeat('s', $totalSpecified) . 'i'; // 假设lot_number是字符串，count是整数
$params = array_merge($ids, [$count]);
mysqli_stmt_bind_param($stmt, $types, ...$params);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);

if ($row['invalid_count'] === 0) {
    // 2. 所有批次库存达标，执行批量更新
    $updateSql = "UPDATE tbl_stock SET stock = stock - ? 
                  WHERE lot_number IN ($placeholders)";
    $updateStmt = mysqli_prepare($link, $updateSql);
    $updateTypes = 'i' . str_repeat('s', $totalSpecified);
    $updateParams = array_merge([$count], $ids);
    mysqli_stmt_bind_param($updateStmt, $updateTypes, ...$updateParams);
    mysqli_stmt_execute($updateStmt);
    
    if (mysqli_stmt_affected_rows($updateStmt) === $totalSpecified) {
        echo "all updated";
    } else {
        echo "update failed";
    }
} else {
    // 存在库存不足的批次，不执行更新
    echo "nothing updated";
}

// 关闭语句和连接
mysqli_stmt_close($stmt);
if (isset($updateStmt)) mysqli_stmt_close($updateStmt);

关键说明 #

1. 原子性校验：通过统计stock < $count的指定批次数量，若为0则说明全部达标
2. SQL注入防护：使用预处理语句绑定参数，避免直接拼接变量带来的安全风险
3. 结果验证：更新后检查受影响行数是否等于指定批次数量，确保全部更新成功

测试场景验证 #

• 当$count=20时：899批次库存为10<20，invalid_count=1，输出nothing updated，无数据修改
• 当$count=10时：所有批次库存≥10，invalid_count=0，执行更新后3个批次库存均扣除10，输出all updated

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