MySQL存储过程异常自动回滚的实现方法

在MySQL存储过程中处理异常，特别是实现错误自动回滚事务，是数据库管理员和开发人员必须掌握的关键技能。然而，其中几个核心细节若理解不透彻，极易引发问题。本文将深入剖析几个常见的误区。

DECLARE EXIT HANDLER 需与 START TRANSACTION 配合生效

首先需要明确一个核心概念：DECLARE EXIT HANDLER FOR SQLEXCEPTION 声明并非全局生效。它仅在当前事务的上下文中起作用。这意味着，如果没有使用 START TRANSACTION 或 BEGIN 显式开启事务，该异常处理器将基本失效。错误发生时，MySQL不会触发它，程序流程会继续执行，甚至可能提交之前已执行成功的语句。

一个典型的错误场景是：存储过程先执行一条UPDATE语句，随后执行INSERT时触发主键冲突（ERROR 1062）。由于未开启事务，异常处理器未生效，过程会继续执行到最后的COMMIT，导致UPDATE生效而数据整体状态不一致。

• 声明顺序至关重要：START TRANSACTION 必须置于 DECLARE EXIT HANDLER 声明之后、业务SQL语句之前。顺序颠倒将导致处理器无法监听到后续语句抛出的错误。
• 避免依赖隐式事务：在默认autocommit=1的设置下，每条SQL本身就是一个独立事务，此时在存储过程中执行ROLLBACK是无效的。
• 警惕隐式提交语句：如果过程中混用了DROP TABLE、CREATE TABLE等会触发隐式提交的DDL语句，事务将在该语句执行时自动提交。此后的ROLLBACK只能回滚该DDL之后的操作。

使用 RESIGNAL 而非 SIGNAL 或静默处理错误

仅在异常处理器中执行ROLLBACK通常是不够的。这虽然能回滚事务，但调用方（如Java程序或PHP脚本）可能无法收到任何错误信息，导致日志仅显示“存储过程执行成功”，为问题排查带来困难。

最规范的做法是使用RESIGNAL。它的作用是在回滚事务后，将捕获到的原始错误信息完整地再次抛出。SQLSTATE、MySQL错误码及错误文本均得以保留，便于调用方准确定位问题。

需要注意一个常见误区：避免手动构造错误。例如使用SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ...抛出自定义错误。一方面，MESSAGE_TEXT超过128字符会直接报错（Error Code: 1648）；另一方面，原始的、更精确的错误码会丢失，不利于问题定位。

• 优先使用 RESIGNAL：MySQL 5.5及以上版本原生支持，无需对错误信息进行截断或重新拼接。
• 诊断信息易丢失：避免在处理器中先执行GET DIAGNOSTICS将错误信息存入变量，随后插入其他语句（即使是SET），最后再尝试使用RESIGNAL。因为任何非GET DIAGNOSTICS的语句都会清空之前获取的诊断信息。
• 记录日志需注意时机：如果确有记录错误日志的需求，必须在GET DIAGNOSTICS之后，立即将信息写入一个已存在的、稳定的日志表。且该插入操作本身不应包含COMMIT或ROLLBACK，以免影响主事务。

避免使用 CONTINUE HANDLER 处理事务异常

这是另一个容易陷入的误区。CONTINUE HANDLER的语义是“出错后，继续执行下一条语句”。这在事务场景下具有高风险。

考虑一个转账过程：先扣减A账户余额（UPDATE成功），再增加B账户余额（UPDATE时发生错误）。如果使用CONTINUE HANDLER FOR SQLEXCEPTION，第二个UPDATE失败后，控制流会继续执行后续的COMMIT语句。结果导致A账户扣款成功，但B账户未收到款项，造成资金不一致。

CONTINUE HANDLER并非完全无用。它适用于一些非强一致性的批量操作，例如批量插入数据时希望跳过某条重复记录并继续处理后续数据，且整个过程不涉及资金或核心状态变更。

• 事务内统一使用 EXIT HANDLER：凡是涉及事务完整性、数据一致性的操作，在事务体内应一律使用DECLARE EXIT HANDLER FOR SQLEXCEPTION。
• 注意其他异常类型：SQLEXCEPTION主要捕获如主键冲突、外键约束违反等“错误”。游标遍历结束属于NOT FOUND条件，而一些警告信息属于SQLWARNING，它们不会触发SQLEXCEPTION处理器，需要单独声明。
• 主键冲突属于 SQLEXCEPTION：像ERROR 1062 (23000): Duplicate entry这类典型错误，本身即属于SQLEXCEPTION范畴，无需额外指定SQLSTATE。

应用层事务中存储过程无法替代 COMMIT/ROLLBACK 决策

这个场景稍复杂。当在应用层（例如使用PHP的mysqli或PDO）通过begin_transaction()开启事务后，再调用CALL执行存储过程，此时事务的生命周期由客户端连接控制。

在这种情况下，若在存储过程内部再次编写START TRANSACTION，MySQL会报错（ERROR 1305）。而如果在过程内部编写COMMIT或ROLLBACK，它们通常会被忽略——MySQL会将其视为嵌套事务的尝试，但实际只响应最外层应用连接发出的提交或回滚指令。

因此，存在一个清晰的边界：要么让存储过程自身管理完整的事务（适用于纯数据库层的独立逻辑单元）；要么由应用层统一开启事务、调用过程，并根据过程执行结果（或捕获的异常）来决定提交或回滚（这种模式更常见，控制力也更强）。

• 混合模式需设置 autocommit：如果采用应用层控制事务的模式，务必确保连接的事务模式正确，通常需要在连接后执行SET autocommit = 0。
• 避免半手动流程：在过程中使用SELECT ... INTO将结果存入变量，再通过IF判断来决定是否COMMIT，这种模式容易遗漏错误分支或误判成功条件，导致应回滚的操作未回滚。
• 难点在于错误边界：有些操作，如INSERT ... ON DUPLICATE KEY UPDATE，执行成功但可能未更新任何行（触发了UPDATE但值未变）。这不会抛出SQLEXCEPTION。对于此类场景，需要依赖ROW_COUNT()函数判断实际影响的行数，从而决定业务逻辑上的成功与否，这比简单的异常捕获更为细致。