如何确保归档存储过程执行正常且插入失败时不删除数据？

这问题本质上是要保证插入归档表和删除原表数据这两个操作的原子性——要么都成功，要么都失败，绝对不能出现“插入失败但原表数据被删了”的情况。咱们可以通过MySQL的事务机制加上异常处理来实现这个需求，具体改法如下：

修改后的存储过程代码 #

CREATE DEFINER=`Mybase`@`%` PROCEDURE `archive`() 
BEGIN
    -- 声明异常处理：一旦出现SQL错误，立即回滚事务并退出存储过程
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        -- 可选：可以在这里添加错误日志或者抛出自定义错误信息
        -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '归档插入失败，已回滚事务';
    END;

    -- 开启事务，将后续操作绑定为一个原子单元
    START TRANSACTION;

    -- 执行归档插入操作
    INSERT `data_archive` SELECT * FROM `data`;

    -- 只有插入成功才会执行到这一步：删除原表数据
    DELETE FROM `data`;

    -- 提交事务，确认所有操作生效
    COMMIT;
END

关键逻辑说明 #

• 事务包裹：把INSERT和DELETE放到同一个事务里，确保这两个操作是不可分割的整体——要么一起生效，要么一起回滚。
• 异常拦截：通过DECLARE EXIT HANDLER FOR SQLEXCEPTION捕获所有SQL执行错误（比如主键冲突、字段类型不匹配、磁盘空间不足等导致插入失败的场景），一旦触发异常就会执行ROLLBACK回滚事务，并且直接退出存储过程，这样后续的DELETE和COMMIT都不会执行，彻底避免了“删了原表但归档失败”的风险。
• 正常流程：如果插入操作完全成功，才会继续执行删除，最后提交事务，完成整个归档流程。

额外注意事项 #

• 确保data和data_archive的表结构完全一致，否则INSERT ... SELECT *会因为字段不匹配直接触发异常，事务自动回滚。
• 检查Mybase用户是否拥有事务操作的权限（一般来说只要能操作这两张表就没问题）。
• 如果需要更细致的错误处理，可以把SQLEXCEPTION换成具体的错误码（比如1062对应主键冲突），或者在异常处理块里添加日志记录，方便后续排查问题。

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