13万条菜谱数据导入MySQL：从LOAD DATA清洗到全文检索的完整实践-北京尧图网络

简介这是一套面向餐饮类应用开发、美食类网站与内容平台搭建、数据分析及MySQL学习人群的菜谱食谱数据库内置约13万条真实菜谱数据。压缩包共4个文件以3个sql数据文件和1个txt说明文档组成总大小52.48MBsql文件分别承载catalog目录表、dishes菜谱表和link目录关联菜谱表负责建表与数据导入txt辅助说明字段含义与基本使用方式。三张表以目录、菜谱及关联关系为核心结构清晰便于按美食分类检索菜谱、快速定位某目录下的菜谱也可反查某菜谱所属分类可在此基础上开发菜谱检索、推荐、点餐或饮食分析类应用。资源同时配套36G菜谱图片通过网盘领取可进一步构建图文并茂的菜谱内容库。已有941人学习下载适合具备一定数据库基础、希望直接获得结构化菜谱数据用于业务开发或数据研究的读者。1. 13万条菜谱加 36G 图片这份数据到底值不值得接做菜谱类产品的人大概率经历过这种尴尬想做个家常菜推荐功能翻遍网上能找到的公开数据要么只有几十条样例要么字段乱到没法直接入库。看到“菜谱食谱数据-mysql【13万条数据36G图片超值】”这个标题时第一反应是量够了第二反应才是关键——MySQL 格式意味着数据至少是结构化的图片单独存放说明它大概率走的是“数据库存文本、文件系统存图片”的正规路子。这份数据值不值取决于你能不能把它顺利导入 MySQL、清洗干净、把图片路径对上最终变成后端能直接查询的食谱服务。下面这套流程是我自己落地这类数据集的固定打法照着走能省下大半天的试错时间。2. 导入前的准备建表语句与 LOAD DATA 的完整选择逻辑拿到数据集压缩包不要急着解压导入。先把“MySQL 格式”这个说法的真实含义搞清楚再决定导入方式、建表结构和字符集否则后面会反复删表重来。2.1 拿到数据包先做三件事查文件结构、确认导出方式、核对字符集常见做法是先解压到工作目录然后用一条命令看顶层文件结构。所谓 MySQL 格式在实际交付时至少有三种形态mysqldump 导出的 .sql 文件、CSV/TSV 等纯文本文件加一份建表说明、甚至是从其他数据库转换出来的 SQL 脚本。这三种形态的导入方式完全不同。mkdir -p /data/recipe cd /data/recipe unzip recipe_data.zip ls -la find . -maxdepth 2 -type f | head -n 30第一条命令创建目录并解压第二条列出顶层文件第三条把文件清单限制在两层目录内目的是快速确认是否存在 .sql、.csv、图片子目录。看到结果后打开文件头确认导出方式head -n 50 dump.sql head -n 5 recipe.csv file images/00000001.jpg如果 .sql 文件开头是CREATE TABLE加INSERT INTO那就是标准的 mysqldump 导出直接用 mysql 命令 source 导入即可如果看到的是LOAD DATA或者纯 CSV 文件就走后面要讲的 LOAD DATA 路线。file命令用来确认图片真实格式防止扩展名与内容不符——这种数据集里 JPEG 伪装成 PNG 的情况并不少见。字符集检查我会放在导入前做而不是导入后发现乱码再处理。用file命令看 CSV 文件的编码或者直接用head查看数据里中文是否正常显示。有一点需要提前说清楚MySQL 连接层、数据库层、表、字段四级都要统一使用 utf8mb4任何一级用了默认的 latin1中文都会变成问号。2.2 建表字段怎么设计决定未来三个月好不好用菜谱数据集的常见字段没有太多悬念菜名、分类、标签、食材、步骤、图片路径、烹饪时间、难度、来源。真正容易踩坑的是图片字段——有人喜欢直接把图片 URL 写进表里有人甚至把图片存成 BLOB这两种做法在大数据量下都有问题。我一般只存相对路径比如images/00000001.jpg域名和前缀留到服务层拼接这样以后从本地迁移到对象存储时不用改数据库。CREATE TABLE recipe_info ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL COMMENT 菜名, category VARCHAR(64) DEFAULT NULL COMMENT 分类, tags VARCHAR(255) DEFAULT NULL COMMENT 标签, ingredients TEXT COMMENT 食材清单, steps MEDIUMTEXT COMMENT 做法步骤, image_path VARCHAR(300) DEFAULT NULL COMMENT 图片相对路径, cook_time_min INT UNSIGNED DEFAULT NULL COMMENT 烹饪时长分钟, difficulty TINYINT UNSIGNED DEFAULT NULL COMMENT 难度 1-3, source VARCHAR(120) DEFAULT NULL COMMENT 数据来源标识, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_category (category), INDEX idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;字段设计的逻辑说三点。第一ingredients用 TEXT、steps用 MEDIUMTEXT因为步骤可能长达上千字TEXT 的 64KB 上限偶尔会顶到第二category和name必须加普通索引后续数据盘点、按分类统计和菜名检索都会用到第三建表时不加全文索引等数据导入后再根据实际查询需求决定避免导入过程被索引拖慢。COLLATEutf8mb4_unicode_ci保证排序和比较时中文按 Unicode 规则处理而不是按字节序。2.3 用 LOAD DATA INFILE 导入 13 万条命令、参数与耗时对比13 万条数据用逐条 INSERT 导入即便开启批量事务也要十分钟起步LOAD DATA 通常几秒到十几秒就能完成。差距大是因为 LOAD DATA 走的是 MySQL 内部的高速导入路径绕过了逐行的语法解析和事务提交开销。下面这条命令是整个导入流程的核心LOAD DATA LOCAL INFILE /data/recipe/recipe.csv INTO TABLE recipe_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, name, category, tags, ingredients, steps, image_path, cook_time_min, difficulty, source) SET category NULLIF(category, ), tags NULLIF(tags, ), cook_time_min NULLIF(cook_time_min, 0), difficulty NULLIF(difficulty, 0);逐段解释参数。LOCAL表示文件在客户端机器上不加时要求文件在 MySQL 服务器本地远程连接时十有八九报File not foundCHARACTER SET utf8mb4必须与建表字符集严格一致少了这一行是乱码的头号原因FIELDS TERMINATED BY ,告诉 MySQL 每一列的间隔符是逗号如果数据里某些字段内部也包含逗号必须配合OPTIONALLY ENCLOSED BY 使用让带引号的字段被当作整体读取IGNORE 1 LINES跳过表头。SET 子句里的NULLIF是空值清洗的常用手段把空字符串和 0 统一转换成 NULL避免后续统计时出现“烹饪时间为 0 分钟”的脏记录。导入完成后执行验证SELECT COUNT(*) AS total FROM recipe_info; SELECT MAX(id), MIN(id) FROM recipe_info;总数应与数据包描述一致id 的连续性也能侧面反映导入是否完整。需要提醒的是LOCAL导入要求 MySQL 客户端在连接时开启了local_infile参数若报错可以在连接串后面追加?local_infile1或者在 MySQL 配置文件[client]段设置local-infile1。这一步做完13 万条菜谱文本数据就已经躺在库里了接下来要面对的是数据质量——这往往是比导入本身更耗时的环节。3. 数据质量盘点用 SQL 摸清 13 万条菜谱的底细数据能查不等于数据能用。第三方数据集大多来自网页采集或半手工整理分类字段可能混乱、食材文本里可能带着 HTML 标签、同名菜谱可能重复出现。这个阶段的目标是搞清楚库里到底有什么、缺什么、脏在哪里。3.1 分类分布与字段完整率一张 SQL 看出数据底色先跑分类分布确认这个数据集是不是真的覆盖了足够广的菜系SELECT category, COUNT(*) AS cnt FROM recipe_info GROUP BY category ORDER BY cnt DESC LIMIT 30;正常的菜谱数据集分类分布应该集中在“家常菜”“凉菜”“汤品”“主食”等类别。如果发现分类字段里有“美食”“懒人食谱”“孕妇饮食”这类偏场景化的词说明原始数据混杂了多种分类体系统计时通常需要做一次归一化。接着看字段完整率SELECT COUNT(*) AS total, SUM(name IS NULL OR name ) AS missing_name, SUM(ingredients IS NULL OR ingredients ) AS missing_ingredients, SUM(steps IS NULL OR steps ) AS missing_steps, SUM(image_path IS NULL OR image_path ) AS missing_image FROM recipe_info;这一步看的是“能立刻进入业务”的数据比例。做推荐系统时菜名和食材是必须字段做内容展示时步骤和图片缺一不可。如果缺失比例超过 10%建议先做一轮字段回填而不是直接拿来开发。3.2 文本清洗把 HTML 标签和“盐少许”变成能计算的食材数据网页来源的菜谱数据食材和步骤字段里残留 HTML 标签、nbsp;、amp;字符是很常见的事。我一般直接用 SQL 做第一轮粗洗处理最典型的标签污染UPDATE recipe_info SET ingredients TRIM(BOTH \ FROM ingredients), steps REPLACE(steps, br, \n);去掉字段首尾的引号把br换成换行符是最基础的清理。第二轮清洗交给 Python 脚本处理更灵活因为 HTML 标签的形态太多正则匹配比 SQL 的 REPLACE 更可控import re def clean_html(text: str) - str: if not text: return text text re.sub(r[^], , text) text text.replace(nbsp;, ).replace(amp;, ).replace(lt;, ) return re.sub(r\s, , text).strip()这段脚本的核心是先用正则去除所有尖括号包裹的 HTML 标签再处理 HTML 实体字符占位最后合并连续空白。注意lt;和gt;必须先于amp;转换否则lt;里的会被提前转成amp;导致二次污染。清洗完成后 UPDATE 回表再重新统计字段长度可以验证清洗是否生效。3.3 去重与关联图片同名菜谱和缺失图才是真正的坑菜谱数据集的重复问题比想象中严重。同一道“红烧肉”可能在不同分类下采集了三次内容略有差异。用聚合查询找出重复条目SELECT name, COUNT(*) AS cnt FROM recipe_info GROUP BY name HAVING cnt 1 ORDER BY cnt DESC LIMIT 20;当发现大量同名记录时保留策略是优先保留带图片且步骤长度超过 100 字的记录剩下的标记删除。图片关联检查是另一个高频问题——数据库里的image_path指向的文件实际不存在。把数据库里的路径导出成清单与文件系统里的实际文件做一次对比mysql -u root -p -N -e SELECT image_path FROM recipe_info WHERE image_path IS NOT NULL recipe_db db_images.txt find /data/recipe/images -type f | sort disk_images.txt comm -23 (sort db_images.txt) disk_images.txt | head -n 20comm -23输出只在数据库清单中存在、磁盘上缺失的文件名。看到差异后别急着补文件先确认是文件名规则错位还是文件确实没交付——这个判断直接影响后面的图片关联方案怎么选。4. 36G 图片的落地工程路径关联与最小访问方案图片体积占了整个数据集的绝大部分却也是工程上最容易翻车的部分。36G 图片通常意味着几十万个小文件MySQL 里存的只是一串路径真正的开销在文件系统的管理和访问层的设计上。4.1 为什么图片不放进 MySQL从备份到查询的连锁反应见过有人把图片转成 BLOB 塞进 MySQL理由是“方便管理”。后果是表空间膨胀到几十 GInnoDB 缓冲池被大字段挤爆普通查询被拖成秒级每次全量备份都要拷几十 G 的数据库文件恢复时更是一场灾难。InnoDB 的聚簇索引结构不适合存大对象BLOB/TEXT 字段超过一定大小会存储在溢出页随机读性能极差。正确做法是 MySQL 存相对路径图片文件放独立目录两者通过image_path字段关联。这个方案对菜谱这种“文本小、图片多”的数据形态是最稳的。4.2 图片目录结构与命名规则避免单目录 40 万文件的尴尬小文件最怕的是单个目录文件数过多inode 耗尽或者ls直接卡死。36G 图片按单张 200KB 估算就是 18 万到 40 万张如果全部平铺在一个目录下文件系统会变得极难维护。我一般会按分类或者 ID 范围分桶mkdir -p /data/recipe_images cd /data/recipe_images find /data/recipe/images -type f | awk -F/ {print $NF} | head -n 5先看原始目录里文件名的真实形态。如果文件名是数字 ID直接按 ID 段分桶比如把00000001.jpg归入0000/00000001.jpg每 10000 张一个目录如果有分类信息就按category/00000001.jpg分。直接复制 36G 文件时要注意普通cp遇到海量小文件会非常慢建议用rsync并关闭压缩rsync -a --no-compress /data/recipe/images/ /data/recipe_images/00/加--no-compress是因为图片本身就是压缩格式再压缩只会白白消耗 CPU。复制完成后用 Python 校验图片文件能否被正常解码同时把image_path更新成分桶后的新路径。4.3 图片访问Nginx 静态服务与查询层的路径拼接本地图片目录配套 Nginx 静态服务是成本最低的访问方案。把图片目录直接暴露给 Nginx数据库里的image_path就是静态资源的相对 URLserver { listen 8080; server_name _; location /images/ { alias /data/recipe_images/; access_log off; expires 7d; add_header Cache-Control public; } }expires 7d让浏览器缓存图片七天减少重复请求access_log off避免海量图片请求把日志磁盘打爆这是图片服务的常见做法。业务层拿到数据库里的image_path后只需要拼接http://服务器地址:8080/images/前缀即可。将来迁移到对象存储时只需要改这一层拼装逻辑数据库完全不用动——这就是当初只存相对路径的价值。4.4 图片与数据库一致性校验脚本要做成可反复执行的路径更新和校验要写成能反复执行的脚本而不是一次性命令。脚本逻辑围绕两个目标数据库里的路径必须真实存在文件系统里的文件必须能被解码。核心逻辑如下import os import hashlib from pathlib import Path from PIL import Image def verify_image(path: str) - bool: if not os.path.exists(path): return False try: with Image.open(path) as img: img.verify() return True except Exception: return False def batch_rename_by_category(src_dir: Path, dst_root: Path): for img_path in src_dir.glob(*.jpg): category img_path.parent.name dst_dir dst_root / category dst_dir.mkdir(parentsTrue, exist_okTrue) dst dst_dir / img_path.name if not dst.exists(): img_path.rename(dst)verify()不会完整解码图片但能识别出损坏的文件头和截断的 JPEG 文件。batch_rename_by_category按来源目录的类别名重新分桶避免部分图片因为分类字段为空而全部堆到无分类目录。脚本跑完后再执行一次前面用过的comm对比确保库里每条记录的image_path在磁盘上都有对应文件。这一步做完36G 图片资源才算真正可用。5. 菜谱数据落地的五个典型坑从乱码到图片错位这个环节的每一条都来自真实踩坑记录。数据导入与关联阶段翻车率最高的五个问题按出现频率排一下。5.1 坑一LOAD DATA 导入后中文全部变成问号现象导入过程没有报错SELECT查询结果里所有中文字段显示为???。原因很直接建表时用了默认字符集或者 LOAD DATA 语句缺少CHARACTER SET utf8mb4客户端连接层和表字符集不一致写入时发生转换失败。解决方式是删表重建建表语句显式指定CHARSETutf8mb4 COLLATEutf8mb4_unicode_ciLOAD DATA 时同样显式指定字符集。检查连接层也不能省命令行登录后先执行SET NAMES utf8mb4。把这三处统一后乱码基本不再出现。5.2 坑二导入后 SELECT COUNT(*) 卡了十几秒现象数据量只有 13 万条但COUNT(*)竟然跑了好几次才出结果。原因通常是建表时没有索引InnoDB 需要扫描整个聚簇索引才能计数如果表里不小心混入了 BLOB 字段扫描代价更大。解决方法是先确认表结构里没有大字段再执行ALTER TABLE recipe_info ADD PRIMARY KEY (id); ANALYZE TABLE recipe_info;如果只是为了日常计数可以维护一个汇总表或者用SHOW TABLE STATUS中的行数估算值生产环境不建议对百万级大表频繁执行精确 COUNT。5.3 坑三数据库里的图片路径与文件对不上现象抽查发现SELECT image_path FROM recipe_info LIMIT 100里有超过三分之一指向不存在的文件。原因多半是导出方在整理数据集时图片文件的命名序号和数据库 ID 发生了偏移例如删除过部分记录后没有重新生成文件名。解决方式是以文件系统为准写一次路径重建脚本先遍历磁盘上所有图片文件再按 ID 规则拼出应有关联把缺失的image_path更新为磁盘上的真实文件路径。如果文件名完全没有规律就只能靠序号或图片内容的感知哈希去做匹配——这一步尽量在购买前找对方确认清楚命名规则。5.4 坑四把整库导入后MySQL 目录膨胀到 40 多 G现象数据落地两三天后磁盘告警MySQL 备份耗时直线上升。原因很简单当初把图片存成了 BLOB 字段。MySQL 的 InnoDB 引擎会把大字段存储在溢出页但索引和主键仍然要扫描这些页的指针表空间和备份体积根本压不住。解决方式是分两步第一步把 BLOB 数据导出成文件路径写回数据库第二步执行ALTER TABLE recipe_info DROP COLUMN image_blob释放空间。做完后再做一次OPTIMIZE TABLE recipe_info彻底回收碎片。这个教训是任何超过几百 KB 的对象都不该住进 MySQL。5.5 坑五步骤字段里夹着 HTML 标签和 JSON 代码现象用接口把步骤展示到前端时页面上渲染出div、p、{step:之类的内容。原因是有相当一部分数据来源于网页结构化提取清洗流程没有覆盖到。解决方式是先做一次全表扫描统计包含或{的记录数SELECT COUNT(*) AS dirty_cnt FROM recipe_info WHERE steps LIKE %% OR steps LIKE {%;清洗时优先用 Python 脚本结合正则处理比 SQL 的 REPLACE 更灵活。如果 JSON 是包裹在步骤内部的完整对象需要解析 JSON 后重新提取文本字段而不是简单去掉花括号——否则会把 JSON 的 key 也留在文本里。6. 进阶把这套数据变成带全文检索的食谱服务13 万条菜谱数据只有入库是不够的要让它变成一个能响应查询的食谱服务全文检索是性价比最高的第一步。MySQL 自带的全文索引配合 ngram 解析器对中文菜名和食材的检索效果够用并且不用额外引入搜索引擎组件。6.1 加全文索引ngram 是中文检索的一道坎直接对 name 和 tags 建 FULLTEXT 索引13 万条数据量下中文检索必须指定 ngram 解析器。ngram 会把中文文本按默认的 2 字粒度切分“红烧肉”会被切为“红烧”和“烧肉”两个 token。真实场景里这个粒度已经够用注意ngram_token_size是全局参数改小了会显著增加索引体积和维持成本。建索引命令ALTER TABLE recipe_info ADD FULLTEXT INDEX ft_name_tags (name, tags) WITH PARSER ngram;索引建好后执行检索验证SELECT id, name, MATCH(name, tags) AGAINST(红烧肉) AS relevance FROM recipe_info WHERE MATCH(name, tags) AGAINST(红烧肉) ORDER BY relevance DESC LIMIT 20;relevance返回相关性分数排序时按分数倒序即可拿到最匹配的菜谱列表。6.2 查询层加缓存别让全文索引被频繁命中全文检索本身有开销高频查询还是建议加一层缓存。用 Redis 缓存查询结果键按“关键词分页”设计TTL 设置 15 分钟保证数据更新后缓存能较快失效import redis import json r redis.Redis(hostlocalhost, port6379, decode_responsesTrue) def search_recipes(keyword: str, page: int 1, size: int 10): cache_key frecipe:search:{keyword}:{page}:{size} cached r.get(cache_key) if cached: return json.loads(cached) # 执行 MySQL 全文检索 result query_mysql(keyword, page, size) r.setex(cache_key, 900, json.dumps(result, ensure_asciiFalse)) return result这段逻辑先查缓存未命中再走 MySQL命中率高的关键词能把数据库压力降一个量级。注意 Redis 存储 JSON 时要传ensure_asciiFalse避免中文被转成\uXXXX把缓存数据搞大几倍。6.3 服务化一个最小的查询接口最后用 FastAPI 把检索能力暴露成 HTTP 接口前后端都能直接调用from fastapi import FastAPI, Query from pydantic import BaseModel app FastAPI() class RecipeResult(BaseModel): id: int name: str image_url: str app.get(/recipes, response_modellist[RecipeResult]) def list_recipes(q: str Query(..., min_length1), page: int 1): recipes search_recipes(q, pagepage) return [RecipeResult(idr[id], namer[name], image_urlf/images/{r[image_path]}) for r in recipes]这个接口把全文检索、图片路径拼装和分页逻辑全部串了起来调用方只需要传一个q参数就能拿到菜谱列表和图片地址。做数据落地这行我自己的习惯是每拿到一批数据先不在业务代码上花时间而是先把导入、校验、预览这三条链路跑通确认它能支撑最小闭环再往上层堆东西。否则数据清理做到一半发现路径对不上或者导入完发现字符集错了返工的都是纯耗时。过去吃过一次亏花了三个多小时导入了一整套食谱数据最后发现图片文件名与数据库 ID 整体偏移了一位只能重新生成路径关联。从那以后校验脚本都留成模板每次导入新数据就套用一遍。现在这套流程从拿到压缩包到接口可查半天内基本能跑完。希望帮到你。本文还有配套的精品资源点击获取

13万条菜谱数据导入MySQL：从LOAD DATA清洗到全文检索的完整实践

相关推荐

再见，SSE！你好，Streamable HTTP！轻松开发 Streamable HTTP MCP Server（TaoToken 统一 Key 通道版）

万字长文解读wen进化史：从论文到TaoToken模型家族全景复盘

Spring Bean 生命周期源码分析：实例化、初始化、后置处理器与销毁

更多资讯

CNN+Transformer混合模型：运动想象脑电分类实战与避坑指南

基于OpenCV手势识别的打地鼠游戏：从肤色分割到交互实现

Win8/Win10免安装GSQL绿色简版：解压即用与避坑指南

Java多前端心理健康评估系统：量表计分引擎与多端适配实战

卡内基梅隆大学研究者用TaoToken统一Key通道复现“以小博大”智能体路由实验

MongoDB聚合管道实战：从$match到$group的常用阶段精讲

今日更新

PHP htmlentities()函数用法讲解

AI Agent工程实战：从七要素到七个决策点的系统设计指南

AI入局芯片设计验证：验证收敛50倍加速的真相与应对

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？