餐饮采购系统数据库建设：原材料清单结构化与导入实战

简介：这份资源面向餐饮企业信息化建设者、采购管理人员及数据库初学者，提供一套可直接参考的原材料清单文档，用于搭建食品采购系统数据库的基础数据层。文档以蔬菜类为主，涵盖芦荟、四季豆、西兰花、娃娃菜、莲藕、金针菇等上百种食材，每条记录均标注英文代码、中文名称、规格处理方式（如去根、去皮、净重）及计量单位，并区分kg、pcs/bag、pkt等不同计量口径，便于统一命名与规格定义。资源包共1个doc文件，约864KB，内容紧凑、字段规整，可直接导入关系型数据库作为食材主数据表。已有101人学习下载，适合需要快速建立食材分类、库存跟踪、供应商管理与采购计划模块的读者参考，也可作为数据标准化与质量控制字段设计的样例，帮助减少重复整理成本，提升采购与库存管理效率。

1. 餐饮食品采购系统数据库建设原材料清单：从一张 Excel 到可查询的采购底表

很多餐饮老板第一次认真对待采购数据，都是从一张叫“原材料清单”的 Excel 开始的。这张表里通常有品名、规格、单位、供应商、参考单价，可能还有分类和备注。它看起来只是一份文档，但一旦你想做采购系统，它就是整个数据库的地基。问题在于，绝大多数人直接把这张表导入数据库，结果系统上线三个月后，采购单里出现“土豆”“马铃薯”“洋芋”三个品项，库存永远对不上。餐饮食品采购系统数据库建设的核心，不是把 Excel 变成表，而是把“原材料清单”变成一套有编码、有分类、有单位换算、有供应商关联的结构化数据。这篇文章面向正在做采购系统选型或自建数据库的从业者，从清单字段设计讲到建表、导入、校验和日常维护，让你能把手里那张 doc 或 xls 真正跑起来。

2. 原材料清单为什么不能直接当数据库表用：字段拆解与编码设计

2.1 一张典型原材料清单里藏着哪些坑

先看一张常见的餐饮原材料清单长什么样。列通常是：序号、原材料名称、规格、单位、分类、参考单价、供应商、备注。行数从几十到上千不等。直接导入数据库后，你会遇到几个典型问题。

第一，名称不统一。同一个东西，采购叫“土豆”，厨房叫“马铃薯”，供应商报价单上写“荷兰土豆”。如果没有唯一编码，系统无法判断这是不是同一个物料。第二，规格和单位混在一起。比如“25kg/袋”既包含规格又包含单位，拆不开就没法做单位换算。第三，分类层级不固定。有的按食材类型分“蔬菜、肉类、调料”，有的按采购渠道分“本地采购、中央配送”，还有的按存储条件分“常温、冷藏、冷冻”。第四，供应商一列经常写多个，或者写“详见报价单”，这在数据库里没法做外键关联。

我一般会先把原始清单做一次“字段清洗映射”，把一列拆成多列，把自由文本变成枚举值。这一步不做，后面所有查询和统计都是玄学。

2.2 原材料主数据表的字段设计

原材料清单在数据库里对应的核心表是“物料主数据表”，我通常命名为ingredient或raw_material。字段设计要覆盖采购、库存、财务三个视角。下面是一张我常用的字段表。

物料编码建议用“分类码+流水号”的方式，比如VEG0001表示蔬菜类第一个物料。不要用名称拼音做编码，因为改名后编码就废了。别名字段很重要，它让采购员输入“土豆”时能匹配到标准名称“马铃薯”。

2.3 分类表和供应商关联表怎么建

分类表material_category至少要有id、parent_id、category_name、level四个字段。餐饮场景我一般设两级：一级是“蔬菜、肉类、水产、调料、粮油、酒水、包材”，二级是“叶菜、根茎、菌菇”等。层级不要超过三级，否则采购员选分类会翻车。

供应商关联用中间表material_supplier，字段包括material_id、supplier_id、supply_price、is_default、lead_time_days。一个物料可以对应多个供应商，但只有一个默认供应商。这样采购单生成时能自动带出默认供应商和最近报价。

