mysql单表多字段update的执行顺序

链接: MySQL 5.6 Reference Manual / UPDATE Statement

针对以下类似sql

UPDATE t1 SET col1 = col1 + 1, col2 = col1 - 1;

执行结果和从左往右, col1原值加1, col2 = 更新后的col1减1(与col1原值相同)

原文

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

If you set a column to the value it currently has, MySQL notices this and does not update it.

注意: 对于以上情况, mysql单表更新赋值通常是从左到右计算的。对于多表更新，不能保证按任何特定的顺序执行分配。

同时, 如果update赋值前后的结果不变, mysql不会执行update, 所以不能通过sql的影响行数来判断业务逻辑的执行情况