更新失败时的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事务回滚

相关推荐