MySQL回滚事务 [英] MySQL Rollback in transaction

问题描述

我已经定义了一个这样的函数 -

I have defined a function like this -

...
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; 
...

我收到以下错误 -

I am getting the following error --

  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

编辑

我更新了我的proc我还在收到错误..

I updated my proc I am still getting the error ..

 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; 

推荐答案

在处理程序中使用复合语句时，需要嵌入他们在一个BEGIN ... END块：

When using compound statements in a handler you need to embed them in a BEGIN ... END block:

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

请参阅 DECLARE HANDLER 部分的MySQL手册了解更多信息。

Refer to the DECLARE HANDLER section of the MySQL manual for more information.

这篇关于MySQL回滚事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！