面试官：MySQL 中 update 更新，数据与原数据相同时会执行吗？大部分人答不 ...

来源：https://developer.aliyun.com/article/694162
背景

本文主要测试MySQL执行update语句时，针对与原数据（即未修改）相同的update语句会在MySQL内部重新执行吗？
测试环境

• MySQL5.7.25
  
• Centos 7.4
  

binlog_format为ROW

参数

1. root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
2. +------------------+-------+
3. | Variable_name    | Value |
4. +------------------+-------+
5. | binlog_row_image | FULL  |
6. +------------------+-------+
7. 1 row in set (0.00 sec)
9. root@localhost : (none) 04:53:49> show variables like 'binlog_format';
10. +---------------+-------+
11. | Variable_name | Value |
12. +---------------+-------+
13. | binlog_format | ROW   |
14. +---------------+-------+
15. 1 row in set (0.00 sec)
17. root@localhost : test 05:15:14> show variables like 'transaction_isolation';
18. +-----------------------+-----------------+
19. | Variable_name         | Value           |
20. +-----------------------+-----------------+
21. | transaction_isolation | REPEATABLE-READ |
22. +-----------------------+-----------------+
23. 1 row in set (0.00 sec)

复制代码

测试步骤

session1

1. root@localhost : test 04:49:48> begin;
2. Query OK, 0 rows affected (0.00 sec)
4. root@localhost : test 04:49:52> select * from test where id =1;
5. +----+------+------+------+
6. | id | sid  | mid  | name |
7. +----+------+------+------+
8. |  1 |  999 |  871 | NW   |
9. +----+------+------+------+
10. 1 row in set (0.00 sec)
12. root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
13. ...
14. ---
15. LOG
16. ---
17. Log sequence number 12090390
18. Log flushed up to   12090390
19. Pages flushed up to 12090390
20. Last checkpoint at  12090381
21. 0 pending log flushes, 0 pending chkp writes
22. 33 log i/o's done, 0.00 log i/o's/second
24. *************************** 1. row ***************************
25.              File: mysql-bin.000001
26.          Position: 154
27.      Binlog_Do_DB:
28. Binlog_Ignore_DB:
29. Executed_Gtid_Set:
30. 1 row in set (0.00 sec)

复制代码

session2

