如何检测MySQL存储过程的回滚?
我正在尝试找出一种方法来检测 MySQL 存储过程中是否发生回滚,以便我可以通过 PHP 脚本相应地处理这种情况,但到目前为止我找不到任何解决方案。
我的存储过程如下所示:
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 ;
我对此过程进行了回滚测试,它确实回滚了,但我没有得到错误。 我希望我的存储过程在事务失败时抛出某种错误消息,所以我可以像这样处理它:
$result = mysql_query($procedure);
if(!$result)
{
//rollback occured do something
}
Is there a way to detector rollback in MySQL? 我错过了什么吗? 任何答复将不胜感激。 感谢您的阅读。
感谢您的建议,我解决了这个问题。这是我所做的:
存储过程
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,则会出现以下错误:
OUT 或 INOUT 参数不是 变量或 NEW 伪变量 触发之前
我不知道我做错了什么,但我无法解决这个问题。
PHP 脚本
$result=mysqli_query($con,$procedure);
if(is_object($result))
{
//rollback happened do something!
}
如果 SP 成功,则会抛出 true。
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.
My stored procedure looks like this:
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
}
Is there a way to detect rollback in MySQL?
Am I missing something?
Any reply will be appreciated.
Thanks for reading.
Thanks to your advices I fixed this problem. Here's what I did:
Stored Procedure
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 ;
If I use out variable instead of select -1, it gives me this error:
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 script
$result=mysqli_query($con,$procedure);
if(is_object($result))
{
//rollback happened do something!
}
If the SP is successful it throws true.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
您可以添加输出参数,然后将其设置为退出处理程序中所需的值。
这是使用您的过程的示例:
You can add an output param and then set it to the value you want in your exit handlers.
Here's an example using your proc:
如果您想捕获错误,通常会在 PHP 端执行此操作。阅读 http://php.net/manual/en/pdo.transactions.php 了解更多信息。
You would usually do this PHP-side if you wanted to catch errors. Read http://php.net/manual/en/pdo.transactions.php for more information.
嘿,做一件事,使用 OUTPUT 变量并返回 1 或 0 作为 SP 的结果,并在此标志上执行您想要的操作。
Hey do one thing, use OUTPUT variable and return 1 or 0 as result form SP and do what ever you want on this flag.