MySQL中update修改数据与原数据相同是不是会再次执行
发布时间：2022-02-27 14:51:42 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下MySQL中update修改数据与原数据相同是否会再次执行，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 本文主要测试MySQL执行update语句时，针对与原数据（
|
小编给大家分享一下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 statusGshow master statusG ... --- 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 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 statusGshow master statusG ... --- 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)”这个接口，但是引擎发现值与原来相同，不更新，直接返回。 （编辑：92站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
