PHP项目出库单如何扣减对应商品库存数量

wen PHP项目 2026-06-24 297

本文目录导读：

核心逻辑流程
扣减库存方法实现
完整示例代码
数据库表结构建议
最佳实践建议

在PHP项目中实现出库单扣减库存,建议采用事务处理 + 锁机制（乐观锁/悲观锁） 来保证数据一致性，以下是完整的实现逻辑和代码示例：

核心逻辑流程

public function processOutboundOrder($orderData) {
    // 开启数据库事务
    $this->db->beginTransaction();
    try {
        // 1. 校验出库单商品数据
        $this->validateOutboundItems($orderData['items']);
        // 2. 锁定库存（防止并发）
        $this->lockInventory($orderData['items']);
        // 3. 逐项扣减库存
        foreach ($orderData['items'] as $item) {
            $this->deductStock($item['product_id'], $item['quantity']);
        }
        // 4. 记录出库单
        $this->saveOutboundOrder($orderData);
        // 提交事务
        $this->db->commit();
    } catch (Exception $e) {
        $this->db->rollback();
        throw $e;
    }
}

扣减库存方法实现

方案1：悲观锁（推荐高并发场景）

// 使用 SELECT ... FOR UPDATE 锁定行
public function deductStock($productId, $quantity) {
    // 锁定并查询库存
    $sql = "SELECT id, stock_quantity, version 
            FROM products 
            WHERE id = ? 
            FOR UPDATE";
    $product = $this->db->fetchOne($sql, [$productId]);
    // 检查库存是否充足
    if ($product['stock_quantity'] < $quantity) {
        throw new Exception("商品{$productId}库存不足");
    }
    // 执行扣减
    $updateSql = "UPDATE products 
                  SET stock_quantity = stock_quantity - ?,
                      updated_at = NOW()
                  WHERE id = ? 
                  AND stock_quantity >= ?";  // 二次校验
    $affected = $this->db->execute($updateSql, [$quantity, $productId, $quantity]);
    if ($affected === 0) {
        throw new Exception("库存扣减失败，可能已被其他请求更新");
    }
}

方案2：乐观锁（适用于冲突较少的场景）

public function deductStock($productId, $quantity, $maxRetries = 3) {
    $retries = 0;
    while ($retries < $maxRetries) {
        // 查询当前库存和版本号
        $product = $this->db->fetchOne(
            "SELECT stock_quantity, version FROM products WHERE id = ?",
            [$productId]
        );
        if ($product['stock_quantity'] < $quantity) {
            throw new Exception("库存不足");
        }
        $newVersion = $product['version'] + 1;
        // 带版本号的更新
        $sql = "UPDATE products 
                SET stock_quantity = stock_quantity - ?,
                    version = ?,
                    updated_at = NOW()
                WHERE id = ? 
                AND version = ?
                AND stock_quantity >= ?";
        $affected = $this->db->execute($sql, [
            $quantity, 
            $newVersion, 
            $productId, 
            $product['version'],
            $quantity
        ]);
        if ($affected > 0) {
            return true; // 成功
        }
        // 更新失败，重试
        $retries++;
        usleep(100000); // 100ms 延迟
    }
    throw new Exception("库存扣减失败，请重试");
}

完整示例代码

<?php
class InventoryService {
    private $db;
    public function processOutboundOrder($orderId, $items) {
        try {
            $this->db->beginTransaction();
            // 1. 创建出库单记录
            $outboundId = $this->createOutbound($orderId);
            // 2. 处理每个商品
            foreach ($items as $item) {
                // 锁定商品行
                $product = $this->db->fetchOne(
                    "SELECT * FROM products WHERE id = ? FOR UPDATE",
                    [$item['product_id']]
                );
                if (!$product) {
                    throw new Exception("商品不存在: " . $item['product_id']);
                }
                // 校验库存
                $requestedQty = $item['quantity'];
                $availableQty = $product['stock_quantity'];
                if ($availableQty < $requestedQty) {
                    throw new Exception(
                        "商品 {$product['name']} 库存不足：需求{$requestedQty}，剩余{$availableQty}"
                    );
                }
                // 扣减库存
                $this->db->execute(
                    "UPDATE products 
                     SET stock_quantity = stock_quantity - ?,
                         updated_at = NOW()
                     WHERE id = ? 
                     AND stock_quantity >= ?",
                    [$requestedQty, $item['product_id'], $requestedQty]
                );
                // 记录出库明细
                $this->createOutboundDetail($outboundId, $item);
            }
            $this->db->commit();
            return $outboundId;
        } catch (Exception $e) {
            $this->db->rollback();
            // 日志记录错误
            error_log("出库失败: " . $e->getMessage());
            throw $e;
        }
    }
    private function createOutbound($orderId) {
        $sql = "INSERT INTO outbound_orders (order_id, status, created_at) 
                VALUES (?, 'pending', NOW())";
        $this->db->execute($sql, [$orderId]);
        return $this->db->lastInsertId();
    }
    private function createOutboundDetail($outboundId, $item) {
        $sql = "INSERT INTO outbound_details 
                (outbound_id, product_id, quantity, unit_price) 
                VALUES (?, ?, ?, ?)";
        $this->db->execute($sql, [
            $outboundId, 
            $item['product_id'], 
            $item['quantity'], 
            $item['unit_price'] ?? 0
        ]);
    }
}

数据库表结构建议

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,  -- 乐观锁版本号
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 出库单主表
CREATE TABLE outbound_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id VARCHAR(50) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 出库明细表
CREATE TABLE outbound_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    outbound_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) DEFAULT 0.00,
    FOREIGN KEY (outbound_id) REFERENCES outbound_orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

最佳实践建议

数据库引擎：使用 InnoDB（支持事务和行级锁）
库存充足校验：在 UPDATE 语句中加入 AND stock_quantity >= ? 二次校验
并发控制：高并发时优先使用悲观锁（FOR UPDATE）
性能优化：对 product_id 建立索引
监控报警：库存低于阈值时触发告警
日志记录：每次操作记录详细的日志

// 库存充足监控示例
public function checkStockThreshold($productId, $threshold = 10) {
    $sql = "SELECT stock_quantity FROM products WHERE id = ?";
    $stock = $this->db->fetchOne($sql, [$productId]);
    if ($stock['stock_quantity'] <= $threshold) {
        // 发送告警通知
        $this->notifyLowStock($productId, $stock['stock_quantity']);
    }
}

通过以上方案,可以有效地保证在并发场景下库存数据的准确性和一致性，建议根据实际业务量选择合适的锁机制，并做好异常处理和日志记录。

本文地址： https://www.bmjup.com/post/7300.html