MySQL DML - MySQL中update修改的数据与原数据相同是否会再次执行 - 《SQL》

环境说明
1、binlog_format为ROW
2、binlog_format为STATEMENT

MySQL UPDATE

环境说明

MySQL8.0

1、`binlog_format`为`ROW`

A.查看相关参数值

mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

B.测试步骤

session1：select

mysql> use test;
Database changed
mysql> select * from user where id = 1;
+----+---------+-----+------+---------------------+-----------+
| id | user_id | age | name | login_time          | address   |
+----+---------+-----+------+---------------------+-----------+
|  1 | 11      |  18 | Fc   | 2020-07-23 22:16:57 | 北京市    |
+----+---------+-----+------+---------------------+-----------+
1 row in set (0.00 sec)
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 14:17:04 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 17 srv_active, 0 srv_shutdown, 23290 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16774
Purge done for trx's n:o < 16773 undo n:o < 0 state: running but idle
History list length 54
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1065 OS file reads, 1161 OS file writes, 330 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          34446419
Log buffer assigned up to    34446419
Log buffer completed up to   34446419
Log written up to            34446419
Log flushed up to            34446419
Added dirty pages up to      34446419
Pages flushed up to          34446419
Last checkpoint at           34446419
330 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 429080
Buffer pool size   8192
Free buffers       6969
Database pages     1212
Old database pages 458
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1042, created 170, written 723
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1212, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 246, updated 426, deleted 194, read 33839
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 3434
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>

session2：update

mysql> update user set user_id = 11 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 14:21:11 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 17 srv_active, 0 srv_shutdown, 23537 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16775
Purge done for trx's n:o < 16773 undo n:o < 0 state: running but idle
History list length 54
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1065 OS file reads, 1161 OS file writes, 330 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          34446419
Log buffer assigned up to    34446419
Log buffer completed up to   34446419
Log written up to            34446419
Log flushed up to            34446419
Added dirty pages up to      34446419
Pages flushed up to          34446419
Last checkpoint at           34446419
330 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 429080
Buffer pool size   8192
Free buffers       6969
Database pages     1212
Old database pages 458
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1042, created 170, written 723
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1212, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 246, updated 426, deleted 194, read 33840
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 3434
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>

session3：update

mysql> update user set user_id = 11 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 14:28:37 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 17 srv_active, 0 srv_shutdown, 23983 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16776
Purge done for trx's n:o < 16773 undo n:o < 0 state: running but idle
History list length 54
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1065 OS file reads, 1161 OS file writes, 330 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 6 buffer(s)
0.00 hash searches/s, 0.05 non-hash searches/s
---
LOG
---
Log sequence number          34446419
Log buffer assigned up to    34446419
Log buffer completed up to   34446419
Log written up to            34446419
Log flushed up to            34446419
Added dirty pages up to      34446419
Pages flushed up to          34446419
Last checkpoint at           34446419
330 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 429080
Buffer pool size   8192
Free buffers       6969
Database pages     1212
Old database pages 458
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1042, created 170, written 723
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1212, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 246, updated 426, deleted 194, read 33841
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.05 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 3434
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>

对比session2中的upadted发现updated一致

C.总结

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

2、`binlog_format`为`STATEMENT`

A.查看相关参数值

mysql> set session binlog_format = statement;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql>

B.测试步骤

session1：select

mysql> select * from user where id = 1;
+----+---------+-----+------+---------------------+-----------+
| id | user_id | age | name | login_time          | address   |
+----+---------+-----+------+---------------------+-----------+
|  1 | 11      |  18 | Fc   | 2020-07-23 22:16:57 | 北京市    |
+----+---------+-----+------+---------------------+-----------+
1 row in set (0.00 sec)
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 15:04:07 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 21 srv_active, 0 srv_shutdown, 26107 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 12
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16847
Purge done for trx's n:o < 16779 undo n:o < 0 state: running but idle
History list length 87
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1122 OS file reads, 1306 OS file writes, 400 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          34458677
Log buffer assigned up to    34458677
Log buffer completed up to   34458677
Log written up to            34458677
Log flushed up to            34458677
Added dirty pages up to      34458677
Pages flushed up to          34458677
Last checkpoint at           34458677
392 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 473442
Buffer pool size   8192
Free buffers       6911
Database pages     1267
Old database pages 487
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1097, created 170, written 797
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1267, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 250, updated 459, deleted 194, read 53826
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 3434
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

