更新失败时的MySQL事务回滚 [英] Mysql transaction rollback on failure in update
本文介绍了更新失败时的MySQL事务回滚的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
通过一个简单的交易
START TRANSACTION;
UPDATE posts SET status='approved' where post_id='id' AND status != 'approved';
.. other queries ...
COMMIT;
我只想在更改状态时执行一次交易;但是当没有任何行更新时,上面的UPDATE
不会给回滚事务带来错误.
I want to perform the transaction only once when changing the status; but the above UPDATE
will not give an error to rollback the transaction when no row is updated.
如何限制事务仅在更新行后才提交(我的意思是状态已更改).
How can I limit the transaction to commit only if the row is updated (I mean the status is changed).
推荐答案
在PHP中(未经测试,需要适应您的情况):
Here is in PHP (haven't tested, needs adapting to your situation):
mysql_query('START TRANSACTION;')
mysql_query("UPDATE posts SET status='approved' where post_id='id' AND status != 'approved';");
if (mysql_affected_rows()){
mysql_query('COMMIT');
} else {
mysql_query('ROLLBACK');
}
或者,如果您想变得聪明一点,并在SQL中使用它(使用 IF
):
Or, If you want to be clever and do it in SQL (using ROW_COUNT()
and IF
):
START TRANSACTION;
UPDATE posts SET status='approved' where post_id='id' AND status != 'approved';
SELECT ROW_COUNT() INTO @affected_rows;
-- .. other queries ...
IF (affected_rows > 0) THEN
COMMIT;
ELSE
ROLLBACK;
END IF
这篇关于更新失败时的MySQL事务回滚的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文