本文实例讲述了mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点。分享给大家供大家参考，具体如下：
replace into和insert into on duplicate key update都是为了解决我们平时的一个问题
就是如果数据库中存在了该条记录，就更新记录中的数据，没有，则添加记录。
我们创建一个测试表test
|
1
2
3
4
5
6
|
create table `test` ( `id` int(11) unsigned not null auto_increment comment 'id', `name` varchar(32) default '' comment '姓名', `addr` varchar(256) default '' comment '地址', primary key (`id`)) engine=innodb default charset=utf8; |
向该表中插入一些数据
|
1
2
3
4
5
6
|
insert into testvalues (null, 'a', 'aaa'), (null, 'b', 'bbb'), (null, 'c', 'ccc'), (null, 'd', 'ddd'); |
影响行数4，结果如下：
我们运行如下语句：
|
1
|
replace into test values(null, 'e', 'eee'); |
结果显示，影响行数1条，记录被插入成功了
注意上面的语句，我们并没有填写主键id。
然后我们再执行下面的语句：
|
1
|
replace into test values(1, 'aa', 'aaaa'); |
结果显示，影响行数2条，id为1的记录被更新成功了
为什么会出现这种情况，原因就是replace into会首先尝试先往表里面插入记录，因为我们的id是主键，不可重复，显然这条记录是无法插入成功的，然后replace into会把这条已存在的记录删掉，然后再插入，所以会显示影响行数是2。
我们再运行下面这条语句：
|
1
|
replace into test(id,name) values(1, 'aaa'); |
这里我们只指定id,name字段，我们来看看replace into后addr字段内容是否还存在
显然addr字段内容没有了，跟我们上面的分析是一致的，reaplce into先删除了id为1的记录，然后再插入记录，但我们并没有指定addr的值，所以会如上图所示那样。
但是有些时候我们的需求是，如果记录存在则更新指定字段的数据，原有字段数据仍保留，而不是上面所示的，addr字段数据没有了。
这里就需要用到insert into on duplicate key update
执行如下语句：
|
1
2
3
4
5
|
insert into test (id, name)values(2, 'bb') on duplicate keyupdatename = values(name); |
values(字段名)表示获取当前语句insert的列值，values(name)表示的就是'bb'
结果显示，影响行数2条
如上图所示，addr字段的值被保留了。
insert into on duplicate key update语句的做法是先插入记录，如果不成功，则更新记录，但是为什么影响的行数是2？
我们重新建一张表test2
|
1
2
3
4
5
6
7
8
|
create table `test2` ( `id` int(11) unsigned not null auto_increment comment 'id', `sn` varchar(32) default '' comment '唯一键', `name` varchar(32) default '' comment '姓名', `addr` varchar(256) default '' comment '地址', primary key (`id`), unique key `sn` (`sn`)) engine=innodb default charset=utf8; |
往里面插入点数据
|
1
2
3
4
5
6
|
insert into test2values (null, '01', 'a', 'aaa'), (null, '02', 'b', 'bbb'), (null, '03', 'c', 'ccc'), (null, '04', 'd', 'ddd'); |
我们运行如下语句：
|
1
2
3
4
5
6
7
|
insert into test2 (sn, name, addr)values ('02', 'bb', 'bbbb')on duplicate keyupdatename = values(name),addr = values(addr); |
结果如下：
每运行一次上面的语句，虽然影响行数为0，但表test2的自增字段就加1。
显然如果insert into on duplicate key update语句仅仅只是在原记录基础上进行更新操作的话，自增字段是不会自动加1的，说明它也进行了记录删除操作。
先插入记录，如果不成功，则删除原记录，但保留了除update语句后字段的值，然后把保留的值与需要更新的值合并，然后插入一条新记录。
总结：
replace into 与 insert into on duplicate key update都是先尝试插入记录，如果不成功，则删除记录，replace into不保留原记录的值，而insert into on duplicate key update保留。然后插入一条新记录。希望本文所述对大家MySQL数据库计有所帮助。
原文链接：https://www.cnblogs.com/jkko123/p/7285641.html