session2：update

mysql> update user set user_id = 111 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 15:12:04 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 24 srv_active, 0 srv_shutdown, 26580 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18
OS WAIT ARRAY INFO: signal count 17
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16853
Purge done for trx's n:o < 16853 undo n:o < 0 state: running but idle
History list length 90
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1122 OS file reads, 1335 OS file writes, 420 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.35 writes/s, 0.21 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 7 buffer(s)
0.00 hash searches/s, 0.21 non-hash searches/s
---
LOG
---
Log sequence number          34460271
Log buffer assigned up to    34460271
Log buffer completed up to   34460271
Log written up to            34460271
Log flushed up to            34460271
Added dirty pages up to      34460271
Pages flushed up to          34460271
Last checkpoint at           34460271
402 log i/o's done, 0.15 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 473442
Buffer pool size   8192
Free buffers       6911
Database pages     1267
Old database pages 487
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1097, created 170, written 810
0.00 reads/s, 0.00 creates/s, 0.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1267, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 250, updated 462, deleted 194, read 53829
0.00 inserts/s, 0.03 updates/s, 0.00 deletes/s, 0.03 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 4419
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

session3：update

mysql> update user set user_id = 111 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> show engine innodb status\Gshow master status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-07-24 15:12:17 0x3b50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 25 srv_active, 0 srv_shutdown, 26592 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 19
OS WAIT ARRAY INFO: signal count 18
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 125, rounds 242, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.94 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16855
Purge done for trx's n:o < 16855 undo n:o < 0 state: running but idle
History list length 91
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283929911699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283929911700120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1122 OS file reads, 1344 OS file writes, 426 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.69 writes/s, 0.46 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 7 buffer(s)
0.00 hash searches/s, 0.08 non-hash searches/s
---
LOG
---
Log sequence number          34460759
Log buffer assigned up to    34460759
Log buffer completed up to   34460759
Log written up to            34460759
Log flushed up to            34460759
Added dirty pages up to      34460759
Pages flushed up to          34460759
Last checkpoint at           34460759
405 log i/o's done, 0.23 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 473442
Buffer pool size   8192
Free buffers       6911
Database pages     1267
Old database pages 487
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1097, created 170, written 814
0.00 reads/s, 0.00 creates/s, 0.31 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1267, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=472, Main thread ID=00000000000039CC , state=sleeping
Number of rows inserted 250, updated 463, deleted 194, read 53830
0.00 inserts/s, 0.08 updates/s, 0.00 deletes/s, 0.08 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
*************************** 1. row ***************************
             File: binlog.000021
         Position: 4748
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> select * from user where id = 1;
+----+---------+-----+------+---------------------+-----------+
| id | user_id | age | name | login_time          | address   |
+----+---------+-----+------+---------------------+-----------+
|  1 | 111     |  18 | Fc   | 2020-07-23 22:16:57 | 北京市    |
+----+---------+-----+------+---------------------+-----------+
1 row in set (0.00 sec)

对比session2中的upadted发现执行改变

C.总结

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

MySQL中update修改的数据与原数据相同是否会再次执行

环境说明

1、binlog_format为ROW

A.查看相关参数值

B.测试步骤

session1：select

session2：update

session3：update

C.总结

2、binlog_format为STATEMENT

A.查看相关参数值

B.测试步骤

session1：select

session2：update

session3：update

C.总结

1、`binlog_format`为`ROW`

2、`binlog_format`为`STATEMENT`