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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `uniq_b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Replace a duplicate row:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 2 | | 2 | 2 | NULL | +---+------+------+ |
|
1 2 3 |
mysql> replace into t1(b,c) values (1,9); Query OK, 2 rows affected (0.07 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 2 | 2 | NULL | | 3 | 1 | 9 | +---+------+------+ |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 2 | | 2 | 2 | NULL | +---+------+------+ |
|
1 2 3 |
mysql> replace into t1(b) values (1); Query OK, 2 rows affected (0.06 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 2 | 2 | NULL | | 3 | 1 | NULL | +---+------+------+ |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 2 | 2 | 2 | | 3 | 1 | 2 | +---+------+------+ |
|
1 2 3 |
mysql> replace into t1(a,b,c) values (2,1,9); Query OK, 3 rows affected (0.10 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 2 | 1 | 9 | +---+------+------+ |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 2 | | 2 | 2 | NULL | +---+------+------+ |
|
1 2 3 |
mysql> insert into t1(a,b,c) values (2,3,4) on duplicate key update c=4; Query OK, 2 rows affected (0.02 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 2 | | 2 | 2 | 4 | +---+------+------+ |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 2 | | 2 | 2 | 4 | +---+------+------+ |
|
1 2 3 |
mysql> insert into t1(b,c) values (1,5) on duplicate key update c=5; Query OK, 2 rows affected (0.20 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t1; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 1 | 5 | | 2 | 2 | 4 | +---+------+------+ |
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.
