For your scenario, the best thing is INSERT ... ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

For example, there are three fields (A, B, C), A is the unique index, and you don’t want to change B if there are duplicates, then:

insert into table1(A, B, C) values(1,2,3) on duplicate key update C=3;

In terms of efficiency, INSERT ... ON DUPLICATE KEY UPDATE is better than replace. After all, if replace is repeated, it will be deleted first and then inserted. Moreover, replace has side effects: 1. Replace needs to reassign the auto-incrementing ID every time; 2. When executing delete in replace, it will be very troublesome when there are foreign keys; 3. If there is a trigger defined when deleting, it will be executed; 4. Side effects will also be propagated to replica slaves.

But these two are more efficient than what you did before. Think about it and you will know that before, you first selected, made judgments in the program, and then performed a database operation.

Update

1. ON DUPLICATE KEY UPDATE will not change the auto-incremented id;
2. Update multiple columns, simple, just on duplicate key update col1=7, col2=8;

Experiment:

mysql> create table rep_vs_up(id int primary key auto_increment, a 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)

mysql>

Official documentation:

Keep in mind that a REPLACE INTO requires a test on the keys, and if a matching unique key is found on any or all columns, a DELETE FROM is executed, then an INSERT is executed.

replace into will first delete and then insert after matching the condition, instead of executing update, which is much less efficient than update

It is still recommended to use your previous method, which is more efficient

Additional to @brayden's answer 1. ON DUPLICATE KEY UPDATE will also reassign an auto-increment id each time (if there is an auto-increment field). 2. REPLACE INTO will overwrite all fields and set the default values ​​if no values ​​are specified in the SQL statement. However, ON DUPLICATE KEY UPDATE will not overwrite fields not specified in sql, but will only update the original fields.