MySQL的Replace into与Insert into...on duplicate key update...真正的不同之处
今天听同事介绍oracle到
mysql的数据migration，他用了Insert into ..... on duplicate key update ...，我当时就想怎么不用Replace呢，于是回来就仔细查了下，它们果然还是有区别的，看下面的例子吧：
www.2cto.com
1 Replace into ...
1.1 录入原始数据
mysql> use test;
Database changed
mysql>
mysql>
mysql> show tables; www.2cto.com
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
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> SELECT * FROM t1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c3 | c2 |
+---+----+----+
1 row in set (0.00 sec)
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
mysql> select * from t1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c3 | c2 |
| 2 | 2 | 3 |
| 3 | r2 | r3 |
+---+----+----+
3 rows in set (0.00 sec)
1.2 开始replace操作
mysql> REPLACE INTO t1(a,b) VALUES(2,'a') ;
Query OK, 2 rows affected (0.06 sec)
mysql> select * from t1;
+---+----+----+
| a | b | c |
+---+----+----+
| 1 | c3 | c2 |
| 2 | a | |
| 3 | r2 | r3 |
+---+----+----+
3 rows in set (0.00 sec)
【】看到这里，replace，看到这里，a=2的记录中c字段是空串了，
所以当与key冲突时，replace覆盖相关字段，其它字段填充默认值，可以理解为删除重复key的记录，新插入一条记录，一个delete原有记录再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 |
+---+---