REPLACE into with INSERT ... On DUPLICATE KEY UPDATE

In terms of efficiency, INSERT ... On DUPLICATE KEY UPDATE is better than replace, after all, replace deletes and inserts if repeated. And replace also has side effects: 1. Replace each time to reassign the ID; 2. When a delete is executed in replace, it can be cumbersome to have a foreign key; 3. If a trigger is defined on delete, it is executed; 4. Side effects can also be spread to replica slave.

But these two are also more efficient than you were before, think also know, before you, select, in the program to make judgments, and then do a database operation. Update On DUPLICATE KEY UPDATE does not change the self-increasing ID; Update multiple columns, simple, is on duplicate key update col1=7, col2=8;

Experiment:

Mysql> CREATE TABLE rep_vs_up (ID int primary key auto_increment, an int unique, b int, c int, d int);
Query OK, 0 rows affected (0.09 sec)


mysql> INSERT INTO REP_VS_UP (a,b,c,d) values (1,2,3,4);
Query OK, 1 row affected (0.01 sec)


Mysql> select * from Rep_vs_up;
+----+------+------+------+------+
| ID | A | B | C | D |
+----+------+------+------+------+
|    1 |    1 |    2 |    3 | 4 |
+----+------+------+------+------+
1 row in Set (0.00 sec)


Mysql> Replace into rep_vs_up (a,b,c) values (1,3,4);
Query OK, 2 rows affected (0.01 sec)


Mysql> select * from Rep_vs_up;
+----+------+------+------+------+
| ID | A | B | C | D |
+----+------+------+------+------+
|    2 |    1 |    3 | 4 | NULL |
+----+------+------+------+------+
1 row in Set (0.00 sec)


mysql> INSERT into Rep_vs_up (A, B, C, D) VALUES (1,6,7,8) on duplicate key update c=7, d=8;
Query OK, 2 rows affected (0.01 sec)


Mysql> select * from Rep_vs_up;
+----+------+------+------+------+
| ID | A | B | C | D |
+----+------+------+------+------+
|    2 |    1 |    3 |    7 | 8 |
+----+------+------+------+------+
1 row in Set (0.00 sec)