事务中的 MySQL 回滚

Question

我定义了这样一个函数-

...
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; 
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK SET error_key = 1 AND error_message = 'Failed -- Rollback.';
BEGIN
     SELECT 'Insert Failed.';
     CALL log_error(error_key,  error_message);
 END ; 

START TRANSACTION;

     DECLARE EXIT HANDLER FOR 1062 SET error_key = 02 AND error_message = 'Insert Failed';
     BEGIN
        SELECT 'Attempt to create a duplicate entry in the follow table.';
        CALL log_error(error_key,  error_message);
      END; 
      INSERT INTO `user_table` (...) VALUES (...);

      /* Call the trigger to update the profile table */
      CALL updateAnotherTable(@result1);
      CALL updateAnotherTable1(@result2);

IF @retsult1=0 OR @retsult2=0 THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF; 
...

我收到以下错误 --

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; DECLARE EXIT HANDLER FOR SQL

编辑

我更新了我的程序我仍然收到错误..

 DECLARE exit handler for sqlexception sqlwarning 
 BEGIN        
       SET error_key = 901;
       SET error_message = 'Insert Failed.';
       CALL log_error(error_key,  error_message);
       ROLLBACK;
 END; 

Answer 1

在处理程序中使用复合语句时，您需要将它们嵌入到 BEGIN ... END block 中:

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SET error_key = 02;
    SET error_message = 'Insert Failed';
END

引用DECLARE HANDLER MySQL 手册部分以获取更多信息。