does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed?

Question

Let me explain :

Let us assume we have a database "user" :

ID   Name   Surname
===================
1    John   Doe
2    Foo    Bar
3    Foo    Baz

Now I wanna change all these three rows. I will throw the following insert :

INSERT INTO user (ID, Name, Surname) VALUES (1, 'John', 'Conor') ON DUPLICATE KEY UPDATE ID = 1, Name = 'John', Surname = 'Conor';
INSERT INTO user (ID, Name, Surname) VALUES (2, 'Foo', 'Bar') ON DUPLICATE KEY UPDATE ID = 2, Name = 'Foo', Surname = 'Bar';
INSERT INTO user (ID, Name, Surname) VALUES (3, 'Foo', 'Baz') ON DUPLICATE KEY UPDATE ID = 3, Name = 'Foo', Surname = 'Baz';

Question

Does the command ON DUPLICATE KEY execute itself if the row before the update was the same ? Meaning, does it will execute for the rows with ID equal to 2 and 3 even if it should not executes (because it is useless) ? I ask this question for performance issues.

Answer 1

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:

It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does not do any update. It still has to check them though.

So the result in your case (where you send 3 rows to be inserted) would be:

• 3 rows to be inserted
• 3 key collisions
  • 0 rows inserted
  • 3 to be updated
    • 1 row actually updated
    • 2 identical (no update)

---

A few things about your syntax:

• you don't need to update the UNIQUE key.
• you can use VALUES(column) in the UPDATE part.

• you can combine the multiple inserts into one:

  INSERT INTO user 
    (ID, Name, Surname) 
  VALUES 
    (1, 'John', 'Conor'),
    (2, 'Foo',  'Bar'),
    (3, 'Foo',  'Baz') 
  ON DUPLICATE KEY UPDATE 
    Name = VALUES(Name), 
    Surname = VALUES(Surname) ;