mysql中有replace into 和 on duplicate key update 实现类似oracle merge into的功能,经过实现测试on duplicate key update的性能更高,另外replace into的实现操作是先删除再插入,所以会造成其他列的数据丢失。所以实际使用中,不建议使用replace into,而是on duplicate key update.
+----+-------+---------------------+
| 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操作。
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)
从以上看出值相同时,数据库里的时间列并没有发生修改。
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)
时间只变化了有变化的列。
mysql> insert into t1 (id,name) values(11,'qing1'),(12,'ru') on duplicate key update id=values(id),name=values(name);
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)
一。基本测试
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)