MySQL执行update语句和原数据相同会再次执行吗
背景
本文主要测试MySQL执行update语句时，针对与原数据（即未修改）相同的update语句会在MySQL内部重新执行吗？
测试环境
- MySQL5.7.25
- Centos7.4
binlog_format为ROW
参数
root@localhost:(none)04:53:15>showvariableslike'binlog_row_image'; +------------------+-------+ |Variable_name|Value| +------------------+-------+ |binlog_row_image|FULL| +------------------+-------+ 1rowinset(0.00sec) root@localhost:(none)04:53:49>showvariableslike'binlog_format'; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |binlog_format|ROW| +---------------+-------+ 1rowinset(0.00sec) root@localhost:test05:15:14>showvariableslike'transaction_isolation'; +-----------------------+-----------------+ |Variable_name|Value| +-----------------------+-----------------+ |transaction_isolation|REPEATABLE-READ| +-----------------------+-----------------+ 1rowinset(0.00sec)
测试步骤
session1
root@localhost:test04:49:48>begin; QueryOK,0rowsaffected(0.00sec) root@localhost:test04:49:52>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|999|871|NW| +----+------+------+------+ 1rowinset(0.00sec) root@localhost:(none)04:54:03>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12090390 Logflushedupto12090390 Pagesflushedupto12090390 Lastcheckpointat12090381 0pendinglogflushes,0pendingchkpwrites 33logi/o'sdone,0.00logi/o's/second ***************************1.row*************************** File:mysql-bin.000001 Position:154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1rowinset(0.00sec)
session2
root@localhost:test04:47:45>updatetestsetsid=55whereid=1; QueryOK,1rowaffected(0.01sec) Rowsmatched:1Changed:1Warnings:0 root@localhost:(none)04:54:03>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12091486 Logflushedupto12091486 Pagesflushedupto12091486 Lastcheckpointat12091477 0pendinglogflushes,0pendingchkpwrites 39logi/o'sdone,0.00logi/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 1rowinset(0.00sec)
session1
root@localhost:test04:49:57>updatetestsetsid=55whereid=1; QueryOK,0rowsaffected(0.00sec) Rowsmatched:1Changed:0Warnings:0 root@localhost:(none)04:54:03>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12091486 Logflushedupto12091486 Pagesflushedupto12091486 Lastcheckpointat12091477 0pendinglogflushes,0pendingchkpwrites 39logi/o'sdone,0.00logi/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 1rowinset(0.00sec) root@localhost:test04:52:05>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|999|871|NW| +----+------+------+------+ 1rowinset(0.00sec) root@localhost:test04:52:42>commit; QueryOK,0rowsaffected(0.00sec) root@localhost:test04:52:52>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|55|871|NW| +----+------+------+------+ 1rowinset(0.00sec)
总结
在binlog_format=row和binlog_row_image=FULL时，由于MySQL需要在binlog里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来，那么重复数据的update不会执行。即MySQL调用了InnoDB引擎提供的“修改为(1,55)”这个接口，但是引擎发现值与原来相同，不更新，直接返回
binlog_format为STATEMENT
参数
root@localhost:(none)04:53:15>showvariableslike'binlog_row_image'; +------------------+-------+ |Variable_name|Value| +------------------+-------+ |binlog_row_image|FULL| +------------------+-------+ 1rowinset(0.00sec) root@localhost:(none)05:16:08>showvariableslike'binlog_format'; +---------------+-----------+ |Variable_name|Value| +---------------+-----------+ |binlog_format|STATEMENT| +---------------+-----------+ 1rowinset(0.00sec) root@localhost:test05:15:14>showvariableslike'transaction_isolation'; +-----------------------+-----------------+ |Variable_name|Value| +-----------------------+-----------------+ |transaction_isolation|REPEATABLE-READ| +-----------------------+-----------------+ 1rowinset(0.00sec)
测试步骤
session1
root@localhost:test05:16:42>begin; QueryOK,0rowsaffected(0.00sec) root@localhost:test05:16:44>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|111|871|NW| +----+------+------+------+ 1rowinset(0.00sec) root@localhost:(none)05:16:51>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12092582 Logflushedupto12092582 Pagesflushedupto12092582 Lastcheckpointat12092573 0pendinglogflushes,0pendingchkpwrites 45logi/o'sdone,0.00logi/o's/second ***************************1.row*************************** File:mysql-bin.000001 Position:154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1rowinset(0.00sec)
session2
root@localhost:test05:18:30>updatetestsetsid=999whereid=1; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 root@localhost:(none)05:18:47>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12093678 Logflushedupto12093678 Pagesflushedupto12093678 Lastcheckpointat12093669 0pendinglogflushes,0pendingchkpwrites 51logi/o'sdone,0.14logi/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 1rowinset(0.00sec)
session1
root@localhost:test05:16:47>updatetestsetsid=999whereid=1; QueryOK,0rowsaffected(0.00sec) Rowsmatched:1Changed:0Warnings:0 root@localhost:(none)05:20:03>showengineinnodbstatus\Gshowmasterstatus\G ... --- LOG --- Logsequencenumber12094504 Logflushedupto12094504 Pagesflushedupto12094504 Lastcheckpointat12094495 0pendinglogflushes,0pendingchkpwrites 56logi/o'sdone,0.00logi/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 1rowinset(0.00sec) root@localhost:test05:19:33>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|999|871|NW| +----+------+------+------+ 1rowinset(0.00sec) root@localhost:test05:20:44>commit; QueryOK,0rowsaffected(0.01sec) root@localhost:test05:20:57>select*fromtestwhereid=1; +----+------+------+------+ |id|sid|mid|name| +----+------+------+------+ |1|999|871|NW| +----+------+------+------+ 1rowinset(0.00sec)
总结
在binlog_format=statement和binlog_row_image=FULL时，InnoDB内部认真执行了update语句，即“把这个值修改成(1,999)“这个操作，该加锁的加锁，该更新的更新。
好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。