MySQL INSERT ON DUPLICATE KEY UPDATE ... return "rows affected: 1", but no changes really

Question

I create table with column which have default value '1'

create table int_1 (
  id int not null auto_increment,
  value int default 1,
  primary key (id)
);

command

insert into int_1 values(1, null) on duplicate key update value = null;

always return result

1 row(s) affected

but record really changed (inserted) after first execution. But if i create table with default value '0', all works as expected:

create table int_0 (
  id int not null auto_increment,
  value int default 0,
  primary key (id)
);
insert into int_0 values(1, null) on duplicate key update value = null;

return

0 row(s) affected

after second run "insert on duplicate key"

Checked on MySQL 5.7, 8.0

Is there an explanation for this behavior?

Answer 1

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

-- https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Answer 2

This is an interesting thing that I found recently too in MySQL 5.7. If you purposely try to insert a row that already exists using on duplicate key update, this query will also duplicate the number of rows affected.

For instance, run these queries:

CREATE TABLE int_0 (
 id INT NOT NULL AUTO_INCREMENT,
 VALUE INT DEFAULT 0,
 PRIMARY KEY (id) 
);

INSERT INTO int_0 VALUES(1, NULL) ON DUPLICATE KEY UPDATE VALUE = NULL;

Then run this:

INSERT INTO int_0 VALUES(1, 2) ON DUPLICATE KEY UPDATE VALUE = 2;

This query will show '2 rows affected' even though there is only 1 row in the table and affecting 2 rows is therefore impossible.

My theory is that it is trying to count the number of rows affected by the insert and the update, then adding them together. Somehow the ON DUPLICATE KEY UPDATE being triggered is getting counted as a row being 'affected' by the insert. I believe this is the behavior that you are seeing.