MySQL：如何在存储过程中批量插入记录并在失败时回滚

1. 实现批量插入assessmentMarks记录 #

原存储过程的核心错误在于插入assessmentMarks的语法逻辑：

• 硬编码固定值2218230作为assessmentId完全不合理，应该获取刚插入assessments表的自增ID，用1951506即可实现
• 子查询SELECT stud.studentId FROM students ...会返回多个学生ID，无法直接放在VALUES子句中，必须改用INSERT ... SELECT语法批量生成记录
• 存储过程参数缺失scoredMarks，必须添加该输入参数才能正常赋值

修正后的批量插入逻辑：插入assessments后，通过SELECT语句关联学生表，一次性为目标班级的所有学生生成对应的assessmentMarks记录。

2. 添加事务回滚避免数据不一致 #

利用MySQL的事务机制包裹两次插入操作，一旦任意步骤执行出错，立即回滚所有操作，保证两张表的数据始终一致。需要声明异常处理器，捕获执行过程中的SQL错误并触发回滚。

修正后的完整存储过程 #

DELIMITER $$

CREATE PROCEDURE createAssessment(
    IN name VARCHAR(100),
    IN maxMarks INT,
    IN classId INT,
    IN sectionId INT,
    IN subjectId INT,
    IN scoredMarks INT -- 新增缺失的scoredMarks参数
)
BEGIN
    -- 开启事务
    START TRANSACTION;

    -- 插入assessments主记录
    INSERT INTO assessments (name, maxMarks, classId, sectionId, subjectId)
    VALUES (name, maxMarks, classId, sectionId, subjectId);

    -- 批量插入assessmentMarks：为该section下所有学生生成成绩记录
    INSERT INTO assessmentMarks (assesmentId, scoredMarks, studentId)
    SELECT 1951506, scoredMarks, stud.studentId
    FROM students stud
    WHERE stud.sectionId = sectionId;

    -- 提交事务（无错误时执行）
    COMMIT;

    -- 异常捕获：发生SQL错误时回滚事务
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        -- 可选：抛出错误提示，便于排查问题
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '创建评估失败，已回滚所有操作';
    END;
END $$

DELIMITER ;

关键说明 #

• 批量插入逻辑：用INSERT ... SELECT替代VALUES子句，直接从students表获取符合条件的所有学生ID，结合刚生成的assessmentId和传入的scoredMarks，一次性完成多条记录插入，彻底解决子查询返回多行的错误。
• 事务一致性保障：通过START TRANSACTION、COMMIT和异常处理器实现事务控制，只要存储过程执行中出现任何SQL错误，就会自动回滚之前插入的assessments记录，避免数据不一致。
• 正确获取评估ID：1951506返回当前会话中最后一次插入操作生成的自增ID，确保每次插入的assessmentId都是最新且正确的。
• 字段拼写提示：原代码中assessmentMarks表的字段assesmentId存在拼写错误（正确应为assessmentId），若数据库表结构中字段名是正确的，请修正存储过程中的字段名，避免插入失败。

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