CREATE TABLE material_category ( id INT PRIMARY KEY AUTO_INCREMENT, parent_id INT DEFAULT 0, category_name VARCHAR(64) NOT NULL, level TINYINT NOT NULL DEFAULT 1 ); CREATE TABLE ingredient ( id BIGINT PRIMARY KEY AUTO_INCREMENT, material_code VARCHAR(32) NOT NULL UNIQUE, material_name VARCHAR(128) NOT NULL, alias_names VARCHAR(255) DEFAULT '', category_id INT NOT NULL, spec VARCHAR(64) DEFAULT '', base_unit VARCHAR(16) NOT NULL, purchase_unit VARCHAR(16) NOT NULL, conversion_rate DECIMAL(10,4) NOT NULL DEFAULT 1, reference_price DECIMAL(10,2) DEFAULT 0, storage_type TINYINT NOT NULL DEFAULT 1, shelf_life_days INT DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_category (category_id), INDEX idx_name (material_name) ); CREATE TABLE material_supplier ( id BIGINT PRIMARY KEY AUTO_INCREMENT, material_id BIGINT NOT NULL, supplier_id BIGINT NOT NULL, supply_price DECIMAL(10,2) DEFAULT 0, is_default TINYINT DEFAULT 0, lead_time_days INT DEFAULT 1, UNIQUE KEY uk_material_supplier (material_id, supplier_id) );

上面建表语句里，conversion_rate是关键参数。比如采购单位是“袋”，基本单位是“kg”，一袋25kg，那这个值就是25。所有库存扣减和成本核算都按基本单位走，采购单按采购单位显示。storage_type用枚举值而不是文本，是为了后面做库存预警时能直接按条件筛选。alias_names用逗号分隔是简化做法，如果别名很多，建议单独建别名表。

3. 从 doc 到数据库：原材料清单导入的完整操作路径

3.1 先把 doc 或 xls 转成标准 CSV

拿到手的原材料清单如果是.doc，第一步是转成结构化格式。Word 里的表格复制到 Excel 经常串行，我一般用 Python 的python-docx读表格，或者直接让行政重新导出一份 Excel。转成 CSV 时注意三点：编码用 UTF-8，分隔符用逗号，首行必须是字段名。如果原始表头是中文，先映射成英文字段名。

import pandas as pd # 读取原始 Excel，跳过前两行说明文字 df = pd.read_excel('原材料清单.xlsx', skiprows=2) # 列名映射：中文表头转英文字段 column_map = { '原材料名称': 'material_name', '规格': 'spec', '单位': 'purchase_unit', '分类': 'category_name', '参考单价': 'reference_price', '供应商': 'supplier_name', '备注': 'remark' } df = df.rename(columns=column_map) # 去掉完全空白的行 df = df.dropna(how='all') # 导出标准 CSV df.to_csv('material_raw.csv', index=False, encoding='utf-8-sig') print(f'共导出 {len(df)} 条原材料记录')

这段代码的关键参数是skiprows=2，因为很多清单前两行是标题和说明。encoding='utf-8-sig'是为了 Excel 打开 CSV 时不乱码。导出后先别急着入库，用文本编辑器打开检查一遍，看有没有列错位。

3.2 用 Python 做数据清洗和编码生成

CSV 里通常还有空值、重复名称、单位不统一的问题。下一步做清洗：名称去空格、全角转半角、重复名称合并、自动生成物料编码。分类名称要映射到分类表的 ID。

