MySQL执行update语句和原数据相同会再次执行吗

背景

本文主要测试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)“这个操作，该加锁的加锁，该更新的更新。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。