⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 zhuanlan.zhihu.com/p/59717198 「powdba」欢迎转载，保留摘要，谢谢！

背景

本文主要测试MySQL执行update语句时，针对与原数据（即未修改）相同的update语句会在MySQL内部重新执行吗？

测试环境

MySQL5.7.25
Centos 7.4

binlog_format为ROW

参数

root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost : (none) 04:53:49> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost : test 05:15:14> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

测试步骤

session1

root@localhost : test 04:49:48> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:49:52> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |  999 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12090390
Log flushed up to   12090390
Pages flushed up to 12090390
Last checkpoint at  12090381
0 pending log flushes, 0 pending chkp writes
33 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

session2

root@localhost : test 04:47:45> update test set sid=55 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12091486
Log flushed up to   12091486
Pages flushed up to 12091486
Last checkpoint at  12091477
0 pending log flushes, 0 pending chkp writes
39 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 500
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

session1

root@localhost : test 04:49:57> update test set sid=55 where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12091486
Log flushed up to   12091486
Pages flushed up to 12091486
Last checkpoint at  12091477
0 pending log flushes, 0 pending chkp writes
39 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 500
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

root@localhost : test 04:52:05> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |  999 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

root@localhost : test 04:52:42> commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 04:52:52> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |   55 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

总结

在binlog_format=row和binlog_row_image=FULL时，由于MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来，那么重复数据的update不会执行。即MySQL 调用了 InnoDB 引擎提供的“修改为 (1,55)”这个接口，但是引擎发现值与原来相同，不更新，直接返回

binlog_format为STATEMENT

参数

root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

root@localhost : (none) 05:16:08>  show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

root@localhost : test 05:15:14> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

测试步骤

session1

root@localhost : test 05:16:42> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 05:16:44> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |  111 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

root@localhost : (none) 05:16:51> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12092582
Log flushed up to   12092582
Pages flushed up to 12092582
Last checkpoint at  12092573
0 pending log flushes, 0 pending chkp writes
45 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

session2

root@localhost : test 05:18:30> update test set sid=999 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost : (none) 05:18:47> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12093678
Log flushed up to   12093678
Pages flushed up to 12093678
Last checkpoint at  12093669
0 pending log flushes, 0 pending chkp writes
51 log i/o's done, 0.14 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 438
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

session1

root@localhost : test 05:16:47> update test set sid=999 where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

root@localhost : (none) 05:20:03> show engine innodb status\Gshow master status\G
...
---
LOG
---
Log sequence number 12094504
Log flushed up to   12094504
Pages flushed up to 12094504
Last checkpoint at  12094495
0 pending log flushes, 0 pending chkp writes
56 log i/o's done, 0.00 log i/o's/second

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 438
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
1 row in set (0.00 sec)

root@localhost : test 05:19:33> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |  999 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

root@localhost : test 05:20:44> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost : test 05:20:57> select * from test where id =1;
+----+------+------+------+
| id | sid  | mid  | name |
+----+------+------+------+
|  1 |  999 |  871 | NW   |
+----+------+------+------+
1 row in set (0.00 sec)

总结

在binlog_format=statement和binlog_row_image=FULL时，InnoDB内部认真执行了update语句，即“把这个值修改成 (1,999)“这个操作，该加锁的加锁，该更新的更新。

芋道源码 —— 纯源码解析博客

MySQL中，当update修改数据与原数据相同时会再次执行吗？

背景

测试环境

binlog_format为ROW

测试步骤

总结

binlog_format为STATEMENT

测试步骤

总结