面试官:MySQL 中 update 更新,数据与原数据相同时会执行吗?大部分人答不上来!()

  本篇文章为你整理了面试官: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的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: