Giant pit! Common update statements can easily cause bugs

In business systems, updating data using update statements is a normal scenario, and we often make some business judgments by the number of rows updated by update, similar to the following pseudocode: (mybatis + mysql scenario)

if (xxxMapper.updateByPrimaryKeySelective(entity)>0){
   //更新成功，做其它业务处理
}
           

But here's a pit where the update affects the number of rows >0 in mysql is conditional, if there is a table:

There is only one record (ID=1) in it, and we update this record with update with ID=1

This line is successfully updated, affecting the number of rows to 1, of course, and then executes this update statement again:

At this point, the number of affected rows returned is 0, which means that when the record to be updated is the same as the old value of the original record, mysql does not actually make any updates. In other words, if the data passed upstream is equal to the old value of the database itself, and there is no change, the update statement affects the number of rows to 0. This is indistinguishable from another scenario: "Updating a record that doesn't exist affects the number of rows returning 0" is indistinguishable.

Conclusion: Don't use the update statement to make important business judgments about the number of rows affected!

Source: cnblogs.com/yjmyzz/p/13562182.html