13万条菜谱数据拆包：三张SQL表撑起食谱库

简介这是一份面向餐饮类应用开发者、数据分析学习者与菜谱网站搭建者的MySQL菜谱数据库资源可用于美食推荐系统、菜谱检索平台或数据挖掘练习等场景。压缩包共4个文件以3个sql脚本和1个txt说明为主整体约52.48MB其中sql文件分别承载目录、菜谱及目录关联三类数据txt则用于补充使用说明导入后即可快速构建结构清晰的关系型数据表。数据规模达13万条菜谱记录并配套约36G图片资源图片地址与提取码已在说明中给出方便按需下载图文素材。目录表、菜谱表与关联表之间通过link表建立多对多映射便于实现分类浏览、关键词检索与关联推荐等功能。目前已有938人学习下载适合需要真实、成规模中文菜谱数据来练手或验证业务逻辑的开发者参考使用。1. 13万条菜谱数据拆包三个 SQL 表怎么撑起一个能跑起来的食谱库拿到一个 13 万条、图片体积 36G 的菜谱数据包第一反应往往不是兴奋而是先确认它到底能不能直接喂进项目。这个资源的核心不是图片而是三张 MySQL 表catalog.sql存目录分类dishes.sql存菜谱主体link.sql负责目录与菜谱的多对多关联。它解决的是「从零搭一个食谱类应用时没有真实结构化数据可跑」的问题——搜索、分类筛选、详情页、分页列表全都能靠这三张表撑起来。适合做 JavaWeb 课程设计、Python 爬虫练手、小程序后端、数据可视化大屏的从业者也适合想练 SQL 关联查询和索引优化的新手。图片单独打包意味着你可以先只导数据、后补图片不会因为 36G 卡住建库流程。2. 三张表的结构与导入顺序先建 catalog再灌 dishes最后补 link2.1 为什么导入顺序不能乱这三张表之间存在明确的外键依赖关系。catalog是分类目录比如「家常菜」「川菜」「烘焙」dishes是菜谱明细包含菜名、食材、步骤、图片路径等字段link是中间表把某个菜谱挂到某个目录下。如果先导link它引用的catalog_id和dishes_id还不存在要么报外键错误要么产生脏关联。常见做法是关掉外键检查再导但我不建议——数据量一大后面排查关联缺失会非常痛苦。正确顺序是建库 → 导 catalog → 导 dishes → 导 link → 建索引。先看导入前的库和字符集准备。菜谱数据里中文占比极高字符集选错会出现大量乱码而且后期改字符集比重新导一遍还麻烦。-- 创建专用数据库字符集必须用 utf8mb4 CREATE DATABASE recipe_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE recipe_db; -- 导入前临时关闭外键检查仅用于加速批量导入 SET FOREIGN_KEY_CHECKS 0;utf8mb4而不是utf8是因为部分菜谱名或食材描述里可能出现生僻字或特殊符号utf8在 MySQL 里实际只支持三字节遇到四字节字符会截断。FOREIGN_KEY_CHECKS 0只在批量导入阶段用导完必须改回 1否则后续业务写入不会校验关联完整性。2.2 用命令行导入三个 SQL 文件假设解压后三个文件在同一目录用 mysql 客户端依次导入。Linux 和 Windows 命令略有差异下面以通用写法为主。# 导入目录表 mysql -u root -p recipe_db catalog.sql # 导入菜谱主表13万条数据这一步耗时最长 mysql -u root -p recipe_db dishes.sql # 导入关联表 mysql -u root -p recipe_db link.sql如果数据量导致单次导入超时可以在 my.cnf 里临时调大max_allowed_packet和net_read_timeout。13 万条纯文本 SQL 通常几百 MB 以内但dishes表如果内嵌了较长的步骤描述单条 INSERT 可能很大。导入完成后先别急着建索引用SELECT COUNT(*)确认三张表的行数是否与说明文件一致。SELECT COUNT(*) FROM catalog; SELECT COUNT(*) FROM dishes; SELECT COUNT(*) FROM link;link表的行数通常大于dishes因为一个菜谱可能同时属于「家常菜」和「快手菜」两个目录。如果link行数反而小于dishes说明有关联丢失需要检查导入过程中是否有报错被忽略。2.3 建索引别等查询慢了才补导入完成后立刻建索引比后期在 13 万行上补索引更省事。核心索引落在关联字段和常用筛选字段上。-- 关联表双字段索引加速「按目录查菜谱」和「按菜谱查目录」 ALTER TABLE link ADD INDEX idx_catalog_dish (catalog_id, dishes_id); ALTER TABLE link ADD INDEX idx_dish (dishes_id); -- 菜谱表按名称和分类的查询索引 ALTER TABLE dishes ADD INDEX idx_name (name); ALTER TABLE dishes ADD INDEX idx_category (category_id);idx_catalog_dish是联合索引遵循最左前缀原则能同时服务WHERE catalog_id ?和WHERE catalog_id ? AND dishes_id ?两种查询。单独再建idx_dish是为了反向查询——从菜谱详情页反查它属于哪些目录。索引不是越多越好dishes表如果字段多每加一个索引都会拖慢导入和更新所以只加真正出现在 WHERE 和 JOIN 条件里的列。3. 关联查询与分页把目录树和菜谱列表跑通3.1 目录关联菜谱的标准 JOIN 写法最常见的业务场景是用户点开某个目录看到该目录下所有菜谱带分页。这条 SQL 必须走索引否则 13 万行全表扫描会明显卡顿。-- 查询「家常菜」目录下的菜谱每页 20 条 SELECT d.id, d.name, d.image_path, d.description FROM dishes d INNER JOIN link l ON d.id l.dishes_id INNER JOIN catalog c ON l.catalog_id c.id WHERE c.name 家常菜 ORDER BY d.id LIMIT 20 OFFSET 0;INNER JOIN而不是LEFT JOIN是因为link表存在的意义就是建立有效关联没有关联的菜谱不应该出现在目录列表里。ORDER BY d.id配合主键索引比按name排序更稳定也更快。分页用LIMIT ... OFFSET在深分页时比如 OFFSET 10000性能会下降常见优化是先查主键再回表但 13 万条量级下普通分页足够用。3.2 统计每个目录下的菜谱数量首页或侧边栏经常要显示「川菜328 道」这样的计数。这条查询如果写成子查询13 万行下会非常慢正确做法是用GROUP BY配合关联。SELECT c.id, c.name, COUNT(l.dishes_id) AS recipe_count FROM catalog c LEFT JOIN link l ON c.id l.catalog_id GROUP BY c.id, c.name ORDER BY recipe_count DESC;这里用LEFT JOIN是为了让没有关联菜谱的空目录也显示出来计数为 0。GROUP BY后跟c.id, c.name而不是只跟c.id是为了兼容ONLY_FULL_GROUP_BY模式避免报错。如果目录表很大可以在link.catalog_id上单独建索引让 COUNT 走索引扫描而不是全表。3.3 菜谱详情与多目录归属一个菜谱可能属于多个目录详情页需要把它所有归属目录都查出来。这条查询用link表反向关联即可。SELECT c.name AS catalog_name FROM catalog c INNER JOIN link l ON c.id l.catalog_id WHERE l.dishes_id 10086;dishes_id 10086是示例实际使用时换成详情页传入的 ID。这条查询走idx_dish索引即使link表有几十万行也能毫秒级返回。如果详情页还要展示食材和步骤那部分字段在dishes表里用主键查一次即可不要和目录查询混在一条大 SQL 里否则可读性和缓存命中率都会变差。4. 图片路径与 36G 资源对接别把二进制塞进数据库4.1 图片存储的两种常见方案这个数据包的图片是单独打包的dishes表里存的应该是图片路径或文件名而不是 BLOB。常见做法有两种一是把图片放到 Web 服务器的静态目录数据库只存相对路径二是把图片传到对象存储数据库存 URL。前者适合本地开发和课程设计后者适合正式部署。-- 查看图片路径字段的实际存储格式 SELECT id, name, image_path FROM dishes LIMIT 5;如果image_path存的是类似images/12345.jpg的相对路径那你在项目里需要配置一个静态资源映射把/images/**指向实际解压出来的图片目录。如果存的是完整 URL那就要确认这些 URL 是否还能访问——数据包里的图片是离线打包的URL 大概率需要替换成你自己的地址。4.2 批量替换图片路径前缀假设图片解压后放在/data/recipe_images/而表里存的是旧前缀可以用一条 UPDATE 批量替换。注意先备份或者先用 SELECT 确认影响范围。-- 先确认要替换的行数 SELECT COUNT(*) FROM dishes WHERE image_path LIKE old_prefix/%; -- 确认无误后再执行替换 UPDATE dishes SET image_path REPLACE(image_path, old_prefix/, /data/recipe_images/) WHERE image_path LIKE old_prefix/%;REPLACE是字符串替换不是正则所以前缀必须写准确。WHERE条件不能省否则会全表更新13 万行下既慢又危险。执行前建议把sql_safe_updates打开强制 UPDATE 必须带 WHERE 或 LIMIT。4.3 图片缺失时的兜底查询36G 图片解压后未必和数据库记录一一对应上线前最好跑一遍缺失检查。可以在应用层做也可以用 SQL 先筛出可疑记录。-- 找出图片路径为空或明显异常的菜谱 SELECT id, name, image_path FROM dishes WHERE image_path IS NULL OR image_path OR image_path NOT LIKE %.jpg%;这条查询不走索引但在 13 万行上跑一次可接受。筛出来的记录可以导出成 CSV交给前端做占位图处理。不要试图在数据库里存图片二进制36G 塞进 MySQL 会让备份和迁移变成噩梦这是血泪经验。5. 避坑与排查导入、乱码、关联丢失的常见问题5.1 导入时报 Unknown command 现象用mysql xxx.sql导入时中途报错提示某个字符无法识别。原因通常是 SQL 文件里包含了 MySQL 客户端不认识的转义字符或者文件编码不是 UTF-8。解决先用file xxx.sql确认编码如果是 GBK 先转成 UTF-8导入时加--default-character-setutf8mb4参数。5.2 中文显示为问号或乱码现象导入成功但SELECT出来菜名全是???。原因建库时字符集不是utf8mb4或者连接字符集不对。解决检查SHOW VARIABLES LIKE character%确保character_set_client、character_set_connection、character_set_results都是utf8mb4。已经导错的数据只能重新导字符集问题没有后悔药。5.3 link 表关联查询结果为空现象catalog和dishes都有数据但 JOIN 查不出任何结果。原因link表里的catalog_id或dishes_id与主表实际 ID 对不上可能是导入时自增 ID 偏移或者字段类型不一致比如一边是 INT 一边是 VARCHAR。解决先SELECT * FROM link LIMIT 10看实际值再和catalog.id、dishes.id比对确认类型和取值范围一致。5.4 分页查询越翻越慢现象第一页很快翻到几百页后明显变慢。原因LIMIT ... OFFSET在深分页时会扫描并丢弃前 N 行。解决13 万条量级下可以接受但如果要优化改成基于游标的分页比如WHERE d.id 上一页最后一条ID ORDER BY d.id LIMIT 20这样每次只扫描 20 行。5.5 图片路径对但页面不显示现象数据库里image_path正确但前端 404。原因静态资源映射没配或者图片实际目录和数据库记录的前缀不一致。解决先在浏览器直接访问一个图片完整 URL确认文件存在再检查后端静态资源映射配置确保/images/**指向的物理目录和image_path拼接后能对上。6. 进阶用法用存储过程批量生成测试数据和验证关联完整性数据导入后除了直接查还可以用存储过程做两件事一是批量生成模拟的浏览记录或收藏记录用于压测分页和 JOIN 性能二是定期校验link表是否存在孤儿记录。下面这个存储过程演示如何批量插入测试收藏数据并顺带检查关联完整性。DELIMITER // CREATE PROCEDURE generate_test_favorites(IN batch_size INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE v_dish_id INT; DECLARE v_max_id INT; -- 取当前最大菜谱 ID避免插入不存在的关联 SELECT MAX(id) INTO v_max_id FROM dishes; WHILE i batch_size DO -- 随机取一个有效菜谱 ID SET v_dish_id FLOOR(1 RAND() * v_max_id); -- 只插入确实存在于 dishes 表中的 ID IF EXISTS (SELECT 1 FROM dishes WHERE id v_dish_id) THEN INSERT INTO favorites (user_id, dishes_id, created_at) VALUES (FLOOR(1 RAND() * 1000), v_dish_id, NOW()); SET i i 1; END IF; END WHILE; END // DELIMITER ;DELIMITER //是为了让存储过程体内的分号不被客户端提前截断这是 MySQL 存储过程最常见的踩坑点。batch_size控制插入条数RAND()配合MAX(id)生成随机菜谱 ID但必须用EXISTS过滤掉不存在的 ID否则会插入孤儿收藏记录。调用时执行CALL generate_test_favorites(10000);即可生成一万条测试数据。生成之后用一条查询验证link表有没有孤儿记录-- 检查 link 表中是否存在 dishes 表里没有的 dishes_id SELECT COUNT(*) AS orphan_count FROM link l LEFT JOIN dishes d ON l.dishes_id d.id WHERE d.id IS NULL;如果orphan_count大于 0说明有关联指向了不存在的菜谱需要清理或修复。这个检查我一般会在每次批量导入后强制走一遍因为数据包来源多、格式杂关联完整性靠肉眼是看不出来的。从那以后我每次导完这类多表关联的数据都先跑一遍孤儿检查再建索引省得后面业务查询出玄学问题。希望帮到你。本文还有配套的精品资源点击获取