Testing Whether MySQL Executes UPDATE Statements When Data Is Unchanged Under Different binlog_format Settings
This article investigates how MySQL handles UPDATE statements that set columns to their existing values, comparing the behavior under ROW and STATEMENT binlog formats with binlog_row_image set to FULL, and shows that duplicate updates are skipped in ROW mode but executed in STATEMENT mode.
1. Background
The article tests whether MySQL re-executes an UPDATE statement that sets columns to the same values as the existing data.
2. Test Environment
MySQL 5.7.25 running on CentOS 7.4.
3. 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 column values in the binlog; during read it loads the full row, detects that the new values are identical to the old ones, and therefore skips the duplicate UPDATE. InnoDB’s modify interface is invoked but the engine returns without making changes.
4. 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, applying the change (e.g., setting the value to (1,999)), acquiring necessary locks and performing the update as usual.
Java Captain
Focused on Java technologies: SSM, the Spring ecosystem, microservices, MySQL, MyCat, clustering, distributed systems, middleware, Linux, networking, multithreading; occasionally covers DevOps tools like Jenkins, Nexus, Docker, ELK; shares practical tech insights and is dedicated to full‑stack Java development.
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.