Do Identical UPDATE Statements Trigger MySQL Execution? A Binlog Format Comparison
This article investigates whether MySQL re‑executes UPDATE statements that do not change any data, comparing the behavior under binlog_format = ROW and binlog_format = STATEMENT with a full row image setting, and presents test results from two sessions.
Background
This article mainly tests whether MySQL re‑executes an UPDATE statement that is identical to the original data (i.e., no change) when the UPDATE is issued.
Test Environment
MySQL 5.7.25
CentOS 7.4
binlog_format = ROW
Parameters
Test Steps
Session 1:
Session 2:
Session 1 (repeat):
Summary
When binlog_format=row and binlog_row_image=FULL , MySQL records all fields in the binlog, so reading data loads every column; consequently, an UPDATE that does not change any values is not executed. MySQL calls the InnoDB interface to modify the row, but the engine detects that the value is unchanged and returns without performing an update.
binlog_format = STATEMENT
Parameters
Test Steps
Session 1:
Session 2:
Session 1 (repeat):
Summary
When binlog_format=statement and binlog_row_image=FULL , InnoDB fully executes the UPDATE statement, applying locks and performing the actual data modification (e.g., changing the value to (1,999)).
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Programmer DD
A tinkering programmer and author of "Spring Cloud Microservices in Action"
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.