import pandas as pd import re df = pd.read_csv('material_raw.csv') # 名称清洗：去空格、全角转半角 def clean_name(name): if pd.isna(name): return '' name = str(name).strip() name = name.replace('（', '(').replace('）', ')') return re.sub(r'\s+', '', name) df['material_name'] = df['material_name'].apply(clean_name) # 删除名称为空的行 df = df[df['material_name'] != ''] # 按名称去重，保留第一条 df = df.drop_duplicates(subset=['material_name'], keep='first') # 生成物料编码：分类前缀 + 4位流水号 category_prefix = { '蔬菜': 'VEG', '肉类': 'MEA', '水产': 'AQU', '调料': 'SEA', '粮油': 'GRA', '酒水': 'BEV', '包材': 'PAC' } df['prefix'] = df['category_name'].map(category_prefix).fillna('OTH') df['seq'] = df.groupby('prefix').cumcount() + 1 df['material_code'] = df['prefix'] + df['seq'].apply(lambda x: f'{x:04d}') # 单位统一：把“斤”转成“kg”，1斤=0.5kg def convert_unit(row): unit = str(row['purchase_unit']).strip() if unit == '斤': return 'kg', 0.5 elif unit == '公斤': return 'kg', 1.0 elif unit == '袋': return '袋', 1.0 return unit, 1.0 df[['purchase_unit', 'conversion_rate']] = df.apply( lambda r: pd.Series(convert_unit(r)), axis=1 ) df.to_csv('material_clean.csv', index=False, encoding='utf-8-sig') print(df[['material_code', 'material_name', 'purchase_unit', 'conversion_rate']].head(10))

清洗逻辑里，drop_duplicates只保留第一条，实际业务中如果两条记录规格不同，应该保留为两个物料，所以去重前要先判断规格是否一致。单位换算这里只处理了“斤”和“公斤”，实际清单里可能还有“件”“箱”“桶”，需要按业务补充映射表。conversion_rate为0.5表示1斤等于0.5kg，入库时库存按kg记。

3.3 批量插入数据库并做唯一性校验

清洗后的 CSV 用pandas.to_sql或逐行 INSERT 入库。入库前先查一遍material_code和material_name是否已存在，避免重复导入。

import pymysql import pandas as pd conn = pymysql.connect( host='localhost', user='root', password='your_password', database='catering_procurement', charset='utf8mb4' ) cursor = conn.cursor() df = pd.read_csv('material_clean.csv') insert_sql = """ INSERT INTO ingredient (material_code, material_name, category_id, spec, base_unit, purchase_unit, conversion_rate, reference_price, storage_type, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, 1) ON DUPLICATE KEY UPDATE material_name = VALUES(material_name), reference_price = VALUES(reference_price) """ success, skip = 0, 0 for _, row in df.iterrows(): # 分类名称转 category_id，这里假设已提前建好分类 cursor.execute("SELECT id FROM material_category WHERE category_name=%s", (row['category_name'],)) cat = cursor.fetchone() if not cat: skip += 1 continue try: cursor.execute(insert_sql, ( row['material_code'], row['material_name'], cat[0], row.get('spec', ''), 'kg', row['purchase_unit'], row['conversion_rate'], row.get('reference_price', 0), 1 )) success += 1 except Exception as e: print(f"插入失败: {row['material_name']}, 原因: {e}") skip += 1 conn.commit() print(f'成功插入 {success} 条，跳过 {skip} 条') cursor.close() conn.close()

这里用ON DUPLICATE KEY UPDATE是为了重复导入时更新价格而不是报错。base_unit统一写kg是简化处理，实际如果基本单位有“个”“瓶”，要从清洗结果里取。入库后一定要跑一遍校验 SQL，检查有没有category_id为空、conversion_rate为0、material_code重复的记录。

4. 采购系统数据库建设避坑：原材料清单落地时的 5 个血泪教训

4.1 坑一：同名不同物被合并，库存永远对不上

现象：系统里“五花肉”只有一个物料，但采购有时买的是带皮五花，有时是去皮五花，成本差很多，库存扣减后总金额对不上。原因：清洗时按名称去重，把规格不同的记录合并了。解决：去重前先按“名称+规格”组合判断，规格不同就生成不同物料编码，名称后面加后缀区分，比如“五花肉(带皮)”和“五花肉(去皮)”。

4.2 坑二：单位换算系数填反，采购单数量放大100倍

现象：采购单里“大米”数量显示2500，实际应该是25袋。原因：采购单位是“袋”，基本单位是“kg”，一袋25kg，conversion_rate应该填25，但填成了0.04。解决：入库前用一条校验 SQL 检查conversion_rate是否小于1且采购单位不是基本单位，如果是就人工复核。我一般要求所有换算系数必须大于等于1，小于1的只允许“斤转kg”这种特例并单独标记。

