REPLACE INTO vs ON DUPLICATE KEY UPDATE

REPLACE INTO my_table (col_1, col_2, col_3, col_4) VALUES 
('row1_valA', 'row1_valB', 'row1_valC', 'row1_valD'),
('row2_valA', 'row2_valB', 'row2_valC', 'row2_valD');

INSERT INTO my_table (col_1, col_2, col_3, col_4) VALUES
('row1_valA', 'row1_valB', 'row1_valC', 'row1_valD'),
('row2_valA', 'row2_valB', 'row2_valC', 'row2_valD')
ON DUPLICATE KEY UPDATE col_1 = 'fixed_value', col_2 = VALUES(col_2), col_3 = col_3, col_4 = DEFAULT(col_4);
-- Work also with INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE ...

Quickly, ON DUPLICATE KEY UPDATE win! :

doesn't destroy foreign key child (delete cascade), and doesn't call trigger on delete action
seems to be faster (because statement is an update instead the dual delete and insert statement)

On the Web

INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO

INSERT ON DUPLICATE UPDATE is what I need to use, especially knowing the cascading keys issue.

In MySQL using InnoDB, which is better for performance: REPLACE INTO or INSERT ON DUPLICATE KEY UPDATE?

Insert on Duplicate Key update is almost 32 times faster in my case

In MySQL, when should I use "REPLACE INTO" versus "INSERT ... ON DUPLICATE KEY UPDATE"?

INSERT ... ON DUPLICATE KEY UPDATE on the other hand, is much more straightforward and contains fewer gotchas.

Difference Between Mysql Replace and Insert on Duplicate Key Update

REPLACE = DELETE+INSERT
INSERT ON DUPLICATE KEY UPDATE = UPDATE + INSERT

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
Inserting a NULL into a column with a NOT NULL constraint.
Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

A new auto-increment ID is allocated.
Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
Triggers that fire on DELETE are executed unnecessarily.
Side effects are propagated to replication slaves too.

MariaDB

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

The VALUES() function can only be used in a ON DUPLICATE KEY UPDATE clause and has no meaning in any other context. It returns the column values from the INSERT portion of the statement. This function is particularly useful for multi-rows inserts.

MySQL

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Both, REPLACE and INSERT ... ON DUPLICATE KEY UPDATE are a MySQL extension to the SQL standard.

Use case

CREATE TABLE IF NOT EXISTS `table_insert_on_duplicate_update` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `n` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`,`b`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

INSERT INTO table_insert_on_duplicate_update (id, c, d) VALUES
(NULL, NULL, 'toto');
-- 1

INSERT INTO table_insert_on_duplicate_update (id, c, d) VALUES
(NULL, NULL, 'toto');
-- 2

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(NULL, 'b', NULL, 'toto');
-- 3

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(NULL, 'b', NULL, 'toto');
-- 4

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(NULL, 'b', 'c', 'toto');
-- 5

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(NULL, 'b', 'c', 'toto');
-- #1062 - Duplicate entry 'c' for key 'c'

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(1, 'b', 'c', 'toto');
-- #1062 - Duplicate entry '1' for key 'PRIMARY'

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(1, 'b', 'c', 'toto')
ON DUPLICATE KEY UPDATE `d`='titi';
-- 2 inserted lines.
-- only first unique key matching has values changed (here is the PK, is the PK the first key read from unique keys ?)

INSERT INTO table_insert_on_duplicate_update (id, b, c, d) VALUES
(9, 'b', 'c', 'toto')
ON DUPLICATE KEY UPDATE `d`='riri';
-- 2 inserted lines.
-- here the PK value is ignored, beacause unique key [a,b] already exist

On a composite key with NULL values, it ignores that key.
In case of several keys, with several rows, it seems to take the PK first, then the other unique ones, but only update one row.

PlaceOweb Dotclear