问题描述
我正在尝试找出一种检测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:
我不知道我做错了什么,但我无法解决此问题.
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存储过程中检测回滚?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!