分析MySQL中update修改数据与原数据相同是否会再次执行
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了分析MySQL中update修改数据与原数据相同是否会再次执行,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7800字,纯文字阅读大概需要12分钟。
内容图文
本篇文章给大家带来的内容是关于分析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)“这个操作,该加锁的加锁,该更新的更新。
以上就是分析MySQL中update修改数据与原数据相同是否会再次执行的详细内容,更多请关注Gxl网其它相关文章!
内容总结
以上是互联网集市为您收集整理的分析MySQL中update修改数据与原数据相同是否会再次执行全部内容,希望文章能够帮你解决分析MySQL中update修改数据与原数据相同是否会再次执行所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。