mysql - REPLACE INTO的问题

Question

某种应用场景下我需要SELECT判断是否存在数据，如果存在就UPDATE。不存在就INSERT INTO。

为了提高效率我直接用了REPLACE INTO替换了上面3条语句。但是其中的某一个字段我不需要覆盖，之前的方法还可行，现在用了REPLACE INTO却实现不了了。

请教大家这种情况下效率最好的方法是什么

PHP中文网 · Answer

对于你的场景来说, 最好的是 INSERT ... ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

比如三个字段(A, B, C), A为唯一索引, 重复的情况下你不想改动B, 则:

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

效率上来说, INSERT ... ON DUPLICATE KEY UPDATE 比replace 要好, 毕竟replace如果重复则先删除再插入. 而且replace还有副作用: 1. replace每次要重新分配自增id; 2. replace中执行delete时, 在有外键的情况下会很麻烦; 3. 如果delete时定义的有触发器, 则会被执行; 4. 副作用也会被传播到replica slave.

但是这两个怎么也比你之前的效率高, 想想也知道, 你之前, 先select, 在程序里做判断, 然后再做一个数据库操作.

更新

ON DUPLICATE KEY UPDATE 不会改变自增id;
更新多个列, 简单, 就是on duplicate key update col1=7, col2=8;

实验:

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>

PHP中文网 · Answer

官方文档：

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 匹配到条件以后会先delete然后insert，而不是执行update，效率较update低很多

还是建议使用你之前的做法，这样效率高

大家讲道理 · Answer

补充一下 @brayden 的答案 1. ON DUPLICATE KEY UPDATE 每次也会重新分配一个自增id（如果存在自增字段）。 2. REPLACE INTO 会把所有字段覆盖，SQL语句中没有指明值的置为默认值。但是 ON DUPLICATE KEY UPDATE 不会覆盖 sql 中没有指明的字段，只会更新原有字段。