MySQL存储过程使用事务控制批量插入实例

实现目标

创建一个存储过程，接收参数指定插入数量。在执行多条INSERT语句的过程中，如果任何一步发生SQL异常，整个批次将回滚；若全部完成且无异常，则提交事务。

准备条件

• MySQL数据库环境（建议使用InnoDB引擎以支持事务）
• 具备相应权限的用户账号（通常root或应用账号）
• 已建好的目标表（示例中使用“表名称”占位，需替换为实际表名）

真实操作顺序

第1步：编写带事务控制的存储过程

进入数据库管理界面（如MySQL Workbench、Navicat或命令行），新建存储过程。以下是核心逻辑代码，需注意关键点：

CREATE DEFINER=`root`@`localhost` PROCEDURE `createBusiness`(parameter1 int)
BEGIN
    # 1. 声明变量
    DECLARE flag INT DEFAULT parameter1;
    DECLARE uuidStr VARCHAR(32);
    DECLARE currentTime TIMESTAMP;
    
    # 声明一个整形变量err，默认值是0
    DECLARE err INT DEFAULT 0;
    
    # 当sqlexception handler捕捉到异常时，设置err=1
    DECLARE continue HANDLER FOR sqlexception SET err=1;

    # 2. 开始事务
    START TRANSACTION;

    # 3. 循环执行插入
    WHILE flag > 0 DO 
        # 生成32位UUID字符串
        SET uuidStr = REPLACE(UUID(), '-', '');
        # 获取当前时间戳
        SET currentTime = CURRENT_TIMESTAMP();
        
        # 执行插入语句
        INSERT INTO  
            (id, title, keyword, hasImage, isTodayHead, isShowInHome, isBigness, publishTime, originId, modify_time, isAnalysis)
        VALUES
            (uuidStr, CONCAT('事件标题', flag), CONCAT('关键字', flag), 1, 1, 0, 0, currentTime, CONCAT('xxxxxxx', flag), currentTime, 1);
            
        # 注意：MySQL中没有flag--语法，需手动递减
        SET flag = flag - 1;
    END WHILE;

    # 4. 判断结果并提交或回滚
    IF (err = 0) THEN
        COMMIT;
        SELECT 'OK';
    ELSE
        ROLLBACK;
        SELECT 'err';
    END IF;

END;

关键说明：

1. DECLARE ... HANDLER：continue表示即使发生异常也继续执行后续代码，但会将标志位err设为1，这是判断是否需要回滚的关键。
2. START TRANSACTION：明确开启事务块。
3. IF (err = 0) 判断：只有当所有步骤均未触发异常处理器时，才执行COMMIT；否则执行ROLLBACK恢复原状。

第2步：调用存储过程测试

在SQL编辑器中执行以下命令调用上述过程，例如传入参数5，尝试插入5条记录：

CALL createBusiness(5);

若控制台输出'OK'，说明事务提交成功；若输出'err'，说明过程中发生错误并已自动回滚。

完成判断

检查目标表中是否准确插入了指定数量的数据，且无脏数据。可通过查询最后几条记录的时间戳和ID验证。CALL createBusiness(n)后返回SELECT 'OK'即视为正常完成。

原文有依据的排错

• WHILE空实现错误：原文注释提到“while不能空实现”，若在循环体内未包含任何语句会导致语法错误。本例中至少包含SET和INSERT语句，符合规范。
• 变量赋值符号：MySQL存储过程中给变量赋值必须使用SET var = value，不可直接用=进行比较赋值混合使用（如flag=7 then写法是错误的比较而非赋值），本例严格遵循SET语法。
• 缺少自增/主键冲突：若表中id字段非唯一或存在重复值限制，可能导致插入失败触发sqlexception。确保插入逻辑生成的uuidStr全局唯一或id字段允许重复。

以上就是在Mysql存储过程中使用事务实例的详细内容，更多关于MySQL事务处理与存储过程优化的资料请关注本站其它相关文章！