1. root@localhost : test 04:47:45> update test set sid=55 where id =1;
2. Query OK, 1 row affected (0.01 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
6. ...
7. ---
8. LOG
9. ---
10. Log sequence number 12091486
11. Log flushed up to   12091486
12. Pages flushed up to 12091486
13. Last checkpoint at  12091477
14. 0 pending log flushes, 0 pending chkp writes
15. 39 log i/o's done, 0.00 log i/o's/second
17. *************************** 1. row ***************************
18.              File: mysql-bin.000001
19.          Position: 500
20.      Binlog_Do_DB:
21. Binlog_Ignore_DB:
22. Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
23. 1 row in set (0.00 sec)

复制代码

session1

1. root@localhost : test 04:49:57> update test set sid=55 where id =1;   
2. Query OK, 0 rows affected (0.00 sec)
3. Rows matched: 1  Changed: 0  Warnings: 0
5. root@localhost : (none) 04:54:03> show engine innodb status\Gshow master status\G
6. ...
7. ---
8. LOG
9. ---
10. Log sequence number 12091486
11. Log flushed up to   12091486
12. Pages flushed up to 12091486
13. Last checkpoint at  12091477
14. 0 pending log flushes, 0 pending chkp writes
15. 39 log i/o's done, 0.00 log i/o's/second
17. *************************** 1. row ***************************
18.              File: mysql-bin.000001
19.          Position: 500
20.      Binlog_Do_DB:
21. Binlog_Ignore_DB:
22. Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
23. 1 row in set (0.00 sec)
25. root@localhost : test 04:52:05> select * from test where id =1;
26. +----+------+------+------+
27. | id | sid  | mid  | name |
28. +----+------+------+------+
29. |  1 |  999 |  871 | NW   |
30. +----+------+------+------+
31. 1 row in set (0.00 sec)
33. root@localhost : test 04:52:42> commit;
34. Query OK, 0 rows affected (0.00 sec)
36. root@localhost : test 04:52:52> select * from test where id =1;
37. +----+------+------+------+
38. | id | sid  | mid  | name |
39. +----+------+------+------+
40. |  1 |   55 |  871 | NW   |
41. +----+------+------+------+
42. 1 row in set (0.00 sec)

复制代码

总结

• 在binlog_format=row和binlog_row_image=FULL时，由于MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来，那么重复数据的update不会执行。即MySQL 调用了 InnoDB 引擎提供的“修改为 (1,55)”这个接口，但是引擎发现值与原来相同，不更新，直接返回
  

binlog_format为STATEMENT

参数

1. root@localhost : (none) 04:53:15> show variables like 'binlog_row_image';
2. +------------------+-------+
3. | Variable_name    | Value |
4. +------------------+-------+
5. | binlog_row_image | FULL  |
6. +------------------+-------+
7. 1 row in set (0.00 sec)
9. root@localhost : (none) 05:16:08>  show variables like 'binlog_format';
10. +---------------+-----------+
11. | Variable_name | Value     |
12. +---------------+-----------+
13. | binlog_format | STATEMENT |
14. +---------------+-----------+
15. 1 row in set (0.00 sec)
17. root@localhost : test 05:15:14> show variables like 'transaction_isolation';
18. +-----------------------+-----------------+
19. | Variable_name         | Value           |
20. +-----------------------+-----------------+
21. | transaction_isolation | REPEATABLE-READ |
22. +-----------------------+-----------------+
23. 1 row in set (0.00 sec)

复制代码

测试步骤

session1

1. root@localhost : test 05:16:42> begin;
2. Query OK, 0 rows affected (0.00 sec)
4. root@localhost : test 05:16:44> select * from test where id =1;
5. +----+------+------+------+
6. | id | sid  | mid  | name |
7. +----+------+------+------+
8. |  1 |  111 |  871 | NW   |
9. +----+------+------+------+
10. 1 row in set (0.00 sec)
12. root@localhost : (none) 05:16:51> show engine innodb status\Gshow master status\G
13. ...
14. ---
15. LOG
16. ---
17. Log sequence number 12092582
18. Log flushed up to   12092582
19. Pages flushed up to 12092582
20. Last checkpoint at  12092573
21. 0 pending log flushes, 0 pending chkp writes
22. 45 log i/o's done, 0.00 log i/o's/second
24. *************************** 1. row ***************************
25.              File: mysql-bin.000001
26.          Position: 154
27.      Binlog_Do_DB:
28. Binlog_Ignore_DB:
29. Executed_Gtid_Set:
30. 1 row in set (0.00 sec)

复制代码

session2

1. root@localhost : test 05:18:30> update test set sid=999 where id =1;
2. Query OK, 1 row affected (0.00 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. root@localhost : (none) 05:18:47> show engine innodb status\Gshow master status\G
6. ...
7. ---
8. LOG
9. ---
10. Log sequence number 12093678
11. Log flushed up to   12093678
12. Pages flushed up to 12093678
13. Last checkpoint at  12093669
14. 0 pending log flushes, 0 pending chkp writes
15. 51 log i/o's done, 0.14 log i/o's/second
17. *************************** 1. row ***************************
18.              File: mysql-bin.000001
19.          Position: 438
20.      Binlog_Do_DB:
21. Binlog_Ignore_DB:
22. Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
23. 1 row in set (0.00 sec)

复制代码

session1

1. root@localhost : test 05:16:47> update test set sid=999 where id =1;
2. Query OK, 0 rows affected (0.00 sec)
3. Rows matched: 1  Changed: 0  Warnings: 0
5. root@localhost : (none) 05:20:03> show engine innodb status\Gshow master status\G
6. ...
7. ---
8. LOG
9. ---
10. Log sequence number 12094504
11. Log flushed up to   12094504
12. Pages flushed up to 12094504
13. Last checkpoint at  12094495
14. 0 pending log flushes, 0 pending chkp writes
15. 56 log i/o's done, 0.00 log i/o's/second
17. *************************** 1. row ***************************
18.              File: mysql-bin.000001
19.          Position: 438
20.      Binlog_Do_DB:
21. Binlog_Ignore_DB:
22. Executed_Gtid_Set: 8392d215-4928-11e9-a751-0242ac110002:1
23. 1 row in set (0.00 sec)
25. root@localhost : test 05:19:33> select * from test where id =1;     
26. +----+------+------+------+
27. | id | sid  | mid  | name |
28. +----+------+------+------+
29. |  1 |  999 |  871 | NW   |
30. +----+------+------+------+
31. 1 row in set (0.00 sec)
33. root@localhost : test 05:20:44> commit;
34. Query OK, 0 rows affected (0.01 sec)
36. root@localhost : test 05:20:57> select * from test where id =1;
37. +----+------+------+------+
38. | id | sid  | mid  | name |
39. +----+------+------+------+
40. |  1 |  999 |  871 | NW   |
41. +----+------+------+------+
42. 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开发手册（嵩山版）》最新发布，速速下载！
觉得不错，别忘了随手点赞+转发哦！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！