当前位置：首页 > PHP

php 库存实现

2026-02-14 08:59:44PHP

库存管理的基本实现

库存管理在PHP中通常通过数据库操作实现，核心是跟踪商品的进出记录。需要创建商品表、库存变动记录表和库存快照表。

商品表结构示例：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE,
    description TEXT
);

库存变动记录表：

CREATE TABLE inventory_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    type ENUM('in','out') NOT NULL,
    reference_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

实时库存计算

通过聚合查询计算当前库存：

function getCurrentStock($productId) {
    $query = "SELECT SUM(CASE WHEN type='in' THEN quantity ELSE -quantity END) 
              AS stock FROM inventory_transactions WHERE product_id = ?";
    $stmt = $pdo->prepare($query);
    $stmt->execute([$productId]);
    return $stmt->fetchColumn();
}

库存快照优化

对于高频访问的系统，可以定期生成库存快照：

CREATE TABLE inventory_snapshots (
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL,
    updated_at TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

更新快照的存储过程：

function updateSnapshot($productId) {
    $currentStock = getCurrentStock($productId);
    $query = "INSERT INTO inventory_snapshots 
              (product_id, quantity, updated_at) 
              VALUES (?, ?, NOW()) 
              ON DUPLICATE KEY UPDATE 
              quantity = VALUES(quantity), 
              updated_at = VALUES(updated_at)";
    $pdo->prepare($query)->execute([$productId, $currentStock]);
}

库存预警机制

设置库存预警阈值并检查：

function checkLowStock($threshold) {
    $query = "SELECT p.id, p.name, s.quantity 
              FROM products p 
              JOIN inventory_snapshots s ON p.id = s.product_id 
              WHERE s.quantity <= ?";
    $stmt = $pdo->prepare($query);
    $stmt->execute([$threshold]);
    return $stmt->fetchAll();
}

事务处理保证数据一致性

库存操作应使用数据库事务：

function updateInventory($productId, $quantity, $type) {
    try {
        $pdo->beginTransaction();

        // 记录交易
        $stmt = $pdo->prepare("INSERT INTO inventory_transactions 
                              (product_id, quantity, type) 
                              VALUES (?, ?, ?)");
        $stmt->execute([$productId, $quantity, $type]);

        // 更新快照
        updateSnapshot($productId);

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        return false;
    }
}

库存变动历史查询

获取特定商品的库存变动历史：

php 库存实现

function getInventoryHistory($productId, $limit = 100) {
    $query = "SELECT * FROM inventory_transactions 
              WHERE product_id = ? 
              ORDER BY created_at DESC 
              LIMIT ?";
    $stmt = $pdo->prepare($query);
    $stmt->execute([$productId, $limit]);
    return $stmt->fetchAll();
}