小型自选商场商品管理系统：数据库表设计与库存事务实战

简介这是一套面向计算机专业学生与初学数据库开发者的商品管理系统完整源码围绕小型自选商场的进货、销售、库存三大业务环节展开可用于课程设计、毕业设计或数据库综合实训。系统要求记录每笔进货与售货并支持按月统计、日盘存与月盘存在出入库时动态刷新库存同时提供供应商信息查询与收银台程序能根据商品编号和数量生成购物清单并显示收付款情况。数据库设计涵盖库存、售货、进货、供应商四张核心表字段包含商品ID、定价、折扣率、最低存量、存根号等结构贴近真实零售场景。资源包共189个文件约680KB以60个sql脚本、54个cs源文件为主辅以resx与resources资源文件、config配置、exe可执行程序及mdf、ldf数据库文件覆盖建库建表、界面逻辑与运行部署各环节。目前已有1025人学习适合需要完整实现方案与表结构参考的读者。1. 从一张 Excel 表到一套进销存小型自选商场商品管理系统能解决什么如果你在社区超市、校园便利店或者乡镇自选商场待过大概率见过这样的场景老板用一张 Excel 表记商品进货时改一列卖货时改一列月底盘点发现库存对不上某个牌子的方便面明明进了 50 箱系统里显示还剩 12 箱实际货架上只有 8 箱。问题出在哪没人说得清。小型自选商场商品管理系统就是冲着这个痛点来的——它把商品档案、进货入库、销售出库、库存预警和简单的统计报表串成一条线用数据库做底座让每一笔进出都有记录可查。这套系统适合谁一是刚学完数据库课程、想找一个完整项目练手的学生二是小型商场的经营者或店长想用一套轻量工具替代手工记账三是接私活做小型零售管理软件的开发者需要一个能快速改、快速部署的底子。它不追求大而全核心目标就一个让商品数量对得上、让进出流水查得到。2. 数据库表结构怎么设计从商品、库存到流水的三张核心表2.1 为什么先定表结构再写代码很多新手拿到这个项目第一反应是打开 IDE 开始写界面结果写到一半发现库存表里没有记录进货单价销售统计做不了或者商品表和分类表混在一起改一个分类名要更新几十条记录。血泪经验是小型自选商场商品管理系统的复杂度不在界面而在数据关系。商品、分类、供应商、库存、销售流水这几块如果表结构没定好后面每加一个功能都要动数据库改到最后自己都不敢碰。常见做法是先把实体关系理清楚。一个商品属于一个分类一个分类下有多个商品一个供应商可以供应多个商品一个商品也可以有多个供应商但为了简化小型系统通常只保留一个默认供应商字段。库存不是单独一张表就完事而是商品表里存当前库存量另外用一张入库表和一张销售表记录每一次变动。这样查当前库存快查历史流水也快。2.2 三张核心表的字段设计与建表语句下面是我一般会用的建表方案以 MySQL 为例字符集用 utf8mb4引擎 InnoDB。商品表存基础信息和当前库存入库表记录每一次进货销售表记录每一次卖出。-- 商品表存商品基础信息和当前库存量 CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID, barcode VARCHAR(32) UNIQUE COMMENT 条形码唯一, name VARCHAR(100) NOT NULL COMMENT 商品名称, category_id INT NOT NULL COMMENT 分类ID, supplier_id INT DEFAULT NULL COMMENT 默认供应商ID, purchase_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 进货价, sale_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 销售价, stock INT NOT NULL DEFAULT 0 COMMENT 当前库存数量, stock_warn INT NOT NULL DEFAULT 10 COMMENT 库存预警阈值, unit VARCHAR(10) DEFAULT 件 COMMENT 计量单位, status TINYINT DEFAULT 1 COMMENT 1上架 0下架, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表; -- 入库表每一次进货都插一条 CREATE TABLE stock_in ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL COMMENT 商品ID, quantity INT NOT NULL COMMENT 入库数量, purchase_price DECIMAL(10,2) NOT NULL COMMENT 本次进货单价, supplier_id INT DEFAULT NULL, operator VARCHAR(50) COMMENT 操作人, remark VARCHAR(255) COMMENT 备注, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_product (product_id), INDEX idx_created (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT入库记录表; -- 销售表每一次卖出都插一条 CREATE TABLE sale ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, quantity INT NOT NULL COMMENT 销售数量, sale_price DECIMAL(10,2) NOT NULL COMMENT 实际售价, total_amount DECIMAL(10,2) NOT NULL COMMENT 本笔总额, operator VARCHAR(50), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_product (product_id), INDEX idx_created (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT销售记录表;这三张表的逻辑说明product 表里的 stock 字段是冗余设计目的是避免每次查库存都去 sum 入库减销售。入库和销售时用事务同时更新 product.stock 并插入流水记录。参数上purchase_price 和 sale_price 用 DECIMAL(10,2) 而不是 FLOAT是因为金额计算不能有浮点误差这是很多新手翻车的地方。stock_warn 给一个默认值 10后面做库存预警时直接拿 stock 和它比较。barcode 加了 UNIQUE 约束防止重复录入同一个条码的商品。2.3 分类表和供应商表的补充设计分类表和供应商表结构简单但有一个细节要注意分类建议支持两级用 parent_id 自关联这样界面可以做树形展示。供应商表保留名称、联系人、电话、地址即可。这两张表不参与库存计算但商品表通过 category_id 和 supplier_id 关联它们查询时用 JOIN 带出名称。CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id INT DEFAULT 0 COMMENT 0表示一级分类, sort_order INT DEFAULT 0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品分类表; CREATE TABLE supplier ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, contact VARCHAR(50), phone VARCHAR(20), address VARCHAR(255) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT供应商表;分类表的 parent_id 默认 0表示一级分类。查询时如果要拿完整路径可以用递归或者在应用层拼。小型系统里我一般只在应用层做两级拼接不写复杂 SQL。供应商表没有加太多约束因为实际使用中供应商信息经常变约束太死反而不好维护。3. 入库、销售与库存扣减把事务和并发问题按在地上3.1 入库流程的代码实现与参数说明入库操作看起来简单但里面有几个必须处理的点插入入库记录、更新商品库存、如果进货价变了要不要更新商品表里的 purchase_price。我一般会更新因为下一次销售算毛利时要用最新进货价。下面是一个用 Python MySQL 实现的入库函数框架用 Flask 或 FastAPI 都行这里只写核心逻辑。import pymysql def stock_in(conn, product_id, quantity, purchase_price, supplier_id, operator, remark): 入库操作插入流水 更新库存 更新进货价 conn: 数据库连接需支持事务 product_id: 商品ID quantity: 入库数量必须大于0 purchase_price: 本次进货单价 supplier_id: 供应商ID operator: 操作人 remark: 备注 if quantity 0: raise ValueError(入库数量必须大于0) with conn.cursor() as cur: # 1. 插入入库记录 cur.execute( INSERT INTO stock_in (product_id, quantity, purchase_price, supplier_id, operator, remark) VALUES (%s, %s, %s, %s, %s, %s), (product_id, quantity, purchase_price, supplier_id, operator, remark) ) # 2. 更新商品库存和最新进货价 cur.execute( UPDATE product SET stock stock %s, purchase_price %s WHERE id %s, (quantity, purchase_price, product_id) ) if cur.rowcount 0: raise ValueError(商品不存在) conn.commit()逻辑说明整个操作放在一个事务里先插流水再更新库存任何一步失败都回滚。参数上quantity 做了前置校验防止负数入库。purchase_price 直接覆盖商品表里的旧进货价这样商品表里始终是最新进货价。注意这里没有用 SELECT ... FOR UPDATE 锁行因为小型系统并发不高但如果你的场景里多个收银台同时操作建议在 UPDATE 前加SELECT stock FROM product WHERE id %s FOR UPDATE避免并发扣减导致库存错乱。3.2 销售出库与库存不足的判断销售出库比入库多一个判断库存够不够。如果库存不足要么阻止销售要么允许负库存但记录异常。我一般选择阻止因为负库存会让盘点变成玄学。下面是对应的销售函数。def sale(conn, product_id, quantity, sale_price, operator): 销售出库检查库存 插入销售记录 扣减库存 if quantity 0: raise ValueError(销售数量必须大于0) with conn.cursor() as cur: # 1. 锁定商品行查当前库存 cur.execute(SELECT stock, name FROM product WHERE id %s FOR UPDATE, (product_id,)) row cur.fetchone() if not row: raise ValueError(商品不存在) current_stock, product_name row if current_stock quantity: raise ValueError(f库存不足{product_name} 当前库存 {current_stock}本次销售 {quantity}) # 2. 插入销售记录 total_amount round(quantity * sale_price, 2) cur.execute( INSERT INTO sale (product_id, quantity, sale_price, total_amount, operator) VALUES (%s, %s, %s, %s, %s), (product_id, quantity, sale_price, total_amount, operator) ) # 3. 扣减库存 cur.execute(UPDATE product SET stock stock - %s WHERE id %s, (quantity, product_id)) conn.commit()这里用了SELECT ... FOR UPDATE目的是在并发销售时锁住这一行防止两个收银台同时读到相同库存然后都扣减。参数上total_amount 用 round 保留两位小数避免出现 0.30000000000000004 这种浮点结果。如果库存不足直接抛异常前端捕获后提示收银员。注意事务的粒度查询、插入、更新都在同一个事务里commit 放在最后。3.3 库存预警与低库存查询库存预警的逻辑很简单查 product 表里 stock 小于等于 stock_warn 且 status1 的商品。但实际用的时候店长希望看到的是“哪些商品快没了需要补货”而不是所有低于阈值的商品。我一般会加一个排序按 stock 升序最缺的排前面。SELECT id, name, stock, stock_warn, unit FROM product WHERE status 1 AND stock stock_warn ORDER BY stock ASC;这个查询不需要额外参数stock_warn 是每个商品自己的阈值。如果想让不同分类用不同阈值可以在 category 表里加一个默认预警值查询时用 COALESCE 做兜底。小型系统里我一般不做这么细统一用商品级阈值就够了。4. 避坑与排查库存对不上、金额算错、并发扣减的常见问题4.1 库存对不上入库和销售没走事务现象月底盘点发现系统库存比实际多出几件查流水发现某笔入库记录插入了但库存没加或者某笔销售扣了库存但销售记录没插。原因入库或销售操作没有放在事务里插入流水和更新库存是两条独立 SQL中间程序报错或连接断开只执行了其中一条。解决把两个操作包在同一个事务里用 conn.commit() 和 conn.rollback() 控制。上面第 3 章的代码已经这么做了但如果你用的是 ORM要确认 session 的 flush 和 commit 时机。4.2 金额算错用 FLOAT 存价格导致精度丢失现象销售统计里出现 19.900000000000002 这样的数字或者对账时发现总金额差几分钱。原因建表时用了 FLOAT 或 DOUBLE 存价格浮点数在计算机里无法精确表示十进制小数。解决金额字段一律用 DECIMAL(10,2)Python 里用 decimal.Decimal 而不是 float。如果已经建了表用 ALTER TABLE 改字段类型但要注意数据迁移时做四舍五入。4.3 并发扣减两个收银台同时卖同一件商品现象两个收银台同时卖最后一件商品系统都显示库存充足结果卖出两件库存变成 -1。原因销售函数里先 SELECT 查库存再 UPDATE 扣减两个请求都读到了库存 1然后都执行扣减。解决在 SELECT 时加 FOR UPDATE 锁行或者在 UPDATE 的 WHERE 里加 stock quantity 条件用 affected rows 判断是否扣减成功。我一般用 FOR UPDATE因为逻辑更直观。4.4 条码重复同一商品录入两次导致库存分裂现象同一个条码的商品在系统里有两条记录库存分散在两个 ID 下盘点时以为丢了货。原因barcode 字段没有加 UNIQUE 约束或者录入时没有做重复校验。解决建表时给 barcode 加 UNIQUE应用层在插入前先查一次。如果条码可以为空注意 MySQL 的 UNIQUE 允许多个 NULL所以空条码不会冲突这是符合预期的。4.5 时间字段时区不对流水时间差 8 小时现象入库和销售记录的时间比实际时间早或晚 8 小时查当天流水时漏掉部分记录。原因数据库服务器时区、应用服务器时区和连接字符串里的时区不一致。解决统一用 UTC 存储展示时转本地时区或者建表时用 DATETIME 并在连接串里指定 timezone。小型系统里我一般直接在 MySQL 配置里设 default-time-zone08:00应用层不再做转换。5. 从能用到好用用视图和定时任务把统计报表跑起来5.1 用视图简化日报查询系统跑起来之后店长每天要看两个数今天卖了多少钱、哪些商品卖得最好。如果每次都写 JOIN 和 GROUP BYSQL 会越写越长。我一般会建一个销售日报视图把商品名称、分类、销售数量、销售额提前拼好。CREATE VIEW v_daily_sale AS SELECT DATE(s.created_at) AS sale_date, p.id AS product_id, p.name AS product_name, c.name AS category_name, SUM(s.quantity) AS total_qty, SUM(s.total_amount) AS total_amount FROM sale s JOIN product p ON s.product_id p.id LEFT JOIN category c ON p.category_id c.id GROUP BY DATE(s.created_at), p.id, p.name, c.name;视图的好处是查询简单SELECT * FROM v_daily_sale WHERE sale_date CURDATE()就能拿到当天数据。注意视图不存储数据每次查询都会执行底层 SQL所以 sale 表的 created_at 索引要建好否则数据量大了会慢。参数上DATE(s.created_at) 会把时间截断到天如果跨天营业比如凌晨 2 点还在卖需要调整截断逻辑用营业日而不是自然日。5.2 用定时任务做库存快照库存预警是实时的但店长还想要一个“每天关店时的库存快照”用来对比每天的库存变化。我一般会加一张 stock_snapshot 表每天凌晨跑一个定时任务把当前所有商品的库存插进去。CREATE TABLE stock_snapshot ( id INT PRIMARY KEY AUTO_INCREMENT, snapshot_date DATE NOT NULL, product_id INT NOT NULL, stock INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_date_product (snapshot_date, product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;定时任务可以用 Python 的 APScheduler 或者系统 cron。插入时用INSERT ... ON DUPLICATE KEY UPDATE防止同一天重复跑导致数据重复。这个快照表的作用是当店长问“为什么昨天库存还有 20今天只剩 5”时你可以把两天的快照拉出来对比再结合入库和销售流水很快定位到是哪一笔出了问题。5.3 一个我常犯的错忘了给流水表建索引最后说一个我自己的教训。早期做这类系统时sale 表和 stock_in 表只建了主键没建 product_id 和 created_at 的索引。数据量小的时候没感觉跑到几万条之后查某个商品的销售历史要等好几秒。后来每次建流水表我都强制给 product_id 和 created_at 各建一个索引查询速度直接降到毫秒级。从那以后我每次建表都先问自己一句这张表以后会按什么字段查把索引加上再插数据。希望帮到你。本文还有配套的精品资源点击获取