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

库存系统需用乐观锁（version/updated_at）防超卖，订单与库存操作须先落库再驱动变更，实时可用库存应通过视图聚合goods.quantity与inventory_logs各类型delta，辅以合理索引、DECIMAL精度及幂等日志校验。

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

直接用 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 慢成瓶颈

建表语句里漏掉 ON DELETE RESTRICT 或 CASCADE 意味着后期清理脏数据只能手动 DELETE，风险极高。

实际最难的不是建几张表，而是把「预留→确认→取消」这个状态机跑稳。很多团队卡在库存对不上账，根源不在 SQL 写得对不对，而在没把每一步操作映射到明确的日志记录和幂等校验。