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

库存管理核心是设计主库存表和日志表。1. 主库存表（product_inventory）记录产品库存数量、预警值及状态，包含实际库存、预占库存、可售库存（自动计算）、最低最高库存阈值和状态；2. 库存日志表（inventory_log）记录每次库存变更类型、数量、操作来源等，确保可追溯；3. 扣减库存时使用事务，先检查可售库存是否充足，再更新预占库存并写入日志，防止超卖；4. 通过定时任务查询stock_quantity小于min_stock_level的商品触发补货提醒。

库存表结构设计

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

示例表结构：

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)
);

说明：

• stock_quantity：实际入库总量
• reserved_quantity：已下单但未发货占用的库存
• available_quantity：可售库存，通过计算得出
• min_stock_level：低于该值触发补货提醒

库存变动日志记录

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

创建库存日志表：

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;
UPDATE product_inventory 
SET 
reserved_quantity = reserved_quantity + 1,
updated_at = NOW()
WHERE product_id = ? 
AND (stock_quantity - reserved_quantity) >= 1;
-- 检查是否影响一行
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;

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

库存预警与维护

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

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

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

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

• 盘点后更新stock_quantity
• 插入adjust类型的日志说明原因
• 避免直接修改而不留痕迹

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