十二月 11th, 2017

replace into和insert into on duplicate key 区别


replace的用法

当不冲突时相当于insert,其余列默认值
当key冲突时,自增列更新,replace冲突列,其余列默认值

Insert into …on duplicate key的用法

不冲突时相当于insert,其余列默认值
当与key冲突时,只update相应字段值。

实验展示


表结构

create table helei1(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL DEFAULT ”,
age tinyint(3) unsigned NOT NULL default 0,
PRIMARY KEY(id),
UNIQUE KEY uk_name (name)
)
ENGINE=innodb AUTO_INCREMENT=1
DEFAULT CHARSET=utf8;

表数据

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 1 | 贺磊 | 26 |
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
+—-+———–+—–+
3 rows in set (0.00 sec)

replace into用法

root@127.0.0.1 (helei)> replace into helei1 (name) values(‘贺磊’);
Query OK, 2 rows affected (0.00 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 0 |
+—-+———–+—–+
3 rows in set (0.00 sec)
root@127.0.0.1 (helei)> replace into helei1 (name) values(‘爱璇’);
Query OK, 1 row affected (0.00 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 0 |
| 5 | 爱璇 | 0 |
+—-+———–+—–+
4 rows in set (0.00 sec)

replace的用法
当没有key冲突时,replace into 相当于insert,其余列默认值
当key冲突时,自增列更新,replace冲突列,其余列默认值

Insert into …on duplicate key:

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 0 |
| 5 | 爱璇 | 0 |
+—-+———–+—–+
4 rows in set (0.00 sec)

root@127.0.0.1 (helei)> insert into helei1 (name,age) values(‘贺磊’,0) on duplicate key update age=100;
Query OK, 2 rows affected (0.00 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 100 |
| 5 | 爱璇 | 0 |
+—-+———–+—–+
4 rows in set (0.00 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 100 |
| 5 | 爱璇 | 0 |
+—-+———–+—–+
4 rows in set (0.00 sec)

root@127.0.0.1 (helei)> insert into helei1 (name) values(‘爱璇’) on duplicate key update age=120;
Query OK, 2 rows affected (0.01 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 100 |
| 5 | 爱璇 | 120 |
+—-+———–+—–+
4 rows in set (0.00 sec)

root@127.0.0.1 (helei)> insert into helei1 (name) values(‘不存在’) on duplicate key update age=80;
Query OK, 1 row affected (0.00 sec)

root@127.0.0.1 (helei)> select * from helei1;
+—-+———–+—–+
| id | name | age |
+—-+———–+—–+
| 2 | 小明 | 28 |
| 3 | 小红 | 26 |
| 4 | 贺磊 | 100 |
| 5 | 爱璇 | 120 |
| 8 | 不存在 | 0 |
+—-+———–+—–+
5 rows in set (0.00 sec)

总结


replace into这种用法,相当于如果发现冲突键,先做一个delete操作,再做一个insert 操作,未指定的列使用默认值,这种情况会导致自增主键产生变化,如果表中存在外键或者业务逻辑上依赖主键,那么会出现异常。因此建议使用Insert into …on duplicate key。由于编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。

TAGS:

Comments are closed.