来源：https://developer.aliyun.com/...背景本文主要测试MySQL执行更新时，是否会在MySQL内部重新执行与原始数据（即未修改）相同的更新语句陈述？测试环境MySQL5.7.25Centos7.4binlog_formatisROWparameterroot@localhost:(none)04:53:15>showvariableslike'binlog_row_image';+--------------------+------+|变量名|值|+------------------+------+|binlog_row_image|FULL|+----------------+--------+1行在集合中（0.00秒）root@localhost:(none)04:53:49>显示像“binlog_format”这样的变量；+----------------+--------+|变量名|值|+----------------+------+|二进制日志格式|ROW|+----------------+--------+1rowinset(0.00sec)root@localhost:test05:15:14>showvariableslike'transaction_isolation';+------------------------+------------------+|变量名|价值|+--------------------+-----------------+|事务隔离|REPEATABLE-READ|+--------------------+----------------+1行设置（0.00秒）测试步骤session1root@localhost:test04:49:48>begin;QueryOK,0rowsaffected(0.00sec)root@localhost:test04:49:52>select*fromtestwhereid=1;+----+------+------+------+|编号|标准|中期|名称|+----+------+------+-----+|1|999|第871章NW|+----+-----+------+------+1行在集合中（0.00秒）root@localhost:(none)04:54:03>showengineinnodbstatus\Gshowmasterstatus\G...---LOG---日志序列号12090390Logflushedupto12090390Pagesflushedupto12090390Lastcheckpointat120903810pendinglogflushes,0pendingchkpwrites33logi/o'sdone,0.00logi/o's/second***************************1.行***************************文件：mysql-bin.000001位置：154Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：集合中的1行（0.00秒）session2root@localhost：测试04:47:45>更新测试setsid=55whereid=1;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0root@localhost:(none)04:54:03>showengineinnodbstatus\Gshowmasterstatus\G...---LOG---日志序列号12091486Logflushedupto12091486Pagesflushedupto12091486Lastcheckpointat120914770挂起的日志刷新，0挂起的chkpwrites39日志i/o已完成，0.00日志i/o的/秒*********************************1。行***************************文件：mysql-bin.000001位置：500Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：8392d215-4928-11e9-a751-0242ac110002:11rowinset(0.00sec)session1root@localhost:test04:49:57>updatetestsetsid=55whereid=1;查询正常，0行受影响（0.00秒）行匹配：1更改：0警告：0root@localhost：（无）04:54:03>showengineinnodbstatus\Gshowmasterstatus\G...---LOG---Log序列号12091486Logflushedupto12091486Pagesflushedupto12091486Lastcheckpointat120914770pendinglogflushes,0pendingchkpwrites39logi/o'sdone,0.00logi/o's/second****************************1.行*****************************文件：mysql-bin.000001位置：500Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：8392d215-4928-11e9-a751-0242ac110002：集合中的11行（0.00秒)root@localhost:test04:52:05>select*fromtestwhereid=1;+----+-----+-----+------+|编号|标准|中期|名称|+----+------+------+-----+|1|999|第871章NW|+----+------+------+------+1行在集合中（0.00秒）root@localhost:test04:52:42>commit;查询正常，0行受影响（0.00秒）root@localhost:test04:52:52>select*fromtestwhereid=1;+----+-----+------+------+|编号|标准|中期|名称|+----+------+------+-----+|1|55|第871章NW|+----+------+------+------+1rowinset(0.00sec)Summary当binlog_format=rowandbinlog_row_image=FULL时，因为MySQL需要将所有字段记录在binlog中，这样当读取数据入库时，会将所有数据读出，那么不会执行重复数据的更新即MySQL调用InnoDB引擎提供的“修改为(1,55)”接口，但引擎发现该值与原来相同，并没有更新，直接返回binlog_format作为STATEMENT参数root@localhost:(none)04:53:15>showvariableslike'binlog_row_image';+----------------+--------+|变量名|值|+--------------------+------+|binlog_row_image|FULL|+----------------+--------集合中的+1行（0.00秒）root@localhost:(none)05:16:08>显示像'binlog_format'这样的变量；+----------------+----------+|变量名|值|+----------------+----------+|二进制日志格式|STATEMENT|+--------------+--------+1rowinset(0.00sec)root@localhost:test05:15:14>show类似“transaction_isolation”的变量;+--------------------+----------------+|变量名|价值|+--------------------+----------------+|事务隔离|可重复阅读|+--------------------+----------------+1组中的行（0.00秒)测试步骤session1root@localhost:test05:16:42>begin;QueryOK,0行影响ed(0.00sec)root@localhost:test05:16:44>select*fromtestwhereid=1;+----+-----+-----+------+|编号|标准|中期|名称|+----+------+------+-----+|1|111|第871章NW|+----+-----+------+------+1行在集合中（0.00秒）root@localhost:(none)05:16:51>showengineinnodbstatus\Gshowmasterstatus\G...---LOG---日志序列号12092582Logflushedupto12092582Pagesflushedupto12092582Lastcheckpointat120925730pendinglogflushes,0pendingchkpwrites45logi/o'sdone,0.00logi/o's/second***************************1.行***************************文件：mysql-bin.000001位置：154Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：集合中的1行（0.00秒）session2root@localhost：测试05:18:30>更新测试setsid=999whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0root@localhost:(none)05:18:47>显示引擎innodbstatus\Gshowmasterstatus\G...---LOG---日志序列号12093678日志刷新到12093678页面刷新到12093678最后检查点在120936690等待日志刷新，0等待chkp写入51日志i/o完成，0.14日志i/o/秒****************************1.行***************************文件：mysql-bin.000001位置：438Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：8392d215-4928-11e9-a751-0242ac110002：集合中的11行（0.00秒）session1root@localhost：测试05:16:47>更新测试集sid=999，其中id=1；查询正常，0行受影响（0.00秒）行匹配：1更改：0警告：0root@localhost：（无）05:20:03>showengineinnodbstatus\Gshowmasterstatus\G...---LOG---Logsequencenumber12094504Logflushedupto12094504Pagesflushedupto12094504Lastcheckpointat120944950pendinglogflushes,0pendingchkpwrites56logi/o'sdone,0.00日志i/o's/秒***************************1.行***************************文件：mysql-bin.000001位置：438Binlog_Do_DB：Binlog_Ignore_DB：Executed_Gtid_Set：8392d215-4928-11e9-a751-0242ac110002:11rowinset(0.00sec)root@localhost:test05:19:33>whereelectfromtestsid=1;+----+-----+-----+-----+|编号|标准|中期|名称|+----+------+------+-----+|1|999|第871章NW|+----+------+------+------+1行在集合中（0.00秒）root@localhost:test05:20:44>commit;查询正常，0行受影响（0.01秒）root@localhost:test05:20:57>select*fromtestwhereid=1;+----+-----+------+------+|编号|标准|中期|名称|+----+------+------+-----+|1|999|第871章NW|+----+-----+-----+------+1rowinset(0.00sec)Summary当binlog_format=statementandbinlog_row_image=FULL时，InnoDB小心执行里面的update语句，也就是“把这个值改成(1,999)”这个操作，应该加锁的锁，这个更新的最新更新推荐最近的热点文章：1.1,000+Java面试题及答案（2022最新版）2.世界上最好的！Java协程来了。..3.SpringBoot2.x教程，太全面了！4.不要用爆破爆满画面，试试装饰者模式，这才是优雅的方式！！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
