Tips when using ‘replace into’ and ‘insert … on duplicate key update…’ in MySQL

Sometimes when we want to insert rows into a table which contains duplicate keys, then we consider using ‘replace into’ or ‘insert into … on duplicate key update …’. In my recent work, i found that there were some traps when using them, and this article is to show what will happen and why.

1) replace into

Replace a duplicate row:

After we used ‘replace into …’ on the unique key, the (1,1,2) was deleted and a new row (3,1,9) was inserted, it’s not just replaced values at the (1,1,2) row because the column ‘a’ used the AUTO_INCREMENT value ‘3’.

Column name must be specified:

If you didn’t specify the column name, for example:

Column ‘a’ and ‘c’ have been changed into ‘3’ and ‘null’, so when using ‘replace into’, it will delete the duplicate row and insert a new row with the default value.

Replace a duplicate primary key and a duplicate unique key at the same time:

After ‘replace into …’, it shows ‘3 rows affected ‘, two rows have been deleted and just one row is inserted into t1.

As mentioned before, ‘replace into ..’ will delete the duplicate rows before inserting, so when the ‘replace into t1(a,b,c) values (2,1,9);’ is executing, it checks the primary key first, it’s duplicated, so the (2,2,2) is deleted, then the column ‘b’ is also duplicated, so the (3,1,2) is deleted, after that, no duplicate key exists, then insert a new row (2,1,9).

2) Insert into … on duplicate key update …

Using the same table t1 for testing:

Insert a duplicate primary key row:

The ‘insert … on duplicate key update…’ just checked the primary key, and after found the primary key ‘a’ had a duplicate value ‘2’, then it updated the column ‘c’ to new value ‘4’, and ignored the column ‘b’ which was also duplicate. So we got the new line (2,2,4) instead of (2,3,4).

Insert on a duplicate key row:

This is different with ‘replace into ..’, it just updates the value on the duplicate row, with no deletion.

In a word, before using ‘replace into…’ and ‘insert into … on duplicate key update …’, you must know what they actually do, otherwise you will get unexpected results.