MySQL中update修改数据与原数据相同会再次执行吗?

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> MySQL中update修改数据与原数据相同会再次执行吗?

点击上方“后端技术精选”,选择“置顶公众号”

技术文章第一时间送达!

作者:powdba https://yq.aliyun.com/articles/694162

https://yq.aliyun.com/articles/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 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
    
    *************************** 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 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)
    

    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)”这个接口,但是引擎发现值与原来相同,不更新,直接返回

    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 statusGshow master statusG
    ...
    ---
    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 statusGshow master statusG
    ...
    ---
    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 statusGshow master statusG
    ...
    ---
    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)“这个操作,该加锁的加锁,该更新的更新。

    推荐阅读(点击即可跳转阅读)

    1. 

    **2. **

    **3. **

    **4. **

    **5. **

    MySQL中update修改数据与原数据相同会再次执行吗?

    原文始发于微信公众号(后端技术精选):

    本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

    转载声明:转载请注明出处,本技术博客是本人原创文章

    本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

    原文链接:blog.ouyangsihai.cn >> MySQL中update修改数据与原数据相同会再次执行吗?


     上一篇
    【从入门到入土】令人脱发的数据库底层设计 【从入门到入土】令人脱发的数据库底层设计
    点击上方“Java知音”,选择“置顶公众号” 技术文章第一时间送达! 作者:ObjectSpace juejin.im/post/5d6f3efde51d4561fd6cb548 juejin.im/post/5d6f3e
    下一篇 
    SQL 性能优化梳理 SQL 性能优化梳理
    点击上方“Java知音”,选择“置顶公众号” 技术文章第一时间送达! 作者:wolearn juejin.im/post/59b11ba151882538cb1ecbd0 juejin.im/post/59b11ba151