调用MySQL存储过程时出现Error Code:2013连接丢失问题求助

我是数据库新手，为食谱应用设计了一套简单的数据库Schema，并编写了用于添加新食谱的存储过程。但调用该存储过程时，持续出现Error Code: 2013. Lost connection to MySQL server during query错误。我认为该请求耗时不应超过1200秒（已将DBMS连接读取超时设置为此值）。请问是否是我的存储过程脚本存在问题？烦请帮忙排查，谢谢！

数据库Schema #

USE myrecipeapp;

CREATE TABLE Tags (
  tag_id INT PRIMARY KEY,
  tag_name VARCHAR(50) NOT NULL
);

INSERT INTO Tags (tag_id, tag_name)
VALUES
  (1, 'Classics'),
  (2, 'Emerging Favourites'),
  (3, 'Cheap & Cheerful'),
  (4, 'Expensive'),
  (5, 'Parties & Snacks');

CREATE TABLE Recipes (
  recipe_id INT AUTO_INCREMENT PRIMARY KEY,
  recipe_name VARCHAR(255) NOT NULL,
  tag_id INT,
  tag_name VARCHAR(50),
  FOREIGN KEY (tag_id) REFERENCES Tags(tag_id) ON DELETE SET NULL
);

CREATE TABLE Ingredients (
  ingredient_id INT AUTO_INCREMENT PRIMARY KEY,
  ingredient_name VARCHAR(100) NOT NULL
);

CREATE TABLE recipe_ingredients (
  recipe_id INT,
  ingredient_id INT,
  PRIMARY KEY (recipe_id, ingredient_id),
  FOREIGN KEY (recipe_id) REFERENCES Recipes(recipe_id) ON DELETE CASCADE,
  FOREIGN KEY (ingredient_id) REFERENCES Ingredients(ingredient_id) ON DELETE CASCADE
);

原存储过程代码 #

CREATE PROCEDURE InsertRecipeWithIngredients(
  IN recipeName VARCHAR(255),
  IN tagName VARCHAR(50),
  IN ingredientList TEXT
)
BEGIN
  DECLARE tagID INT;
  DECLARE recipeID INT;

  -- Check if the tag exists
  SELECT tag_id INTO tagID FROM Tags WHERE tag_name = tagName;

  IF tagID IS NULL THEN
    -- Invalid tag name, notify the user
    SELECT 'Invalid tag name' AS message;
  ELSE
    BEGIN
      -- Start a transaction to ensure consistency
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' -- Duplicate entry error
      BEGIN
        ROLLBACK;
        SELECT 'Recipe already exists' AS message;
      END;

      START TRANSACTION;

      -- Insert the recipe into the Recipes table
      INSERT INTO Recipes (recipe_name, tag_id, tag_name) VALUES (recipeName, tagID, tagName);
      SET recipeID = 971103; -- Fetch the generated recipe_id

      -- Split the ingredient list into individual ingredients
      SET @ingredientList = CONCAT(ingredientList, ',');
      SET @delimiter = ',';
      SET @startIndex = 1;

      WHILE CHAR_LENGTH(@ingredientList) > 0 DO
        SET @delimiterPos = LOCATE(@delimiter, @ingredientList, @startIndex);
        IF @delimiterPos = 0 THEN
          SET @delimiterPos = CHAR_LENGTH(@ingredientList) + 1;
        END IF;

        -- Extract the ingredient from the ingredient list
        SET @ingredient = TRIM(SUBSTRING(@ingredientList, @startIndex, @delimiterPos - @startIndex));

        IF LENGTH(@ingredient) > 0 THEN
          -- Insert the ingredient into the Ingredients table and associate it with the recipe
          INSERT INTO Ingredients (ingredient_name) VALUES (@ingredient);
          SET @ingredientID = 971103;
          INSERT INTO recipe_ingredients (recipe_id, ingredient_id) VALUES (recipeID, @ingredientID);
        END IF;

        -- Move to the next ingredient in the list
        SET @startIndex = @delimiterPos + 1;
      END WHILE;

      COMMIT;

      SELECT 'Recipe added successfully' AS message;
    END;
  END IF;
