如何建立MySQL中买菜系统的库存变动记录表

买菜系统的库存变动记录表主要用于追踪每一笔商品的库存变化，无论是用户下单减库存、采购入库加库存，还是损耗报损减库存，都需要在这张表中留下完整的操作记录，为后续的库存管理和问题排查提供数据支撑。

库存变动记录表的核心设计思路

设计这张表时，需要覆盖变动的核心要素：哪件商品、什么时候变动、变动数量是多少、变动类型是什么、谁操作的、关联的业务单号是什么。这些要素可以保障每一条记录的信息完整，避免后续统计时出现歧义。

核心字段说明

• id：记录的唯一标识，采用自增主键，方便快速定位单条记录
• product_id：关联商品表的商品ID，明确变动的是哪款商品
• change_amount：变动数量，正数代表库存增加，负数代表库存减少
• change_type：变动类型，区分不同的库存操作场景
• related_order_no：关联的业务单号，比如订单号、采购单号，方便溯源
• operator_id：操作人ID，记录是系统自动操作还是人工操作
• change_time：变动发生的时间，精确到秒
• remark：备注信息，记录变动的额外说明，比如报损原因

完整的建表SQL语句

以下是适配买菜系统场景的库存变动记录表建表语句，已经考虑了常用查询场景的索引优化：

-- 创建买菜系统库存变动记录表
CREATE TABLE `inventory_change_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `product_id` bigint(20) unsigned NOT NULL COMMENT '商品ID，关联商品表',
  `change_amount` int(11) NOT NULL COMMENT '变动数量，正数为加库存，负数为减库存',
  `change_type` tinyint(4) NOT NULL COMMENT '变动类型：1-用户下单减库存，2-采购入库加库存，3-报损减库存，4-库存校正',
  `related_order_no` varchar(64) DEFAULT NULL COMMENT '关联业务单号，如订单号、采购单号',
  `operator_id` bigint(20) unsigned DEFAULT NULL COMMENT '操作人ID，系统操作为0',
  `change_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '变动时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_change_time` (`change_time`),
  KEY `idx_related_order_no` (`related_order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='买菜系统库存变动记录表';

字段设计注意事项

在实际使用中，有几个细节需要特别注意，避免出现数据问题：

变动数量的符号规范

统一规定change_amount字段正数为库存增加，负数为库存减少，不要混合使用不同的符号规则，否则后续统计库存总变动时会出现错误。比如用户下单购买2斤白菜，就插入-2的记录，采购入库10斤白菜就插入10的记录。

变动类型的枚举规范

change_type字段建议使用tinyint类型存储枚举值，不要直接存中文描述，既节省存储空间，也方便后续扩展。可以在代码层维护类型映射关系，比如1对应下单减库存，2对应采购入库等。

关联单号的可空性

部分库存变动可能没有关联的业务单号，比如人工校正库存的操作，因此related_order_no字段设置为可空，避免插入数据时出现约束错误。

常见使用场景示例

插入库存变动记录

当用户下单购买商品时，需要同时插入库存变动记录，以下是示例SQL：

-- 用户下单购买商品ID为1001的白菜3斤，关联订单号202405201234
INSERT INTO `inventory_change_record` (
  `product_id`,
  `change_amount`,
  `change_type`,
  `related_order_no`,
  `operator_id`,
  `remark`
) VALUES (
  1001,
  -3,
  1,
  '202405201234',
  0,
  '用户下单减库存'
);

查询某商品的库存变动历史

如果需要查询商品ID为1001的所有库存变动记录，按照时间倒序排列，可以使用以下SQL：

-- 查询商品1001的库存变动历史，最新记录在前
SELECT 
  `id`,
  `change_amount`,
  `change_type`,
  `related_order_no`,
  `change_time`,
  `remark`
FROM `inventory_change_record`
WHERE `product_id` = 1001
ORDER BY `change_time` DESC;

统计某段时间的库存总变动

如果需要统计2024年5月1日到5月20日，商品1001的库存总变动量，可以使用以下SQL：

-- 统计商品1001在指定时间段的库存总变动
SELECT 
  SUM(`change_amount`) AS total_change
FROM `inventory_change_record`
WHERE `product_id` = 1001
  AND `change_time` >= '2024-05-01 00:00:00'
  AND `change_time` <= '2024-05-20 23:59:59';

索引优化建议

除了建表时默认的索引，还可以根据业务查询场景额外添加联合索引：

• 如果经常需要查询某个商品在某个时间段的变动记录，可以添加idx_product_time联合索引，包含product_id和change_time两个字段，提升查询效率
• 如果经常需要按照变动类型查询记录，可以添加idx_change_type索引，加快类型筛选的速度

合理的索引设计可以大幅提升库存变动记录表的查询性能，尤其是在数据量达到百万级以上时，效果会更加明显。

MySQL买菜系统库存变动记录表数据库设计修改时间：2026-07-05 12:27:29