简介这份资源是餐饮食品采购系统数据库建设中的原材料清单文档面向餐饮企业信息化人员、采购管理者及数据库设计初学者用于解决食材种类繁杂、规格与计量单位不统一导致的库存与采购管理难题。压缩包内共1个doc文件约864KB以表格化清单形式集中呈现蔬菜类等原材料的标准化命名与规格信息便于直接导入或参照建库。清单按英文缩写加中文名称的方式组织如芦荟、四季豆、奶白菜、西兰花、娃娃菜等并标注去根、净重、kg、pcs/bag等规格与单位为数据标准化和字段设计提供现成参考。已有101人学习下载读者可据此快速搭建食材主数据表统一命名与计量口径并延伸设计库存跟踪、供应商管理、采购计划、保质期预警及报表分析等模块提升采购效率与食品安全管控水平。1. 从一份 300 行食材清单说起餐饮采购数据库到底该怎么落地如果你接过餐饮 ERP 或供应链系统的活大概率见过这种场面采购部甩来一份 Excel里面密密麻麻几百行食材中英文混排、单位五花八门还夹着“去根 1/3”“净莴苣”“3pcs/bag/400g/pkt”这种让人头大的规格描述。这份《餐饮食品采购系统数据库建设原材料清单.doc》就是典型样本——它把蔬菜、水果、猪牛羊、禽类、冷冻海鲜按VG/、FR/、PK/、BL/、FBL/、Poultry/、Egg/、SF/这些前缀分了八大类每行都是“英文名 中文名 规格 单位”的结构。很多人拿到手第一反应是直接建一张表往里灌结果三个月后库存对不上、采购单重复、报表跑不出来。这篇笔记就按一线拆包思路把这份清单怎么变成能用的数据库讲透先立数据模型再走导入流程最后把踩过的坑摊开说。适合正在做餐饮供应链、库存管理或数据库课程设计的人照着能复现也能看清边界在哪。2. 数据建模把 300 行清单拆成 5 张核心表2.1 为什么不能一张大表装完先看清单里的真实数据长什么样。蔬菜类有VG/Aloe Vera 芦荟-kg也有VG/Cabbage, Baby 娃娃菜-3pcs/bag/400g/pkt海鲜类有SF/Prawn King, 明虾(冻) 4-6 头/kg还有SF/Scallops, Frozen Aus 10-20 澳洲冰冻带子,10-20 -kg。如果全塞进一张ingredients表字段会变成英文名、中文名、分类前缀、规格描述、单位、是否冷冻、产地、头数/大小……字段越加越多最后一半是 NULL。更致命的是同一个“明虾”在清单里出现 4 次4-6 头、8-10 头、13-15 头、16-20 头如果按行存采购时根本不知道选哪条。常见做法是拆成 5 张表category分类、ingredient食材主表、ingredient_spec规格变体、unit计量单位、supplier_ingredient供应商报价关联。这样“明虾”在ingredient里只有一条4 种头数规格挂在ingredient_spec下采购单选规格即可。2.2 建表 SQL 与字段含义下面这段 SQL 可以直接在 MySQL 8.0 或 PostgreSQL 里跑字段注释对应清单里的实际含义-- 分类表对应 VG/FR/PK/BL/FBL/Poultry/Egg/SF 八个前缀 CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, code VARCHAR(10) NOT NULL UNIQUE COMMENT 分类前缀如VG、SF, name_cn VARCHAR(50) NOT NULL COMMENT 中文分类名如蔬菜类, name_en VARCHAR(50) COMMENT 英文分类名 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 食材主表一行代表一个独立食材不包含规格变体 CREATE TABLE ingredient ( id INT PRIMARY KEY AUTO_INCREMENT, category_id INT NOT NULL, name_en VARCHAR(120) NOT NULL COMMENT 英文名如Prawn King, name_cn VARCHAR(80) NOT NULL COMMENT 中文名如明虾, is_frozen TINYINT(1) DEFAULT 0 COMMENT 是否冷冻0鲜/冰鲜1冻, origin VARCHAR(50) COMMENT 产地如澳洲、本地、泰国, remark VARCHAR(255) COMMENT 备注如去根1/3、去皮, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES category(id), INDEX idx_name_cn (name_cn), INDEX idx_category (category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 规格变体表同一食材的不同规格如明虾4-6头/8-10头 CREATE TABLE ingredient_spec ( id INT PRIMARY KEY AUTO_INCREMENT, ingredient_id INT NOT NULL, spec_desc VARCHAR(120) NOT NULL COMMENT 规格描述如4-6头、10-20, unit_id INT NOT NULL COMMENT 关联单位表, pack_desc VARCHAR(80) COMMENT 包装描述如3pcs/bag/400g/pkt, is_default TINYINT(1) DEFAULT 0 COMMENT 是否默认规格, FOREIGN KEY (ingredient_id) REFERENCES ingredient(id), INDEX idx_ingredient (ingredient_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 单位表kg、pc、box、pkt、bag 等 CREATE TABLE unit ( id INT PRIMARY KEY AUTO_INCREMENT, code VARCHAR(10) NOT NULL UNIQUE COMMENT 单位代码如kg、pc, name_cn VARCHAR(20) NOT NULL COMMENT 中文名如千克、个 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 供应商报价表同一食材不同供应商价格不同 CREATE TABLE supplier_ingredient ( id INT PRIMARY KEY AUTO_INCREMENT, supplier_id INT NOT NULL, spec_id INT NOT NULL, price DECIMAL(10,2) COMMENT 单价, lead_time_days INT DEFAULT 1 COMMENT 供货周期天数, is_active TINYINT(1) DEFAULT 1, FOREIGN KEY (spec_id) REFERENCES ingredient_spec(id), INDEX idx_spec (spec_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明category用code存VG、SF这些前缀导入时直接映射ingredient只存食材本身is_frozen和origin从清单里的“冻”“进口”“本地”等词提取ingredient_spec是关键把“4-6 头”“10-20”“3pcs/bag/400g/pkt”这类描述拆出来unit_id关联到kg或pcsupplier_ingredient留出价格和供货周期方便后面做采购建议。参数说明name_en和name_cn都建了索引因为采购搜索通常按中文名或英文名模糊查spec_desc不建索引因为规格描述太长且查询频率低is_default用来标记默认规格比如“明虾 13-15 头”设为默认采购单不选规格时自动带出。2.3 分类前缀与单位映射规则清单里的前缀和单位不是随便写的导入前必须定好映射规则否则后面查询会乱。下面这张表是我从这份清单里整理出的映射关系前缀分类中文名常见单位特殊单位示例VG蔬菜类kg3pcs/bag/400g/pkt、150g/pkt、100pcs/boxFR水果类kg125G/Box、100g/pcs、8头PK猪肉类kg5kg/只BL新鲜牛羊肉kg7骨、整只FBL冰鲜牛羊肉kg1Kg/pkt、7kg-8kg/块Poultry禽类kg、pc150g/pc、2.5kg、8kgEgg蛋类kg、pc高邮咸鸭蛋按 PCSF冷冻海鲜kg、box、pkt0.9kg/box、450g/pkt、4-6头注意Poultry和Egg里既有kg也有pc导入时不能一刀切。比如Poultry/Quail, Pc, Local 鹌鹑鲜 150g/pc单位是pc而Poultry/Chicken Whole, AA 肉鸡, 1.8kg-2.2kg鲜-kg单位是kg。我的做法是在ingredient_spec里逐行判断pack_desc存“150g/pc”这种完整描述unit_id只存主单位。3. 从 doc 到数据库解析、清洗、导入三步走3.1 用 Python 解析清单文本这份.doc里的内容本质是纯文本行每行格式基本是前缀/英文名 中文名-单位或前缀/英文名 中文名-规格-单位。用 Python 正则拆解最稳下面脚本处理了中英文混排、括号、逗号等边界import re import pymysql # 读取 doc 转出的纯文本按行处理 with open(raw_materials.txt, r, encodingutf-8) as f: lines [l.strip() for l in f if l.strip()] # 匹配模式前缀/英文名 中文名-单位 或 前缀/英文名 中文名-规格-单位 pattern re.compile( r^(?Pprefix[A-Za-z])/ # 前缀如 VG、SF r(?Pen_name.?)\s # 英文名非贪婪 r(?Pcn_name[\u4e00-\u9fa5〔〕]) # 中文名含括号 r(?Prest.*)$ # 剩余部分规格和单位 ) parsed [] for line in lines: m pattern.match(line) if not m: print(f跳过无法解析行: {line}) continue prefix m.group(prefix).upper() en_name m.group(en_name).strip().rstrip(,) cn_name m.group(cn_name).strip() rest m.group(rest).strip().lstrip(-).strip() # 从 rest 里提取单位kg、pc、box、pkt、bag 等 unit_match re.search(r(kg|pc|box|pkt|bag|pcs|Kg|KG)$, rest, re.IGNORECASE) unit unit_match.group(1).lower() if unit_match else kg spec rest[:unit_match.start()].strip(-).strip() if unit_match else rest parsed.append({ prefix: prefix, en_name: en_name, cn_name: cn_name, spec: spec, unit: unit }) print(f共解析 {len(parsed)} 条) # 输出前 5 条检查 for p in parsed[:5]: print(p)逻辑说明正则里en_name用非贪婪匹配避免把中文名前面的空格也吃进去cn_name限定中文字符和括号能正确抓取“娃娃菜”“明虾(冻)”这类rest里再单独提取单位因为单位可能在末尾也可能在规格中间。参数说明re.IGNORECASE让Kg、KG都能匹配rstrip(,)处理英文名末尾的逗号比如Cabbage, Baby。3.2 清洗规则中英文对齐与规格归一解析完只是第一步清单里大量数据需要清洗。我一般会走这几条规则第一英文名里的逗号统一去掉或替换。比如Cabbage, Baby清洗成Cabbage Baby避免后面按逗号拆分时出错。第二中文名里的括号统一成中文括号明虾(冻)和明虾冻要归一。第三规格描述里的O:4~5Cm、10-20、4-6 头统一成4-6头这种格式去掉多余空格和冒号。第四is_frozen字段从英文名或中文名里提取出现Frozen、冻、Chilled、冰鲜分别标记。def clean_item(item): # 英文名去逗号、去多余空格 item[en_name] re.sub(r[,\s], , item[en_name]).strip() # 中文名括号归一 item[cn_name] item[cn_name].replace((, ).replace(), ) # 规格归一去掉冒号、波浪号转横杠 item[spec] re.sub(r[Oo]:, , item[spec]) item[spec] item[spec].replace(~, -).strip() # 冷冻标记 text item[en_name] item[cn_name] if re.search(rFrozen|冻, text, re.IGNORECASE): item[is_frozen] 1 elif re.search(rChilled|冰鲜|鲜, text): item[is_frozen] 0 else: item[is_frozen] 0 return item cleaned [clean_item(p) for p in parsed]逻辑说明清洗顺序很重要先去英文名逗号再提取冷冻标记否则Frozen, Local里的逗号会干扰。参数说明re.sub(r[Oo]:, , ...)处理O:4~5Cm这种写法replace(~, -)把范围统一成横杠方便后面按范围查询。3.3 批量插入与事务控制清洗完的数据要批量入库这里用executemany加事务避免逐条插入导致性能差或中途失败留下脏数据conn pymysql.connect(hostlocalhost, userroot, passwordyour_pwd, databasecatering_procurement, charsetutf8mb4) cursor conn.cursor() # 先插入分类 categories [(VG, 蔬菜类), (FR, 水果类), (PK, 猪肉类), (BL, 新鲜牛羊肉), (FBL, 冰鲜牛羊肉), (Poultry, 禽类), (Egg, 蛋类), (SF, 冷冻海鲜)] cursor.executemany(INSERT IGNORE INTO category (code, name_cn) VALUES (%s, %s), categories) # 查分类 id 映射 cursor.execute(SELECT id, code FROM category) cat_map {code: cid for cid, code in cursor.fetchall()} # 插入食材主表和规格表 for item in cleaned: cat_id cat_map.get(item[prefix]) if not cat_id: continue cursor.execute( INSERT INTO ingredient (category_id, name_en, name_cn, is_frozen) VALUES (%s, %s, %s, %s), (cat_id, item[en_name], item[cn_name], item[is_frozen]) ) ing_id cursor.lastrowid # 单位 id 先查后插这里简化处理 cursor.execute(INSERT IGNORE INTO unit (code, name_cn) VALUES (%s, %s), (item[unit], item[unit])) cursor.execute(SELECT id FROM unit WHERE code%s, (item[unit],)) unit_id cursor.fetchone()[0] cursor.execute( INSERT INTO ingredient_spec (ingredient_id, spec_desc, unit_id, pack_desc) VALUES (%s, %s, %s, %s), (ing_id, item[spec], unit_id, item[spec]) ) conn.commit() cursor.close() conn.close() print(导入完成)逻辑说明INSERT IGNORE避免重复分类报错lastrowid拿到刚插入的食材 id再插规格表单位表用INSERT IGNORE加查询的方式保证不重复。参数说明charsetutf8mb4必须设否则中文名里的生僻字会乱码conn.commit()在所有插入完成后执行中途异常会回滚。提示导入前先在测试库跑一遍用SELECT COUNT(*)核对条数。这份清单我数过大约 300 多条如果导入后主表条数差太多大概率是正则没匹配上某些特殊行。4. 避坑与排查导入食材清单时最容易翻车的 5 个点4.1 现象中文名导入后变成问号或乱码原因数据库连接字符集不是utf8mb4或者建表时用了utf8MySQL 的utf8只支持 3 字节生僻字如“莴”“荠”会丢。解决建库建表统一用utf8mb4连接串加charsetutf8mb4Python 读文件时指定encodingutf-8。如果已经导入乱码只能清表重来没有后悔药。4.2 现象同一食材重复插入多条原因清单里“明虾”出现 4 次如果按行直接插ingredient表就会产生 4 条“明虾”。解决导入前先按name_cn name_en去重只保留一条主记录规格差异挂到ingredient_spec。我的做法是在 Python 里用dict按(en_name, cn_name)聚合规格存成列表。4.3 现象单位字段混入规格描述原因清单里VG/Cabbage, Baby 娃娃菜-3pcs/bag/400g/pkt这种行单位不是单一的kg或pc而是复合包装描述。如果正则只抓末尾单位会把pkt当主单位但实际采购可能按bag算。解决unit表里同时存pkt、bag、pcspack_desc保留完整描述采购时按pack_desc展示按unit计算库存。4.4 现象冷冻/冰鲜标记搞反原因清单里SF/Chilled groupa fillet 冰鲜石斑鱼柳和SF/Frozen Salmon Fish 冰冻三文鱼混在一起如果只按Frozen关键词匹配Chilled开头的行会被误标为冷冻。解决匹配顺序改成先查Chilled|冰鲜标 0再查Frozen|冻标 1最后默认 0。别小看这个库存管理里冷冻和冰鲜的保质期策略完全不同。4.5 现象导入中途报外键约束错误原因ingredient_spec插入时ingredient_id或unit_id不存在通常是分类映射没对上或者单位表还没插入对应单位。解决按“分类 → 单位 → 食材 → 规格”的顺序插入每步用INSERT IGNORE加查询兜底。如果还报错打开 MySQL 的SHOW ENGINE INNODB STATUS看具体是哪条外键失败。5. 进阶用视图和存储过程把采购建议跑起来5.1 建一个食材全貌视图数据导入后日常查询不该每次都 JOIN 五张表。建一个视图把常用字段拼好CREATE VIEW v_ingredient_full AS SELECT i.id AS ingredient_id, c.name_cn AS category_name, i.name_cn, i.name_en, i.is_frozen, s.spec_desc, u.code AS unit_code, s.pack_desc FROM ingredient i JOIN category c ON i.category_id c.id JOIN ingredient_spec s ON s.ingredient_id i.id JOIN unit u ON s.unit_id u.id;逻辑说明视图把分类名、食材名、规格、单位拼成一张宽表采购和库存查询直接SELECT * FROM v_ingredient_full WHERE name_cn LIKE %明虾%就行。参数说明视图不存数据底层表更新后视图自动反映适合报表和前端下拉框。5.2 低库存预警存储过程库存表inventory记录每个规格的当前量和安全库存下面存储过程每天跑一次输出需要补货的清单DELIMITER // CREATE PROCEDURE check_low_stock() BEGIN SELECT v.name_cn, v.spec_desc, inv.current_qty, inv.safety_qty, (inv.safety_qty - inv.current_qty) AS need_qty, v.unit_code FROM inventory inv JOIN v_ingredient_full v ON inv.spec_id v.ingredient_id WHERE inv.current_qty inv.safety_qty ORDER BY need_qty DESC; END // DELIMITER ;逻辑说明need_qty是建议补货量按缺口降序排采购先处理缺口大的。参数说明safety_qty按食材周转率设叶菜类一般设 2 天用量冷冻海鲜可以设 7 天。调用时CALL check_low_stock();即可。5.3 一个具体技巧用前缀快速过滤分类清单里的前缀VG/、SF/不只是分类标记还能直接用在查询里加速。比如前端传categorySF后端不用 JOIN 分类表直接SELECT * FROM v_ingredient_full WHERE category_name 冷冻海鲜 AND name_cn LIKE CONCAT(%, ?, %);如果数据量大可以在category表的code上建唯一索引建表时已经建了查询时先查category_id再查食材比直接 LIKE 快很多。我一般会在应用层缓存分类映射避免每次查库。从那以后我每次拿到新的食材清单都强制先跑一遍解析脚本用SELECT COUNT(*)和抽样 10 条核对确认中英文对齐、单位没混、冷冻标记正确再往生产库导。这份清单看着简单但 300 多行里藏着复合单位、重复食材、中英混排各种坑走完这一遍后面库存和采购模块能省掉大量对账时间。希望帮到你。本文还有配套的精品资源点击获取