MySQL 记录更新应该失败，但不会.为什么? [英] MySQL record UPDATE should fail but does not. Why?

问题描述

这里有一个有趣的情况.

here is an interesting situation.

我开始与 MySQL 进行交易.我的交易涉及 3 个相关查询.每个查询都必须成功，否则不应该写入数据库.

I start a transaction with MySQL. My transaction involves 3 related queries. Each query must succeed, and if not then none should be written to the database.

现在......故意，对于第二个查询......恰好是一个更新查询......我改变了标识要更新为无效(不存在)PK 值的记录的 pk 值.我希望第二个查询失败以进行测试.查询很好，只是 c_id 值错误(我试图更新的记录没有退出).

Now... on purpose, for the 2nd query...which happens to be an UPDATE query... I changed the pk value identifying the record to be updated to an invalid (non-existing) PK value. I wanted the 2nd query to fail for testing purposes. The query is fine, it is just that the c_id value is wrong (the record I'm trying to UPDATE does not exits).

问题是查询是在OK"的情况下执行的...

The problem is that the query is executed with an "OK"...

mysql> UPDATE tableX SET bal = 4576.99 WHERE c_id = 3789;  
Query OK, 0 rows affected (0.00 sec)  
Rows matched: 0  Changed: 0  Warnings: 0   

这是一个问题，因为错误(从我的角度来看是错误的，因为必须更新的关键记录没有在相关查询链中更新)没有被捕获，因此事务没有中止和回滚，而是过程继续第三个查询，该查询也成功，然后提交事务.

This is a problem because the error (is error from my perspective since a key record that must be updated was not updated in a chain of related queries) was not caught and the transaction thus did not abort and rollback, instead the process goes on to the 3rd query which also succeeds and then the transaction is committed.

所以，我觉得奇怪的是，这样的错误没有被 MySQL 捕获或没有被 MySQL 标记为错误.

So, I find it strange that such an error is not caught by MySQL or not labeled an error by MySQL.

关于为什么或如何解决的任何见解?

Any insights as to why or how to fix?

推荐答案

正确，更新了 0 行.如果，对于您的逻辑，这是一个错误，您应该测试受影响的行数，然后在该数字为 0 时引发错误:

It is correct, 0 rows were updated. If, for your logic, that is an error you should test the number of affected rows and then raise an error if that number is 0:

  DECLARE count INT;
  UPDATE tableX SET bal = 4576.99 WHERE c_id = 3789;
  SELECT ROW_COUNT() INTO count;
  IF count = 0 THEN
     CALL raise_error;
  END IF;

错误将使事务回滚.要引发错误，只需调用一个不存在的例程，如this SO question所述:如何在 MySQL 函数中引发错误

error will make the transaction rollback. To raise an error just call a routine which doesn't exist as explained on this SO question: How to raise an error within a MySQL function

关于 row_count() 的更多信息:

further info about row_count():

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

这篇关于MySQL 记录更新应该失败，但不会.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！