mysql中的replace into和 on duplicate key update

mysql中有replace into 和 on duplicate key update 实现类似oracle merge into的功能，经过实现测试on duplicate key update的性能更高，另外replace into的实现操作是先删除再插入，所以会造成其他列的数据丢失。所以实际使用中，不建议使用replace into,而是on duplicate key update.

一。基本测试

create table t1 (
id int primary key auto_increment,
name varchar(30),
lastupdate timestamp not null default current_timestamp on update current_timestamp
);

1.测试replace into 

mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing  | 2015-08-24 10:16:29 |
|  5 | qing  | 2015-08-24 10:18:31 |
+----+-------+---------------------+
3 rows in set (0.00 sec)


mysql> replace into t1(id,name) values(3,'qing');
Query OK, 2 rows affected (0.03 sec)


mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing  | 2015-08-24 10:19:13 |
|  5 | qing  | 2015-08-24 10:18:31 |
+----+-------+---------------------+
3 rows in set (0.00 sec)


由上可见不即使新修改的值是一样，还是实现做了update操作。

2.测试on duplicate key update相同值的更新。

mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing  | 2015-08-24 10:19:13 |
|  5 | qing  | 2015-08-24 10:18:31 |
+----+-------+---------------------+
3 rows in set (0.00 sec)
mysql> insert into t1 (id,name) values(3,'qing') on duplicate key update id=values(id),name=values(name);
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing  | 2015-08-24 10:19:13 |
|  5 | qing  | 2015-08-24 10:18:31 |
+----+-------+---------------------+
3 rows in set (0.00 sec)


从以上看出值相同时，数据库里的时间列并没有发生修改。

3.测试on duplicate key update不同值的更新。

mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing  | 2015-08-24 10:19:13 |
|  5 | qing  | 2015-08-24 10:18:31 |
|  7 | qing  | 2015-08-24 10:25:58 |
|  9 | qing  | 2015-08-24 10:27:17 |
+----+-------+---------------------+
5 rows in set (0.00 sec)


mysql> insert into t1 (id,name) values(3,'qing1') on duplicate key update id=values(id),name=values(name);
Query OK, 2 rows affected (0.01 sec)


mysql> select * from t1;
+----+-------+---------------------+
| id | name  | lastupdate          |
+----+-------+---------------------+
|  1 | tanqr | 2015-08-24 09:59:28 |
|  3 | qing1 | 2015-08-24 10:28:44 |
|  5 | qing  | 2015-08-24 10:18:31 |
|  7 | qing  | 2015-08-24 10:25:58 |
|  9 | qing  | 2015-08-24 10:27:17 |
+----+-------+---------------------+
5 rows in set (0.00 sec)


时间只变化了有变化的列。

4.on duplicate key  update的更多用法 ：

mysql> insert into t3 select * from t2 on duplicate key update id =values(id),name=values(name);
mysql> insert into t1 (id,name) values(11,'qing1'),(12,'ru') on duplicate key update id=values(id),name=values(name);

二。replace into 和on duplicate key update是否会造成数据丢失的比较。

mysql> alter table t1 add column city varchar(30);
mysql> insert into t1(name,city) values('tan','bj');
mysql> insert into t1(name,city) values('tan1','bj1');
mysql> insert into t1(name,city) values('tan1','bj2');
mysql> insert into t1(name,city) values('tan1','bj3');


mysql> select * from t1;
+--------+------+---------------------+------+
| id     | name | lastupdate          | city |
+--------+------+---------------------+------+
| 262113 | tan  | 2015-08-24 11:01:36 | bj   |
| 262115 | tan1 | 2015-08-24 11:02:16 | bj1  |
| 262117 | tan1 | 2015-08-24 11:02:19 | bj2  |
| 262119 | tan1 | 2015-08-24 11:02:21 | bj3  |
+--------+------+---------------------+------+
4 rows in set (0.00 sec)


mysql> replace into t1(id,name) value(262115,'tan1');
Query OK, 2 rows affected (0.08 sec)




mysql> select * from t1;
+--------+------+---------------------+------+
| id     | name | lastupdate          | city |
+--------+------+---------------------+------+
| 262113 | tan  | 2015-08-24 11:01:36 | bj   |
| 262115 | tan1 | 2015-08-24 11:03:29 | NULL |
| 262117 | tan1 | 2015-08-24 11:02:19 | bj2  |
| 262119 | tan1 | 2015-08-24 11:02:21 | bj3  |
+--------+------+---------------------+------+
4 rows in set (0.00 sec)


mysql> insert into t1(id,name)values(262117,'tan1') 
    -> on duplicate key update id=values(id),name=values(name);
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t1;
+--------+------+---------------------+------+
| id     | name | lastupdate          | city |
+--------+------+---------------------+------+
| 262113 | tan  | 2015-08-24 11:01:36 | bj   |
| 262115 | tan1 | 2015-08-24 11:03:29 | NULL |
| 262117 | tan1 | 2015-08-24 11:02:19 | bj2  |
| 262119 | tan1 | 2015-08-24 11:02:21 | bj3  |
+--------+------+---------------------+------+
4 rows in set (0.00 sec)


mysql> insert into t1(id,name)values(262117,'tan11')  on duplicate key update id=values(id),name=values(name);
Query OK, 2 rows affected (0.00 sec)


mysql> select * from t1;
+--------+-------+---------------------+------+
| id     | name  | lastupdate          | city |
+--------+-------+---------------------+------+
| 262113 | tan   | 2015-08-24 11:01:36 | bj   |
| 262115 | tan1  | 2015-08-24 11:03:29 | NULL |
| 262117 | tan11 | 2015-08-24 11:04:57 | bj2  |
| 262119 | tan1  | 2015-08-24 11:02:21 | bj3  |
+--------+-------+---------------------+------+
4 rows in set (0.00 sec)