尧图网页设计 
简介这份资源面向餐饮企业信息化建设者、采购管理人员及数据库初学者提供一套可直接参考的原材料清单文档用于搭建食品采购系统数据库的基础数据层。文档以蔬菜类为主涵盖芦荟、四季豆、西兰花、娃娃菜、莲藕、金针菇等上百种食材每条记录均标注英文代码、中文名称、规格处理方式如去根、去皮、净重及计量单位并区分kg、pcs/bag、pkt等不同计量口径便于统一命名与规格定义。资源包共1个doc文件约864KB内容紧凑、字段规整可直接导入关系型数据库作为食材主数据表。已有101人学习下载适合需要快速建立食材分类、库存跟踪、供应商管理与采购计划模块的读者参考也可作为数据标准化与质量控制字段设计的样例帮助减少重复整理成本提升采购与库存管理效率。1. 餐饮食品采购系统数据库建设原材料清单从一张 Excel 到可查询的采购底表很多餐饮老板第一次认真对待采购数据都是从一张叫“原材料清单”的 Excel 开始的。这张表里通常有品名、规格、单位、供应商、参考单价可能还有分类和备注。它看起来只是一份文档但一旦你想做采购系统它就是整个数据库的地基。问题在于绝大多数人直接把这张表导入数据库结果系统上线三个月后采购单里出现“土豆”“马铃薯”“洋芋”三个品项库存永远对不上。餐饮食品采购系统数据库建设的核心不是把 Excel 变成表而是把“原材料清单”变成一套有编码、有分类、有单位换算、有供应商关联的结构化数据。这篇文章面向正在做采购系统选型或自建数据库的从业者从清单字段设计讲到建表、导入、校验和日常维护让你能把手里那张 doc 或 xls 真正跑起来。2. 原材料清单为什么不能直接当数据库表用字段拆解与编码设计2.1 一张典型原材料清单里藏着哪些坑先看一张常见的餐饮原材料清单长什么样。列通常是序号、原材料名称、规格、单位、分类、参考单价、供应商、备注。行数从几十到上千不等。直接导入数据库后你会遇到几个典型问题。第一名称不统一。同一个东西采购叫“土豆”厨房叫“马铃薯”供应商报价单上写“荷兰土豆”。如果没有唯一编码系统无法判断这是不是同一个物料。第二规格和单位混在一起。比如“25kg/袋”既包含规格又包含单位拆不开就没法做单位换算。第三分类层级不固定。有的按食材类型分“蔬菜、肉类、调料”有的按采购渠道分“本地采购、中央配送”还有的按存储条件分“常温、冷藏、冷冻”。第四供应商一列经常写多个或者写“详见报价单”这在数据库里没法做外键关联。我一般会先把原始清单做一次“字段清洗映射”把一列拆成多列把自由文本变成枚举值。这一步不做后面所有查询和统计都是玄学。2.2 原材料主数据表的字段设计原材料清单在数据库里对应的核心表是“物料主数据表”我通常命名为ingredient或raw_material。字段设计要覆盖采购、库存、财务三个视角。下面是一张我常用的字段表。字段名类型说明是否必填idbigint自增主键是material_codevarchar(32)物料编码唯一是material_namevarchar(128)标准名称是alias_namesvarchar(255)别名逗号分隔否category_idint分类ID关联分类表是specvarchar(64)规格描述否base_unitvarchar(16)基本单位是purchase_unitvarchar(16)采购单位是conversion_ratedecimal(10,4)采购单位转基本单位系数是reference_pricedecimal(10,2)参考单价否storage_typetinyint存储条件1常温 2冷藏 3冷冻是shelf_life_daysint保质期天数否statustinyint1启用 0停用是created_atdatetime创建时间是物料编码建议用“分类码流水号”的方式比如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, skiprows2) # 列名映射中文表头转英文字段 column_map { 原材料名称: material_name, 规格: spec, 单位: purchase_unit, 分类: category_name, 参考单价: reference_price, 供应商: supplier_name, 备注: remark } df df.rename(columnscolumn_map) # 去掉完全空白的行 df df.dropna(howall) # 导出标准 CSV df.to_csv(material_raw.csv, indexFalse, encodingutf-8-sig) print(f共导出 {len(df)} 条原材料记录)这段代码的关键参数是skiprows2因为很多清单前两行是标题和说明。encodingutf-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], keepfirst) # 生成物料编码分类前缀 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)), axis1 ) df.to_csv(material_clean.csv, indexFalse, encodingutf-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( hostlocalhost, userroot, passwordyour_password, databasecatering_procurement, charsetutf8mb4 ) 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”一袋25kgconversion_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字段没维护。解决在物料管理界面加“停用”按钮停用后采购单查询只查status1的物料。历史采购单关联的物料即使停用也要能正常显示所以不要物理删除。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但采购单位是“箱”及时修正后避免了一次大规模库存错账。数据库建设这件事快就是慢慢就是快。原材料清单那几百行数据花两天清洗编码比上线后天天对账强。希望帮到你。本文还有配套的精品资源点击获取 餐饮采购系统数据库建设：原材料清单结构化与导入实战
简介这份资源面向餐饮企业信息化建设者、采购管理人员及数据库初学者提供一套可直接参考的原材料清单文档用于搭建食品采购系统数据库的基础数据层。文档以蔬菜类为主涵盖芦荟、四季豆、西兰花、娃娃菜、莲藕、金针菇等上百种食材每条记录均标注英文代码、中文名称、规格处理方式如去根、去皮、净重及计量单位并区分kg、pcs/bag、pkt等不同计量口径便于统一命名与规格定义。资源包共1个doc文件约864KB内容紧凑、字段规整可直接导入关系型数据库作为食材主数据表。已有101人学习下载适合需要快速建立食材分类、库存跟踪、供应商管理与采购计划模块的读者参考也可作为数据标准化与质量控制字段设计的样例帮助减少重复整理成本提升采购与库存管理效率。1. 餐饮食品采购系统数据库建设原材料清单从一张 Excel 到可查询的采购底表很多餐饮老板第一次认真对待采购数据都是从一张叫“原材料清单”的 Excel 开始的。这张表里通常有品名、规格、单位、供应商、参考单价可能还有分类和备注。它看起来只是一份文档但一旦你想做采购系统它就是整个数据库的地基。问题在于绝大多数人直接把这张表导入数据库结果系统上线三个月后采购单里出现“土豆”“马铃薯”“洋芋”三个品项库存永远对不上。餐饮食品采购系统数据库建设的核心不是把 Excel 变成表而是把“原材料清单”变成一套有编码、有分类、有单位换算、有供应商关联的结构化数据。这篇文章面向正在做采购系统选型或自建数据库的从业者从清单字段设计讲到建表、导入、校验和日常维护让你能把手里那张 doc 或 xls 真正跑起来。2. 原材料清单为什么不能直接当数据库表用字段拆解与编码设计2.1 一张典型原材料清单里藏着哪些坑先看一张常见的餐饮原材料清单长什么样。列通常是序号、原材料名称、规格、单位、分类、参考单价、供应商、备注。行数从几十到上千不等。直接导入数据库后你会遇到几个典型问题。第一名称不统一。同一个东西采购叫“土豆”厨房叫“马铃薯”供应商报价单上写“荷兰土豆”。如果没有唯一编码系统无法判断这是不是同一个物料。第二规格和单位混在一起。比如“25kg/袋”既包含规格又包含单位拆不开就没法做单位换算。第三分类层级不固定。有的按食材类型分“蔬菜、肉类、调料”有的按采购渠道分“本地采购、中央配送”还有的按存储条件分“常温、冷藏、冷冻”。第四供应商一列经常写多个或者写“详见报价单”这在数据库里没法做外键关联。我一般会先把原始清单做一次“字段清洗映射”把一列拆成多列把自由文本变成枚举值。这一步不做后面所有查询和统计都是玄学。2.2 原材料主数据表的字段设计原材料清单在数据库里对应的核心表是“物料主数据表”我通常命名为ingredient或raw_material。字段设计要覆盖采购、库存、财务三个视角。下面是一张我常用的字段表。字段名类型说明是否必填idbigint自增主键是material_codevarchar(32)物料编码唯一是material_namevarchar(128)标准名称是alias_namesvarchar(255)别名逗号分隔否category_idint分类ID关联分类表是specvarchar(64)规格描述否base_unitvarchar(16)基本单位是purchase_unitvarchar(16)采购单位是conversion_ratedecimal(10,4)采购单位转基本单位系数是reference_pricedecimal(10,2)参考单价否storage_typetinyint存储条件1常温 2冷藏 3冷冻是shelf_life_daysint保质期天数否statustinyint1启用 0停用是created_atdatetime创建时间是物料编码建议用“分类码流水号”的方式比如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, skiprows2) # 列名映射中文表头转英文字段 column_map { 原材料名称: material_name, 规格: spec, 单位: purchase_unit, 分类: category_name, 参考单价: reference_price, 供应商: supplier_name, 备注: remark } df df.rename(columnscolumn_map) # 去掉完全空白的行 df df.dropna(howall) # 导出标准 CSV df.to_csv(material_raw.csv, indexFalse, encodingutf-8-sig) print(f共导出 {len(df)} 条原材料记录)这段代码的关键参数是skiprows2因为很多清单前两行是标题和说明。encodingutf-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], keepfirst) # 生成物料编码分类前缀 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)), axis1 ) df.to_csv(material_clean.csv, indexFalse, encodingutf-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( hostlocalhost, userroot, passwordyour_password, databasecatering_procurement, charsetutf8mb4 ) 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”一袋25kgconversion_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字段没维护。解决在物料管理界面加“停用”按钮停用后采购单查询只查status1的物料。历史采购单关联的物料即使停用也要能正常显示所以不要物理删除。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但采购单位是“箱”及时修正后避免了一次大规模库存错账。数据库建设这件事快就是慢慢就是快。原材料清单那几百行数据花两天清洗编码比上线后天天对账强。希望帮到你。本文还有配套的精品资源点击获取