关于MySQL中某个操作影响行数为0的理解

mysql> update t set a=2 where id=1; 你会看到这样的结果： mysql> update t set a=2 where id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1  Changed: 0  Warnings: 0

结果显示，匹配 (rows matched) 了一行，修改 (Changed) 了 0 行

仅从现象上看，MySQL 内部在处理这个命令的时候，可以有以下三种选择：

1、更新都是先读后写的，MySQL 读出数据，发现 a 的值本来就是 2，不更新，直接返回，执行结束；

2、MySQL 调用了 InnoDB 引擎提供的“修改为 (1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；

3、InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作，该加锁的加锁，该更新的更新。

你觉得实际情况会是以上哪种呢？你可否用构造实验的方式，来证明你的结论？进一步地，可以思考一下，MySQL 为什么要选择这种策略呢？

解答：

一：binlog_format=statement的情况：

当 MySQL 去更新一行，但是要修改的值跟原来的值是相同的，这时候 MySQL 会真的去执行一次修改吗？还是看到值相同就直接返回呢？

这是第一次我们课后问题的三个选项都有同学选的，所以我要和你需要详细说明一下。

第一个选项是，MySQL 读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。

假设，当前表 t 里的值是 (1,2)。

图 12 锁验证方式

session B 的 update 语句被 blocked 了，加锁这个动作是 InnoDB 才能做的，所以排除选项 1。

第二个选项是，MySQL 调用了 InnoDB 引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。有没有这种可能呢？这里我用一个可见性实验来确认。

假设当前表里的值是 (1,2)。

图 13 可见性验证方式

session A 的第二个 select 语句是一致性读（快照读)，它是不能看见 session B 的更新的。

现在它返回的是 (1,3)，表示它看见了某个新的版本，这个版本只能是 session A 自己的 update 语句做更新的时候生成。（如果你对这个逻辑有疑惑的话，可以回顾下第 8 篇文章《事务到底是隔离的还是不隔离的？》中的相关内容）

所以，我们上期思考题的答案应该是选项 3，即：InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作，该加锁的加锁，该更新的更新。

然后你会说，MySQL 怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费 InnoDB 操作，多去更新一次了？

其实 MySQL 是确认了的。只是在这个语句里面，MySQL 认为读出来的值，只有一个确定的 (id=1), 而要写的是 (a=3)，只从这两个信息是看不出来“不需要修改”的。

作为验证，你可以看一下下面这个例子。

图 14 可见性验证方式 -- 对照

二：binlog_format=row并且 binlog_row_image=FULL的情况：

时刻 1 session 1 ，开启一个事务，然后查询表的数据，

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    2 |

+----+------+

1 row in set (0.00 sec)

时刻2 session 2，更新a=3

mysql> update t set a=3 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

时刻3 回到session1，同样更新a=3,    #注意此时这里的这个update不记录到binlog中

mysql> update t set a=3 where id=1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

时刻 4紧接着查询数据！发现依旧是2,没查询到session2更新的数据！

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    2 |

+----+------+

1 row in set (0.00 sec)

时刻5 回到session 2，继续执行update,发现处于blocked状态，因为session1的update还没有更新！

mysql> update t set a=46 where id=1;

blocked。。。。。

三：binlog_format=row并且 binlog_row_image=minimal的情况：

时刻1 session 1

mysql> set  binlog_row_image ='minimal';

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    2 |

+----+------+

1 row in set (0.00 sec)

时刻 2 session 2

mysql> update t set a=3 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

时刻3 ，回到session 1，更新a=3，#注意此时这里的这个update也不记录到binlog中

mysql> update t set a=3 where id=1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

时刻4，再查看发现能查看a=3,

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    3 |

+----+------+

1 row in set (0.00 sec)

说明：如果是 binlog_format=row 并且 binlog_row_image=FULL 的时候，由于 MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来了。这样就能判断是否是一样的了，进而也就能判断是否需要再

更新了！

根据上面说的规则，“既然读了数据，就会判断”， 因此在这时候，select * from t where id=1，结果就是“返回 (1,2)”。

同理，如果是 binlog_row_image=NOBLOB, 会读出除 blob 外的所有字段，在我们这个例子里，结果还是“返回 (1,2)”。

对应的代码如图 15 所示。这是 MySQL 5.6 版本引入的，在此之前我没有看过。所以，特此说明。

图 15 binlog_row_image=FULL 读字段逻辑

类似的 如果涉及到timestamp 字段的问题。结论是：如果表中有 timestamp 字段而且设置了自动更新的话，那么更新“别的字段”的时候，MySQL 会读入所有涉及的字段，这样通过判断，就会发现不需要修改。

关于binlog中内容，如果某个update影响的行数为0，那么binlog中就不记录！因为对数据库没有做任何修改，不影响主从同步一致性，记录这个数据反而对数据库带来没必要的负担！

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |   45 |

+----+------+

1 row in set (0.00 sec)

mysql> update t set a=45 where id=1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

具体binglog内容如下，没有发现，确实没有记录这个更新操作！

[root@t1-28-88 data]# vim 10

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#220204 18:05:12 server id 1383306  end_log_pos 123 CRC32 0xbee70783    Start: binlog v 4, server v 5.7.28-log created 220204 18:05:12

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

WPr8YQ+KGxUAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AYMH574=

'/*!*/;

# at 123

#220204 18:05:12 server id 1383306  end_log_pos 194 CRC32 0xf904aaa1    Previous-GTIDs

# 69f8d757-e53a-11eb-8280-005056bdc6db:3344-104296

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

~