MySQL的Replaceinto与Insertintoonduplicatekeyupdate真正的不同之处

转自：http://www.jb51.net/article/47090.htm

今天，我听了同事的介绍oracle到mysql的数据migration，他用了Insert into ..... on duplicate key update ...当时我想为什么不呢Replace所以回来的时候，我仔细检查了一下，发现它们之间还是有区别的， 让我们看一下以下示例：

1 Replace into ...
1.1 输入原始数据
mysql> use test;
Database changed
mysql>

mysql> CREATE TABLE t1 SELECT 1 AS a, c3 AS b, c2 AS c;
ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_INCREMENT ;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT 2,2, 3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t1(b,c) select r2,r3;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

1.2 开始replace操作
mysql> REPLACE INTO t1(a,b) VALUES(2,a) ;
Query OK, 2 rows affected (0.06 sec)

看着这里，replace看这里，a=2的记录中c该字段为空字符串，
所以当与key冲突时，replace覆盖相关字段并填写其他字段的默认值，可以理解为删除重复项key记录，插入新记录，adelete原始记录insert的操作 。

1.3 但我不知道主键auto_increment有没有影响？接下来，让我们测试一下：

mysql> insert into t1(b,c) select r4,r5; 
Query OK, 1 row affected (0.05 sec) 
Records: 1 Duplicates: 0 Warnings: 0 

mysql> select * from t1; 
+---+----+----+ 
| a | b | c | 
+---+----+----+ 
| 1 | c3 | c2 | 
| 2 | a | | 
| 3 | r2 | r3 | 
| 5 | r4 | r5 | 
+---+----+----+ 
4 rows in set (0.00 sec)

从这里可以看出，新的自我增量并不是从4开始，但从5一开始，它意味着repalce操作，在主键中auto_increment会累加1.
所以总结如下：
Replace:

当没有key时，replace相当于普通insert.
当有key什么时候，可以理解为删除重复项key保留记录key在同样的情况下，delete原始记录，然后insert新记录，将仅输入新记录的值replace语句中字段的值不包括在replace语句中的字段将自动填充默认值。

2.1 ok，再来看Insert into ..... on duplicate key update，

mysql> insert into t1(a,b) select 3,r5 on duplicate key update b=r5; 
Query OK, 2 rows affected, 1 warning (0.19 sec) 
Records: 1 Duplicates: 1 Warnings: 1 

mysql> select * from t1; 
+---+----+----+ 
| a | b | c | 
+---+----+----+ 
| 1 | c3 | c2 | 
| 2 | a | | 
| 3 | r5 | r3 | 
| 5 | r4 | r5 | 
+---+----+----+ 
4 rows in set (0.00 sec)

【】a=5当时，原版c该值仍然存在，这意味着当key有时，只执行后续的udate操作语句.

2.2 再检查auto_increment情况。

mysql> insert into t1(a,b) select 3,r5 on duplicate key update b=r5; 
Query OK, 2 rows affected, 1 warning (0.19 sec) 
Records: 1 Duplicates: 1 Warnings: 1 

mysql> select * from t1; 
+---+----+----+ 
| a | b | c | 
+---+----+----+ 
| 1 | c3 | c2 | 
| 2 | a | | 
| 3 | r5 | r3 | 
| 5 | r4 | r5 | 
+---+----+----+ 
4 rows in set (0.00 sec) 

mysql> insert into t1(b,c) select r6,r7; 
Query OK, 1 row affected (0.19 sec) 
Records: 1 Duplicates: 0 Warnings: 0 

mysql> select * from t1; 
+---+----+----+ 
| a | b | c | 
+---+----+----+ 
| 1 | c3 | c2 | 
| 2 | a | | 
| 3 | r5 | r3 | 
| 5 | r4 | r5 | 
| 7 | r6 | r7 | 
+---+----+----+ 
5 rows in set (0.00 sec) 

从这里可以看出，新的自我增量并不是从6开始，但从7一开始，它意味着Insert .. on deplicate udate操作，在主键中auto_increment也跟replace一样累加1 .

2.3 让我们仔细看看key的时候，insert .. on deplicate update的情况

mysql> insert into t1(a,b,c) select 33,r5,c3 on duplicate key update b=r5; 
Query OK, 1 row affected, 1 warning (0.23 sec) 
Records: 1 Duplicates: 0 Warnings: 1 

mysql> select * from t1; 
+----+----+----+ 
| a | b | c | 
+----+----+----+ 
| 1 | c3 | c2 | 
| 2 | a | | 
| 3 | b5 | r3 | 
| 5 | r4 | r5 | 
| 7 | r6 | r7 | 
| 9 | s6 | s7 | 
| 33 | r5 | c3 | 
+----+----+----+ 
7 rows in set (0.00 sec)

看a=33的记录，ok全部已输入。

3 从上面的测试结果可以得出结论，有相似之处：
（1），没有key的时候，replace与insert .. on deplicate udpate相同。
（2），有key维护主键值时，以及auto_increment自动+1
差异：是key的时候，replace是delete旧记录，但输入新记录，因此将清除所有现有记录。此时，如果replace如果语句的字段不完整，则某些原始字段，例如示例中的字段c该字段的值将自动填充为默认值。
而insert .. deplicate update则只执行update标记后sql从表面上看，相当于一个简单的update语句。
但事实上，根据我的猜测，如果很简单update语句，auto_increment不会+1也应该先完成delete，再insert该操作仅在insert在保留分工的过程中update除以下字段之外的所有字段的值。

所以两者之间只有一个区别， insert .. on deplicate udpate保留所有字段的旧值，然后将它们叠加在一起insert进去，而replace没有保留旧值，直接删除它们，然后insert新值。
从底层执行效率来看，replace要比insert .. on deplicate update高效，但以书面形式replace执行此操作时，应完整写入字段，以防止删除旧字段数据。

个人倾向和使用Replace。