replace into和insert into on duplicate key 区别

replace的用法

当不冲突时相当于insert，其余列默认值
当key冲突时，自增列更新，replace冲突列，其余列默认值
Com_replace会加1
Innodb_rows_updated会加1

Insert into …on duplicate key的用法

不冲突时相当于insert，其余列默认值
当与key冲突时，只update相应字段值。
Com_insert会加1
Innodb_rows_inserted会增加1

实验展示

表结构

createtablehelei1(idint(10)unsignedNOTNULLAUTO_INCREMENT,namevarchar(20)NOTNULLDEFAULT'',agetinyint(3)unsignedNOTNULLdefault0,PRIMARYKEY(id),UNIQUEKEYuk_name(name))ENGINE=innodbAUTO_INCREMENT=1DEFAULTCHARSET=utf8;

表数据

root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|1|贺磊|26||2|小明|28||3|小红|26|+----+-----------+-----+3rowsinset(0.00sec)

replace into用法

root@127.0.0.1(helei)>replaceintohelei1(name)values('贺磊');QueryOK,2rowsaffected(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|0|+----+-----------+-----+3rowsinset(0.00sec)root@127.0.0.1(helei)>replaceintohelei1(name)values('爱璇');QueryOK,1rowaffected(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|0||5|爱璇|0|+----+-----------+-----+4rowsinset(0.00sec)

replace的用法

当没有key冲突时，replace into 相当于insert，其余列默认值

当key冲突时，自增列更新，replace冲突列，其余列默认值

Insert into …on duplicate key:

root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|0||5|爱璇|0|+----+-----------+-----+4rowsinset(0.00sec)root@127.0.0.1(helei)>insertintohelei1(name,age)values('贺磊',0)onduplicatekeyupdateage=100;QueryOK,2rowsaffected(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|100||5|爱璇|0|+----+-----------+-----+4rowsinset(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|100||5|爱璇|0|+----+-----------+-----+4rowsinset(0.00sec)root@127.0.0.1(helei)>insertintohelei1(name)values('爱璇')onduplicatekeyupdateage=120;QueryOK,2rowsaffected(0.01sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|100||5|爱璇|120|+----+-----------+-----+4rowsinset(0.00sec)root@127.0.0.1(helei)>insertintohelei1(name)values('不存在')onduplicatekeyupdateage=80;QueryOK,1rowaffected(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+-----------+-----+|id|name|age|+----+-----------+-----+|2|小明|28||3|小红|26||4|贺磊|100||5|爱璇|120||8|不存在|0|+----+-----------+-----+5rowsinset(0.00sec)