如何在mysql中设计产品库存管理功能

库存表结构设计

产品库存管理的核心是数据库表的设计。需要一个主库存表来记录每个产品的当前库存数量、预警值和状态信息。

示例表结构：

CREATE TABLE product_inventory (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL UNIQUE,
    stock_quantity INT DEFAULT 0,
    reserved_quantity INT DEFAULT 0,
    available_quantity INT AS (stock_quantity - reserved_quantity) STORED,
    min_stock_level INT DEFAULT 10,
    max_stock_level INT DEFAULT 100,
    status ENUM('in_stock', 'out_of_stock', 'low_stock') DEFAULT 'in_stock',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

说明：

库存变动日志记录

为保证数据可追溯，必须建立库存流水表记录每一次变更。

创建库存日志表：

CREATE TABLE inventory_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    inventory_id BIGINT NOT NULL,
    change_type ENUM('in', 'out', 'adjust', 'sale', 'return') NOT NULL,
    quantity_change INT NOT NULL,
    reference_type VARCHAR(50),
    reference_id BIGINT,
    operator_id INT,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES product_inventory(id)
);

每次出入库操作都插入一条日志，便于后期对账和排查问题。

关键业务逻辑实现

库存操作需使用事务确保一致性，特别是在高并发场景下防止超卖。

例如扣减库存的SQL示例：

START TRANSACTION;
<p>UPDATE product_inventory 
SET 
reserved_quantity = reserved_quantity + 1,
updated_at = NOW()
WHERE product_id = ? 
AND (stock_quantity - reserved_quantity) >= 1;</p><p>-- 检查是否影响一行
IF ROW_COUNT() = 0 THEN
ROLLBACK;
-- 返回库存不足错误
ELSE
INSERT INTO inventory_log (inventory_id, change_type, quantity_change, reference_type, reference_id)
VALUES (?, 'sale', 1, 'order', ?);
COMMIT;
END IF;</p>

出库时先预占库存（写入reserved），订单完成后再从预占转为实际消耗。

库存预警与维护

可通过定时任务检查低库存商品：

SELECT product_id, stock_quantity, min_stock_level 
FROM product_inventory 
WHERE stock_quantity <= min_stock_level;

结合应用层发送邮件或系统通知提醒采购。也可设置事件自动触发补货工单。

定期核对物理库存与系统数据，通过调整接口修正差异：

stock_quantity

adjust

基本上就这些。核心是结构清晰、操作可追溯、并发安全。