MySQL中获取食材表ID插入关联表的实现问题求助

1. 先给食材表添加唯一约束 #

要实现「已存在则不插入」的核心逻辑，必须先给ingredients表的ingredient字段添加唯一索引，否则无法判断食材是否重复：

ALTER TABLE ingredients ADD UNIQUE INDEX idx_ingredient (ingredient);

2. 重构SQL逻辑 #

你原来的SQL结构过于复杂，我们可以简化为：对每个食材，先确保它存在于ingredients表（不存在则插入），再获取其ID插入到recipeIngredients表。

高效的单食材处理SQL #

利用INSERT ... ON DUPLICATE KEY UPDATE结合824220，可以在一次SQL序列中完成「确保食材存在+获取ID+插入关联表」的操作：

-- 确保食材存在，若已存在则将其ID赋值给824220
INSERT INTO ingredients (ingredient) VALUES (LOWER(?))
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);

-- 插入到关联表，直接使用刚才获取的食材ID
INSERT INTO recipeIngredients (recipe_id, amount, unit_id, ingredient_id)
VALUES (?, ?, ?, 824220);

3. 修正JS代码逻辑 #

你的createRecipeIngredient函数存在参数不匹配、逻辑混乱的问题，以下是重构后的完整代码：

recipes.js 重构后的createRecipeIngredient #

async function createRecipeIngredient(newRecipeId, ingredients) {
  // 批量处理所有食材，用Promise.all提升效率
  const processIngredient = async (item) => {
    const { ingredient, unitId, amount } = item;
    const lowerIngredient = ingredient.toLowerCase();

    // 第一步：确保食材存在并获取ID
    await db.promise().query(`
      INSERT INTO ingredients (ingredient) VALUES (?)
      ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
    `, [lowerIngredient]);

    // 第二步：插入到recipeIngredients表
    await db.promise().query(`
      INSERT INTO recipeIngredients (recipe_id, amount, unit_id, ingredient_id)
      VALUES (?, ?, ?, 824220);
    `, [newRecipeId, amount, unitId]);
  };

  await Promise.all(ingredients.map(processIngredient));
}

module.exports = { getRecipe, getRecipeComments, getRecipePhotos, getUserRecipeCommentLikes, createRecipe, insertRecipePhoto, createRecipeIngredient };

routerRecipes.js 修正调用逻辑 #

你之前调用createRecipeIngredient时仅传入newRecipeId，需要补充传入食材数组：

router.post('/recipes/new', cloudinary.upload.single('photo'), async (req, res, _next) => {
  // 假设createRecipe返回新生成的recipe_id，需根据实际逻辑调整
  const newRecipeId = await recipeQueries.createRecipe();
  await recipeQueries.insertRecipePhoto(newRecipeId, req.user, req.file.path);
  // 传入从请求体获取的食材数组
  await recipeQueries.createRecipeIngredient(newRecipeId, req.body.ingredients);

  res.redirect('/recipes');
});

关键说明 #

• 唯一索引是核心：没有ingredients.ingredient的唯一约束，ON DUPLICATE KEY逻辑无法生效，会导致重复插入食材。
• 统一小写存储：将食材名称转为小写后存储，避免大小写差异导致的重复（比如"Sugar"和"sugar"视为同一食材）。
• 批量处理：用Promise.all并行处理多个食材，比逐个串行处理效率更高。

内容的提问来源于stack exchange，提问作者Bryce Richey