处理程序中的MySQL回滚

处理程序中的MySQL回滚

本文介绍了处理程序中的MySQL回滚的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个存储过程,如果有任何问题,我想回滚.为了做到这一点,我使用了这样的退出处理程序:

I have a stored procedure where I'd like to rollback in case anything goes wrong. In order to do that I'm using a EXIT HANDLER like this:

DECLARE EXIT HANDLER FOR sqlexception
    begin
        ROLLBACK;
    end;

但是以这种方式,当我调用此存储过程时,如果发生任何错误,则该存储过程会成功,并且我不知道实际的问题是什么.我希望客户端(php)记录错误以便对其进行故障排除.所以我以这种方式修改:

But in this way, when I call this stored procedure, in case of any errors, the stored procedure succeed and I don't know what was the actual problem. I want the client (php) to log the error in order to troubleshoot it. So I modify in this way:

DECLARE EXIT HANDLER FOR sqlexception
    begin
        get diagnostics condition 1
              @p1 = MESSAGE_TEXT;
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @p1;
    end;

因此,现在存储过程回滚,然后抛出处理程序拦截的异常.太酷了,但有时MESSAGE_TEXT超过128个字符,在这种情况下,我得到:

So now the stored procedure rollback and than throw the exeption that the handler intercepted. That's cool but sometimes the MESSAGE_TEXT is more than 128 chars and in such cases I get:

当然不能接受这种解决方案:

Of course this solution is not acceptable:

DECLARE EXIT HANDLER FOR sqlexception
    begin
        get diagnostics condition 1
              @p1 = MESSAGE_TEXT;
        ROLLBACK;
        SET @p1=SUBSTRING(@p1,1,128);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @p1;
    end;

是否有任何方法可以拦截任何异常,回滚然后向客户端抛出相同的异常?非常感谢您的帮助

Is there any way to intercept any exception, rollback and then throw the same exception to the client?Thanks very much for your help

推荐答案

肯尼(Kenney)建议,答案是:

As suggested by Kenney the answer is:

DECLARE EXIT HANDLER FOR sqlexception
    begin
        ROLLBACK;
        RESIGNAL;
    end;

这篇关于处理程序中的MySQL回滚的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 23:33