mysql实现简单库存管理系统数据库架构

来源：这里教程网时间：2026-02-28 20:39:05 作者：

库存表必须带版本号或时间戳字段

直接用

quantity

字段做减法更新（如

UPDATE goods SET quantity = quantity - 1 WHERE id = 123

）在并发场景下大概率超卖。MySQL 的行锁只锁住匹配的行，但不阻止多个事务同时读到旧值再各自扣减。

推荐方案是加乐观锁：在

goods

表中增加

version

或

updated_at

字段，更新时带上条件：

UPDATE goods 
SET quantity = quantity - 1, version = version + 1 
WHERE id = 123 AND quantity >= 1 AND version = 5;

执行后检查

ROW_COUNT()

是否为 1 —— 不是则说明已被其他事务抢先修改，需重试或报错。

version
方案更精确，适合高一致性要求场景
updated_at
方案可兼作审计，但要注意 MySQL 的
NOW()
在事务内不刷新，得用
SYSDATE()
或应用层传入时间别用
SELECT ... FOR UPDATE
简单包住读写 —— 容易引发死锁，且锁粒度大、吞吐低

订单与库存变动必须落库再操作

下单不是“查库存 → 扣减 → 创建订单”三步链路，而是先插入一条带状态的订单记录，再用该记录驱动库存变更。否则网络中断或服务崩溃会导致状态不一致。

典型表结构组合：

orders
表含
status ENUM('created', 'paid', 'cancelled')
和
created_at

order_items
表存商品 ID、数量、快照单价（避免后续价格变动影响）
inventory_logs
表记录每次变动：谁（
order_id
）、什么动作（
type IN ('reserve', 'confirm', 'cancel')
）、影响数量、时间

例如用户下单时：

INSERT INTO orders (user_id, status) VALUES (456, 'created');
INSERT INTO order_items (order_id, goods_id, quantity, price) VALUES (LAST_INSERT_ID(), 123, 2, 99.00);
INSERT INTO inventory_logs (order_id, goods_id, type, delta, created_at) 
VALUES (LAST_INSERT_ID(), 123, 'reserve', -2, SYSDATE());

后续支付成功再补一条

type = 'confirm'

日志，并真正扣减

goods.quantity

；超时未支付则走

cancel

流程回滚预留量。

查询实时可用库存要合并多张表

用户看到的“剩余库存”不是

goods.quantity

单一字段，而是：

goods.quantity - 已确认占用（confirm） + 已取消释放（cancel） - 当前待确认预留（reserve）

推荐用视图封装逻辑，避免每个业务点重复写 JOIN：

CREATE VIEW goods_available AS
SELECT 
  g.id,
  g.name,
  g.quantity - COALESCE(r.reserved, 0) AS available
FROM goods g
LEFT JOIN (
  SELECT goods_id, SUM(delta) AS reserved 
  FROM inventory_logs 
  WHERE type = 'reserve' AND order_id NOT IN (
    SELECT id FROM orders WHERE status IN ('cancelled', 'paid')
  )
  GROUP BY goods_id
) r ON g.id = r.goods_id;

注意：

IN

子查询在大数据量时性能差，生产环境建议改用

LEFT JOIN orders

WHERE o.status IS NULL OR o.status = 'created'

。

不要在应用层拼接 SQL 去算可用库存 —— 容易漏掉某类日志类型 缓存该视图结果需设短 TTL（如 1–5 秒），不能依赖长期 Redis 缓存 后台管理页可查原始
inventory_logs
追溯每一笔变动

初始化和迁移要预置约束与索引

没加唯一约束和索引的库存系统，上线后查慢、改错、删不掉数据是常态。

给
goods.id
加主键，
goods.sku
加唯一索引（防止重复上架）
inventory_logs
上建复合索引：
(goods_id, type, order_id)
，覆盖常用查询路径所有金额类字段用
DECIMAL(10,2)
，别用
FLOAT
—— 否则 0.1 + 0.2 ≠ 0.3 外键不是必须，但
order_items.goods_id
和
inventory_logs.goods_id
应有索引，否则 JOIN 慢成瓶颈

建表语句里漏掉