本篇文章为你整理了面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!()的详细内容,包含有 面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!,希望能帮助你了解 面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!。
分享Java技术,高并发编程,分布式技术,架构设计,Java面试题,算法,行业动态,程序人生等。
来源:https://developer.aliyun.com/article/694162
本文主要测试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 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/os done, 0.00 log i/os/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 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/os done, 0.00 log i/os/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 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/os done, 0.00 log i/os/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 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/os done, 0.00 log i/os/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 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/os done, 0.14 log i/os/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 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/os done, 0.00 log i/os/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)“这个操作,该加锁的加锁,该更新的更新。
近期热文推荐:
1.1,000+ 道 Java面试题及答案整理(2022最新版)
2.劲爆!Java 协程要来了。。。
3.Spring Boot 2.x 教程,太全了!
4.别再写满屏的爆爆爆炸类了,试试装饰器模式,这才是优雅的方式!!
5.《Java开发手册(嵩山版)》最新发布,速速下载!
觉得不错,别忘了随手点赞+转发哦!
以上就是面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!()的详细内容,想要了解更多 面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!的内容,请持续关注盛行IT软件开发工作室。
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。