4.3 坑三：分类表没建好，采购员选不到对应分类

现象：导入时发现“冻品”这个分类在分类表里不存在，导致几十条物料全部跳过。原因：原始清单的分类名称和系统分类表不一致，比如清单写“冷冻食品”，系统里叫“冷冻”。解决：导入前先跑一次分类名称去重，和分类表做左连接，找出未匹配的分类，人工映射后再导入。不要用程序自动模糊匹配，容易把“调料”匹配到“粮油”。

4.4 坑四：参考单价带货币符号，入库变成0

现象：CSV 里价格列是“￥12.50”，入库后reference_price全是0。原因：数据库 decimal 字段不接受货币符号，程序转换时抛异常被吞掉了。解决：清洗阶段用正则去掉所有非数字和小数点字符，空值填0。入库后抽查10条价格是否和原始清单一致。

4.5 坑五：没有停用机制，淘汰物料还在采购单里出现

现象：某个调料已经不用了，但采购员新建采购单时还能选到。原因：物料表只有新增没有停用，status字段没维护。解决：在物料管理界面加“停用”按钮，停用后采购单查询只查status=1的物料。历史采购单关联的物料即使停用也要能正常显示，所以不要物理删除。

5. 原材料清单的日常维护与采购系统联动技巧

5.1 用触发器自动记录价格变更历史

原材料价格是波动的，参考单价改一次就覆盖一次，后面想查“上个月土豆多少钱”就查不到了。我一般会在ingredient表上挂一个触发器，价格变更时自动写入price_history表。

CREATE TABLE price_history ( id BIGINT PRIMARY KEY AUTO_INCREMENT, material_id BIGINT NOT NULL, old_price DECIMAL(10,2), new_price DECIMAL(10,2), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER $$ CREATE TRIGGER trg_price_update AFTER UPDATE ON ingredient FOR EACH ROW BEGIN IF OLD.reference_price <> NEW.reference_price THEN INSERT INTO price_history (material_id, old_price, new_price) VALUES (OLD.id, OLD.reference_price, NEW.reference_price); END IF; END$$ DELIMITER ;

触发器逻辑很简单：只有价格真正变化时才记录。OLD和NEW是 MySQL 触发器关键字，分别代表更新前和更新后的行。有了这张历史表，采购系统就能做价格趋势图，也能在供应商涨价时自动提醒。

5.2 用视图把原材料清单变成采购员能看懂的查询

采购员不需要看category_id和conversion_rate，他们要看的是“物料名称、规格、单位、默认供应商、最新价格”。建一个视图把多张表拼起来。

CREATE VIEW v_material_purchase AS SELECT i.material_code AS 物料编码, i.material_name AS 物料名称, i.spec AS 规格, i.purchase_unit AS 采购单位, c.category_name AS 分类, s.supplier_name AS 默认供应商, ms.supply_price AS 最新报价, i.reference_price AS 参考单价, i.status AS 状态 FROM ingredient i LEFT JOIN material_category c ON i.category_id = c.id LEFT JOIN material_supplier ms ON i.id = ms.material_id AND ms.is_default = 1 LEFT JOIN supplier s ON ms.supplier_id = s.id WHERE i.status = 1;

视图里LEFT JOIN保证即使没有默认供应商的物料也能显示。WHERE i.status = 1过滤掉停用物料。采购员直接查这个视图就能导出采购底表，不用理解底层表结构。

5.3 定期跑一致性校验 SQL

原材料清单不是导入一次就完事，供应商换、规格改、分类调整都会让数据漂移。我习惯每周跑一次校验脚本，检查四类问题：物料没有默认供应商、换算系数为0或空、分类ID在分类表里不存在、参考单价超过半年未更新。发现异常就导出清单让采购部确认。这个习惯帮我省了很多后悔药，有一次发现三十多个物料的换算系数是1但采购单位是“箱”，及时修正后避免了一次大规模库存错账。

数据库建设这件事，快就是慢，慢就是快。原材料清单那几百行数据，花两天清洗编码，比上线后天天对账强。希望帮到你。

本文还有配套的精品资源，点击获取