Do Identical UPDATE Statements Trigger MySQL Binlog Writes? A Practical Test
This article experimentally investigates whether MySQL re‑executes UPDATE statements that set a column to its current value, comparing behavior under ROW and STATEMENT binlog formats and revealing how InnoDB handles redundant updates.
Background
This article tests whether MySQL re‑executes an UPDATE statement when the new value is identical to the existing one.
Test Environment
MySQL 5.7.25 on 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 the data pulls all columns; however, an UPDATE that does not change any values is skipped. InnoDB receives the “modify to (1,55)” request, detects no change, and returns without updating.
binlog_format = STATEMENT
Parameters
Test Steps
Session 1:
Session 2:
Session 1 (repeat):
Summary
With binlog_format=statement and binlog_row_image=FULL , InnoDB fully executes the UPDATE, performing the lock and the data change (e.g., modifying 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.
Java Backend Technology
Focus on Java-related technologies: SSM, Spring ecosystem, microservices, MySQL, MyCat, clustering, distributed systems, middleware, Linux, networking, multithreading. Occasionally cover DevOps tools like Jenkins, Nexus, Docker, and ELK. Also share technical insights from time to time, committed to Java full-stack 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.