MySQL中，當(dāng)update修改數(shù)據(jù)與原數(shù)據(jù)相同時會再次執(zhí)行嗎？

程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享?

閱讀本文大概需要 7?分鐘。

來自：zhuanlan.zhihu.com/p/59717198

背景

本文主要測試MySQL執(zhí)行update語句時，針對與原數(shù)據(jù)（即未修改）相同的update語句會在MySQL內(nèi)部重新執(zhí)行嗎？

推薦下自己做的 Spring Boot 的實(shí)戰(zhàn)項(xiàng)目：

https://github.com/YunaiV/ruoyi-vue-pro

測試環(huán)境

• MySQL5.7.25
• Centos 7.4

推薦下自己做的 Spring Cloud 的實(shí)戰(zhàn)項(xiàng)目：

https://github.com/YunaiV/onemall

binlog_format為ROW

參數(shù)

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)

總結(jié)

在binlog_format=row和binlog_row_image=FULL時，由于MySQL 需要在 binlog 里面記錄所有的字段，所以在讀數(shù)據(jù)的時候就會把所有數(shù)據(jù)都讀出來，那么重復(fù)數(shù)據(jù)的update不會執(zhí)行。即MySQL 調(diào)用了 InnoDB 引擎提供的“修改為 (1,55)”這個接口，但是引擎發(fā)現(xiàn)值與原來相同，不更新，直接返回

binlog_format為STATEMENT

參數(shù)

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)

總結(jié)

在binlog_format=statement和binlog_row_image=FULL時，InnoDB內(nèi)部認(rèn)真執(zhí)行了update語句，即“把這個值修改成 (1,999)“這個操作，該加鎖的加鎖，該更新的更新。

推薦閱讀：

騰訊公布 23 年前第一間辦公室照片，太有年代感了

為什么用etcd而不用Zookeeper？

互聯(lián)網(wǎng)初中高級大廠面試題(9個G)

內(nèi)容包含Java基礎(chǔ)、JavaWeb、MySQL性能優(yōu)化、JVM、鎖、百萬并發(fā)、消息隊(duì)列、高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper、數(shù)據(jù)結(jié)構(gòu)、限流熔斷降級......等技術(shù)棧！

?戳閱讀原文領(lǐng)??！? ? ? ? ? ? ? ??? ??? ? ? ? ? ? ? ? ? ?朕已閱?