【美食菜谱】鸿蒙数据库建模：主明细表与按食材反查
实例：美食菜谱（Recipe Book）｜技术：菜谱/食材一对多主明细表、LIKE 反查、JOIN、难度/用时字段
一、业务需求分析
1.1 菜谱应用的两个核心场景
美食菜谱是手机应用的高频品类。用户有两个典型动作：
- “我想做一道菜”——浏览菜谱，看食材、步骤、难度、用时；
- “我家有鸡蛋和西红柿，能做什么？”——按食材反查菜谱，这是菜谱类应用区别于普通列表的最大卖点。
第二个场景对数据建模提出要求：食材不是菜谱的一个字符串字段，而是独立的一对多明细。一道菜有多个食材，一个食材出现在多道菜里（多对多关系，本实例简化为菜谱 → 食材的 1:N 明细，反查靠 LIKE）。
1.2 功能清单
| 编号 | 功能 | 技术要点 |
|---|---|---|
| 1 | 新增菜谱（菜名/分类/难度/用时/食材/步骤） | 事务：菜谱 + 多条食材明细 |
| 2 | 分类筛选 | equalTo + Tab |
| 3 | 菜谱详情（食材清单 + 步骤 + 贴士） | 主表 + 从表组装 |
| 4 | 按食材反查菜谱 ★ | 食材表 LIKE + JOIN 菜谱表 |
| 5 | 统计（菜谱数/分类数/平均用时/食材数） | COUNT DISTINCT + AVG |
| 6 | 删除菜谱 | 先删食材再删菜谱 |
1.3 与前 29 个实例的关系
| 实例 | 从表用途 | 本实例特色 |
|---|---|---|
| 26 快递 | 时间线（事件） | — |
| 27 图书 | 借阅（交易） | — |
| 29 药箱 | 数量日志（审计） | — |
| 30 菜谱 | 食材明细（结构） | 从表是主表内容的组成部分，且支持按从表字段反查主表 |
30 的独特价值：从表不只是"记录"，而是主表实体的结构性内容——没有食材明细，菜谱就不完整。同时，“反查”（从食材出发找菜谱）演示了从表驱动主表查询的方向反转。
二、字段设计表
2.1 主表 recipe（菜谱）
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | 自增主键 |
| name | TEXT | NOT NULL | 菜名 |
| category | TEXT | DEFAULT ‘家常菜’ | 分类（家常菜/汤羹/甜品/主食/凉菜/早餐） |
| difficulty | INTEGER | NOT NULL DEFAULT 1 | 难度 1易 2中 3难 |
| time_cost | INTEGER | NOT NULL DEFAULT 30 | 用时（分钟） |
| servings | INTEGER | NOT NULL DEFAULT 2 | 几人份 |
| steps | TEXT | DEFAULT ‘’ | 步骤（多行文本，\n 分隔） |
| tips | TEXT | DEFAULT ‘’ | 小贴士 |
| created_at | INTEGER | NOT NULL | 录入时间戳 |
2.2 从表 recipe_ingredient（食材明细）
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | INTEGER | PRIMARY KEY AUTOINCREMENT | 自增主键 |
| recipe_id | INTEGER | NOT NULL | 逻辑外键 → recipe.id |
| name | TEXT | NOT NULL | 食材名（西红柿/鸡蛋…） |
| amount | TEXT | DEFAULT ‘’ | 用量（“2个”/“500克”） |
2.3 设计要点详解
要点一：steps 用 TEXT 多行存储而非步骤子表。
步骤是"有序的文本段落"，用 \n 分隔存一个 TEXT 字段，页面按 \n 分割显示。为什么不做步骤子表（step 表存 step_no/内容）？
- 步骤是展示性内容，无需按步骤查询/统计（不像食材需要反查）；
- 一个字段 + 前端 split 足够，避免为"排序展示"建表的过度设计。
判断标准：需要查询/聚合的从表才建表。食材要反查（LIKE），建表；步骤只展示，TEXT 存储。
要点二：食材明细的 amount 是字符串。
“2个”、“500克”、“适量”——用量格式多样（个数/重量/模糊量），用 TEXT 原样存储最灵活。若用数字（500）+ 单位（克）两列，需要强约束格式，且"适量/少许"这类模糊量无法表达。文本用量是菜谱场景的务实选择。
要点三：食材与菜谱是多对多，为何简化为 1:N？
真实世界：西红柿出现在番茄炒蛋、西红柿蛋汤等多道菜。完整建模需"菜谱表 + 食材表 + 关联表（菜谱-食材多对多）"。本实例简化为菜谱 → 食材的 1:N 明细表（每道菜存自己的食材行），反查靠 LIKE 模糊匹配。
简化代价：同一食材的多条记录（不同菜谱下各存一行"西红柿"），有冗余；简化收益：不需要第三张关联表，反查直接 WHERE name LIKE '%西红柿%'。家庭菜谱几十道菜的规模，冗余可接受；大规模菜谱库应做正规化（见深度扩展）。
三、建表 SQL
CREATE TABLE IF NOT EXISTS recipe (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
category TEXT DEFAULT '家常菜',
difficulty INTEGER NOT NULL DEFAULT 1,
time_cost INTEGER NOT NULL DEFAULT 30,
servings INTEGER NOT NULL DEFAULT 2,
steps TEXT DEFAULT '',
tips TEXT DEFAULT '',
created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS recipe_ingredient (
id INTEGER PRIMARY KEY AUTOINCREMENT,
recipe_id INTEGER NOT NULL,
name TEXT NOT NULL,
amount TEXT DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_recipe_category ON recipe (category);
CREATE INDEX IF NOT EXISTS idx_ing_recipe ON recipe_ingredient (recipe_id);
CREATE INDEX IF NOT EXISTS idx_ing_name ON recipe_ingredient (name);
索引分析：
idx_recipe_category：分类筛选；idx_ing_recipe：按菜谱查食材（详情组装）；idx_ing_name：按食材反查的 LIKE 前缀索引——LIKE '%西红柿%'的前导通配符让索引部分失效（无法用 B-tree 前缀匹配），但 SQLite 会做索引扫描；若查询以固定前缀开头（LIKE '西红柿%'），索引完全生效。教学中保留索引并在深度扩展说明 LIKE 优化。
四、RecipeDao 数据访问层
4.1 新增菜谱：事务插入主表 + 多条明细 ★核心方法
static async insertRecipe(context: common.Context, r: Recipe, ingredients: Ingredient[]): Promise<number> {
const store = await RecipeDao.getStore(context);
await store.beginTransaction();
try {
const values: relationalStore.ValuesBucket = {
name: r.name, category: r.category, difficulty: r.difficulty,
time_cost: r.timeCost, servings: r.servings,
steps: r.steps, tips: r.tips, created_at: r.createdAt,
};
const recipeId = await store.insert(RecipeDao.TABLE, values);
for (const ing of ingredients) {
await store.insert(RecipeDao.INGREDIENT_TABLE, {
recipe_id: recipeId, name: ing.name, amount: ing.amount,
});
}
await store.commit();
return recipeId;
} catch (e) {
await store.rollBack();
throw new Error(`新增菜谱失败: ${JSON.stringify(e)}`);
}
}
为什么必须事务：一道菜 = 1 条主表 + N 条食材明细，明细是主表的组成部分。如果主表插入成功、某条食材插入失败，就产生"没有食材的菜谱"——残破数据。事务保证"一道菜要么完整入库，要么全部不存"。
批量插入的循环：食材通常 3~8 条，循环逐条 insert 可接受。数据量大时可考虑批量 API，但教学场景循环清晰直观。
4.2 按食材反查：LIKE + JOIN ★核心查询
static async searchByIngredient(context: common.Context, ingredient: string): Promise<IngredientMatch[]> {
const store = await RecipeDao.getStore(context);
const result = await store.querySql(
`SELECT r.id AS rid, r.name AS rname, r.category AS rcat, i.name AS iname
FROM ${RecipeDao.INGREDIENT_TABLE} i
LEFT JOIN ${RecipeDao.TABLE} r ON i.recipe_id = r.id
WHERE i.name LIKE '%${ingredient}%'
ORDER BY r.id`
);
// 遍历组装 IngredientMatch[]
}
查询方向反转：普通查询"菜谱 → 食材"（先主后从）；反查是"食材 → 菜谱"（先从后主）。SQL 里以从表 i 为驱动表，JOIN 主表取菜谱信息。
LIKE 模糊匹配：i.name LIKE '%鸡蛋%' 匹配食材名含"鸡蛋"的所有行——“鸡蛋”、"鸡蛋清"都命中。这是"家里有什么能做什么"的粗粒度反查。
LEFT JOIN 的意义：从表驱动时用 LEFT JOIN 保证"即使菜谱被删（理论上先删食材不会发生），查询也不报错"。实际由于删除是先删食材，此处 JOIN 结果必有主表匹配，LEFT/RIGHT 等价——用 LEFT 是防御性习惯。
防注入提醒：ingredient 来自用户输入，直接拼进 SQL 有注入风险（输入 ' OR 1=1 -- 会命中全表）。本实例教学演示保留拼接，生产必须用参数化或 predicates.like。这一点在深度扩展里重点强调。
4.3 菜谱视图组装（主表 + 食材 + 主料）
static async queryRecipeViews(context: common.Context, category: string): Promise<RecipeView[]> {
const recipes = category === '' ? await RecipeDao.queryAll(context) : await RecipeDao.queryByCategory(context, category);
const views: RecipeView[] = [];
for (const r of recipes) {
const ingredients = await RecipeDao.queryIngredients(context, r.id);
const v: RecipeView = {
recipe: r,
ingredients: ingredients,
mainIngredient: ingredients.length > 0 ? ingredients[0].name : '',
};
views.push(v);
}
return views;
}
主料 = 第一个食材：约定食材列表第一个是主料，列表页显示"主料：西红柿"。这是简单的约定而非算法——主料识别用"用户输入顺序"表达，避免 NLP。
N+1 组装：每道菜一次 queryIngredients，几十道菜无压力；大规模可 JOIN 优化（见深度扩展）。
4.4 统计：COUNT DISTINCT + AVG
static async summary(context: common.Context): Promise<{ recipes: number; categories: number; avgTime: number; ingredients: number }> {
// 主表：总数 / 分类数 / 平均用时
const rResult = await store.querySql(
`SELECT COUNT(*) AS cnt, COUNT(DISTINCT category) AS cats,
COALESCE(AVG(time_cost), 0) AS avg_time
FROM ${RecipeDao.TABLE}`
);
// 从表：食材总数
const iResult = await store.querySql(`SELECT COUNT(*) AS c FROM ${RecipeDao.INGREDIENT_TABLE}`);
}
COUNT(DISTINCT category)：去重计数——“共有几类菜”。AVG(time_cost)：平均用时，COALESCE(..., 0) 兜底空表。两个统计分属两表，各一条 SQL。
4.5 删除：先子后父
static async deleteRecipe(context: common.Context, id: number): Promise<void> {
const store = await RecipeDao.getStore(context);
const ingPredicates = new relationalStore.RdbPredicates(RecipeDao.INGREDIENT_TABLE);
ingPredicates.equalTo('recipe_id', id);
await store.delete(ingPredicates);
const recipePredicates = new relationalStore.RdbPredicates(RecipeDao.TABLE);
recipePredicates.equalTo('id', id);
await store.delete(recipePredicates);
}
与 26-29 完全同款：先删从表再删主表，杜绝孤儿食材。
4.6 种子数据
static async initSeedData(context: common.Context): Promise<void> {
// 10 道菜：家常菜 3 / 汤羹 1 / 主食 2 / 甜品 1 / 凉菜 2 / 早餐 1
// 难度覆盖 1~3，用时 10~90 分钟
// 刻意让"鸡蛋"出现在 3 道菜（反查演示：搜"鸡蛋"得 3 道）
}
种子设计的反查演示价值："鸡蛋"出现在西红柿炒鸡蛋、紫菜蛋花汤、蛋炒饭、芒果班戟（皮）、皮蛋瘦肉粥——搜"鸡蛋"能命中多道菜，反查功能一次可见。种子数据要为特色功能制造多命中场景。
五、技术要点对照表
| 技术点 | 实现方式 | 生产价值 |
|---|---|---|
| 主明细表 | recipe + recipe_ingredient | 结构性内容拆分 |
| 事务批量插入 | 主表 + 循环插明细 | 一道菜原子入库 |
| LIKE 反查 | 从表驱动 + JOIN 主表 | 方向反转查询 |
| 步骤 TEXT 存储 | \n 分隔 + 前端 split | 展示性内容不建表 |
| 主料约定 | 食材第一行 | 免 NLP 识别 |
| COUNT DISTINCT | 分类数去重 | 维度统计 |
| AVG + COALESCE | 平均用时兜底 | 空表安全 |
六、文章小结
本篇完成了菜谱的数据层：recipe 存菜谱档案（含步骤/难度/用时），recipe_ingredient 存食材明细（结构性内容），新增用事务保证"菜谱 + 食材"原子入库，反查用"从表 LIKE + JOIN 主表"实现方向反转，步骤用 TEXT 多行存储避免过度建表。这套模型的精髓是："主表 + 结构性明细"的主明细表范式——明细不是附带的日志，而是主表实体的必要组成。
下一篇《页面 UI 与操作实现》将搭建：分类 Tab、菜谱卡片（难度/用时/主料）、菜谱详情抽屉（食材清单 + 步骤 + 贴士）、新增菜谱表单（含食材多行输入）、按食材反查。
七、数据层深度扩展
1. 多对多正规化：菜谱-食材关联表
本实例的简化模型（菜谱 → 食材 1:N）在大规模场景有缺陷：
- 冗余：10 道菜都含"鸡蛋"，存 10 行"鸡蛋"；
- 反查不精确：
LIKE '%鸡蛋%'可能误匹配"鸡蛋糕"； - 无法维护食材元数据（热量/单价/季节性）。
正规化方案——三表多对多：
CREATE TABLE ingredient (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE, -- 食材主数据
calory REAL DEFAULT 0
);
CREATE TABLE recipe_ingredient (
recipe_id INTEGER NOT NULL,
ingredient_id INTEGER NOT NULL,
amount TEXT DEFAULT '',
PRIMARY KEY (recipe_id, ingredient_id) -- 复合主键
);
反查变为精确 JOIN：WHERE i.name = '鸡蛋'。何时升级：菜谱上百道、需要按食材精确统计时。家庭场景 1:N 够用。
2. LIKE 的索引与性能
LIKE '%鸡蛋%'（前导通配）无法用 B-tree 索引加速前缀匹配。优化手段：
| 场景 | 方案 |
|---|---|
| 精确匹配 | WHERE name = '鸡蛋'（走 idx_ing_name） |
| 前缀匹配 | WHERE name LIKE '鸡蛋%'（走索引） |
| 任意包含 | 全文索引 FTS5 或 保持全表扫描（数据量小可接受） |
家庭几十道菜，全表扫描毫秒级，无需优化。明确"规模"再决定优化策略。
3. SQL 注入防护（重点）
searchByIngredient 直接拼接 ingredient 是教学演示的反面教材。生产代码必须：
// 方案一：predicates（推荐）
const predicates = new relationalStore.RdbPredicates(RecipeDao.INGREDIENT_TABLE);
predicates.like('name', `%${ingredient}%`);
// JOIN 主表仍需 querySql，则用占位参数
const result = await store.querySql(
`SELECT ... WHERE i.name LIKE ? ORDER BY r.id`,
[`%${ingredient}%`]
);
relationalStore 的 querySql 支持 ? 占位参数数组。用户输入永远不进 SQL 字符串。
4. 步骤的富文本扩展
TEXT 步骤可扩展为"步骤表"以支持：每步配图、计时器、食材关联。触发升级的信号："每步一张图"需求出现。届时 steps 表：step_no / content / image_path / timer_seconds。延迟设计，需求驱动。
5. FAQ
Q1：为什么食材的 amount 用 TEXT 而不用数字？
A："2个"含单位、"适量"是模糊量——TEXT 原样存储表达力最强。若要计算（买菜的合计重量），需结构化（数值+单位），本实例无此需求。
Q2：一道菜可以属于多个分类吗？
A：本实例单分类（category 字段）。多分类需关联表（recipe_category）。家庭场景单分类够用。
Q3：难度 1/2/3 和 26-29 的状态枚举有何不同？
A：本质相同——数字枚举 + 页面映射文案。难度是"属性"（静态描述），状态是"流转"（动态变化）。数字枚举适用一切有限取值字段。
Q4：删除菜谱后反查结果会怎样？
A：先删食材再删菜谱，反查查不到该菜。LEFT JOIN 的兜底只防"菜谱先删"的异常顺序，正常删除流程下无孤儿。
Q5：能按"多个食材同时满足"反查吗（有鸡蛋 AND 有西红柿）？
A：可以，用 GROUP BY + HAVING：
SELECT r.name
FROM recipe_ingredient i JOIN recipe r ON i.recipe_id = r.id
WHERE i.name IN ('鸡蛋', '西红柿')
GROUP BY r.id
HAVING COUNT(DISTINCT i.name) = 2
"家里同时有 A 和 B"的精确匹配。本实例提供单食材 LIKE 反查，多条件组合留作练习。
八、下篇预告
下一篇《页面 UI 与操作实现》将完成：分类 Tab（横向滚动）、菜谱卡片（分类徽标/难度/用时/主料）、详情抽屉（食材清单 + 步骤 + 贴士）、新增表单（食材多行输入 + 难度选择）、按食材反查（输入框 + 结果文案）。敬请期待。
作为“人工智能6S店”的官方数字引擎，为AI开发者与企业提供一个覆盖软硬件全栈、一站式门户。
更多推荐
0
0- 0
已为社区贡献53条内容
所有评论(0)