mysql如何实现商品库存管理系统_mysql数据表与操作流程

库存扣减必须用update…where stock>=?校验并检查row_count()，配合version乐观锁和事务内完成订单生成与扣减，同时记录stock_log日志。

库存扣减必须用 UPDATE ... WHERE stock >= ? 加条件判断

直接 UPDATE goods SET stock = stock - 1 WHERE id = 123 是危险的——并发下单时可能超卖。正确做法是在 sql 层就拦截非法扣减：

UPDATE goods SET stock = stock - 1 WHERE id = 123 AND stock >= 1;

执行后检查 ROW_COUNT() 返回值：为 0 表示库存不足或商品不存在，不是靠应用层查一遍再扣，那会引入竞态。

goods 表至少要包含 version 字段做乐观锁

仅靠 WHERE stock >= 1 还不够，比如两次请求同时读到 stock=5，都通过校验，最终变成 3 而非 4（应只允许一次成功）。加 version 字段可解决：

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

应用需先查出当前 version，更新时带上，失败则重试。不依赖数据库行锁，更适合高并发读多写少场景。

订单生成和库存扣减必须在同一个事务里完成

常见错误是先插入订单，再异步扣库存——一旦扣减失败，订单已落库，状态不一致。正确流程是：

• 开启事务
• 执行带 AND stock >= ? 和 version 校验的 UPDATE
• 检查影响行数是否为 1
• 插入订单记录（含商品 ID、数量、快照库存值）
• 提交事务

任何一步失败都回滚，保证原子性。注意：mysql 必须用 InnoDB 引擎，autocommit=0 或显式 BEGIN。

历史库存变动要用独立表记录，别只靠 goods.stock 反推

goods 表的 stock 是当前值，无法追溯“谁、何时、因何扣了 2 件”。必须建 stock_log 表：

CREATE TABLE stock_log (   id BIGINT PRIMARY KEY AUTO_INCREMENT,   goods_id INT NOT NULL,   change_amount INT NOT NULL,   order_id BIGINT,   remark VARCHAR(100),   created_at DATETIME DEFAULT CURRENT_TIMESTAMP );

每次扣减/补货都写一条日志。排查超卖、对账、运营分析全靠它——别指望从订单表反向计算，退货、取消、补偿等逻辑会让反推变得不可靠。