如何在MySQL存储过程中检测回滚? [英] How to detect a rollback in MySQL stored procedure?

问题描述

我正在尝试找出一种检测MySQL存储过程中回滚的方法，以便可以从PHP脚本中相应地处理这种情况，但到目前为止我找不到任何解决方案.

I'm trying to figure out a way to detect an occurrence of rollback in a MySQL stored procedure so I could handle the situation accordingly from a PHP script, but so far I can not find any solution.

我的存储过程如下:

     delimiter |
      create procedure multi_inserts(
      IN var1 int(11),
           .
           .
           .
      IN string1 text
      )
      BEGIN

      declare exit handler for sqlexception rollback;
      declare exit handler for sqlwarning rollback;

      START TRANSACTION;
      insert into table1(a,b,c,d) values(var1,var2,var3,var4);
      insert into table2(e,f,g) values(var5,var6,string1);
      COMMIT;

      END
      delimiter ;

我对此程序进行了回滚测试，但确实进行了回滚，但没有错误. 我希望存储过程在事务失败时抛出某种错误消息，因此我可以这样处理:

I did a rollback test on this procedure and it did rollback but I got no false. I want my stored procedure to throw some kind of error message if the transaction failed, so I could handle it like this:

    $result = mysql_query($procedure); 
    if(!$result) 
    {
      //rollback occured do something   
    }

有没有一种方法可以检测MySQL中的回滚? 我想念什么吗? 任何答复将不胜感激. 感谢您的阅读.

Is there a way to detect rollback in MySQL? Am I missing something? Any reply will be appreciated. Thanks for reading.

感谢您的建议，我已解决此问题.这是我所做的:

存储过程

     delimiter |
      create procedure multi_inserts(
      IN var1 int(11),
           .
           .
           .
      IN string1 text
      )
      BEGIN

      declare exit handler for sqlexception sqlwarning
      BEGIN
      rollback;
      select -1;
      END;

      START TRANSACTION;
      insert into table1(a,b,c,d) values(var1,var2,var3,var4);
      insert into table2(e,f,g) values(var5,var6,string1);
      COMMIT;

      END
      delimiter ;

如果我使用out变量而不是select -1，则会出现此错误:

If I use out variable instead of select -1, it gives me this error:

OUT或INOUT参数不是 变量或NEW伪变量 触发之前

OUT or INOUT argument is not a variable or NEW pseudo-variable in BEFORE trigger

我不知道我做错了什么，但我无法解决此问题.

I don't know what did I wrong, but I couldn't fix this problem.

PHP脚本

$result=mysqli_query($con,$procedure);
if(is_object($result))
{
//rollback happened do something!
}

如果SP成功，则抛出true.

If the SP is successful it throws true.

推荐答案

您可以添加输出参数，然后将其设置为退出处理程序中所需的值.

You can add an output param and then set it to the value you want in your exit handlers.

以下是使用您的proc的示例:

Here's an example using your proc:

delimiter $$
  create procedure multi_inserts(
  IN var1 int(11),
       .
       .
       .
  IN string1 text,
  OUT p_return_code tinyint unsigned
  )
  BEGIN

  DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
    set p_return_code = 1;
    rollback;
  END;

  DECLARE exit handler for sqlwarning
  BEGIN
    -- WARNING
    set p_return_code = 2;
    rollback;
  END;

  START TRANSACTION;
  insert into table1(a,b,c,d) values(var1,var2,var3,var4);
  insert into table2(e,f,g) values(var5,var6,string1);
  COMMIT;

  -- SUCCESS
  set p_return_code = 0;

  END $$
  delimiter ;

这篇关于如何在MySQL存储过程中检测回滚?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！