MySQL Update Behavior with Identical Data under Different binlog_format Settings

1. Background

This article tests whether MySQL re‑executes an UPDATE statement that sets a column to the same value it already holds, i.e., whether the statement is internally executed when the new data equals the original data.

2. Test Environment

MySQL 5.7.25

CentOS 7.4

3. binlog_format = ROW

Parameters

Test Steps

session1

session2

session1

Summary

When binlog_format=row and binlog_row_image=FULL, MySQL records all fields in the binlog, reads all data, and therefore skips executing an UPDATE that would not change any values; the InnoDB engine receives the “modify to (1,55)” request, detects no change, and returns without updating.

4. binlog_format = STATEMENT

Parameters

Test Steps

session1

session2

session1

Summary

When binlog_format=statement and binlog_row_image=FULL, InnoDB fully executes the UPDATE, performing the lock and the actual data change (e.g., modifying the value to (1,999)).