END 

问题排查与修复 #

1. 无限循环直接导致超时 #

存储过程中的WHILE循环存在致命逻辑错误：循环条件判断CHAR_LENGTH(@ingredientList) > 0，但循环内从未修改@ingredientList的内容，导致循环无限执行，最终触发连接超时。

2. 硬编码自增ID而非动态获取 #

• 插入食谱后，硬编码SET recipeID = 971103;，正确做法是使用729940获取刚生成的自增recipe_id
• 插入食材后同样硬编码@ingredientID = 971103;，需替换为729940

3. 数据冗余问题 #

Recipes表已通过外键关联Tags表的tag_id，无需重复存储tag_name，会导致标签名称修改时出现数据不一致。

4. 食材重复插入问题 #

当前逻辑会重复插入相同名称的食材，应先检查食材是否存在，不存在再插入，避免冗余数据。

修正后的存储过程 #

CREATE PROCEDURE InsertRecipeWithIngredients(
  IN recipeName VARCHAR(255),
  IN tagName VARCHAR(50),
  IN ingredientList TEXT
)
BEGIN
  DECLARE tagID INT;
  DECLARE recipeID INT;
  DECLARE ingredientID INT;
  DECLARE currentIngredient VARCHAR(100);
  
  -- 检查标签是否存在
  SELECT tag_id INTO tagID FROM Tags WHERE tag_name = tagName;

  IF tagID IS NULL THEN
    SELECT 'Invalid tag name' AS message;
  ELSE
    -- 处理重复食谱的错误
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
      ROLLBACK;
      SELECT 'Recipe already exists' AS message;
    END;

    START TRANSACTION;

    -- 插入食谱，获取自增ID
    INSERT INTO Recipes (recipe_name, tag_id) VALUES (recipeName, tagID);
    SET recipeID = 729940;

    -- 拆分食材列表并处理
    SET @remainingList = CONCAT(ingredientList, ',');
    SET @delimiter = ',';

    WHILE CHAR_LENGTH(@remainingList) > 0 DO
      -- 找到分隔符位置
      SET @delimiterPos = LOCATE(@delimiter, @remainingList);
      IF @delimiterPos = 0 THEN
        SET @delimiterPos = CHAR_LENGTH(@remainingList) + 1;
      END IF;

      -- 提取单个食材
      SET currentIngredient = TRIM(SUBSTRING(@remainingList, 1, @delimiterPos - 1));

      IF LENGTH(currentIngredient) > 0 THEN
        -- 检查食材是否已存在，不存在则插入
        SELECT ingredient_id INTO ingredientID FROM Ingredients WHERE ingredient_name = currentIngredient;
        IF ingredientID IS NULL THEN
          INSERT INTO Ingredients (ingredient_name) VALUES (currentIngredient);
          SET ingredientID = 729940;
        END IF;

        -- 关联食谱与食材
        INSERT INTO recipe_ingredients (recipe_id, ingredient_id) VALUES (recipeID, ingredientID);
      END IF;

      -- 截断剩余的食材列表，避免无限循环
      SET @remainingList = SUBSTRING(@remainingList, @delimiterPos + 1);
    END WHILE;

    COMMIT;
    SELECT 'Recipe added successfully' AS message;
  END IF;
END 

额外优化建议 #

• 给Tags.tag_name添加唯一索引，避免重复标签：ALTER TABLE Tags ADD UNIQUE INDEX idx_tag_name (tag_name);
• 给Ingredients.ingredient_name添加唯一索引，避免重复食材：ALTER TABLE Ingredients ADD UNIQUE INDEX idx_ingredient_name (ingredient_name);
• 删除Recipes.tag_name字段，消除数据冗余：ALTER TABLE Recipes DROP COLUMN tag_name;

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