replaceinto和insertintoonduplicatekey。在没有冲突的情况下，replace的用法等同于insert。当key冲突时，更新自增列，替换冲突列，其余列默认值com_replace会加1，innodb_rows_updated会加1。insertinto...onduplicatekey用法等同于insertwhen重复键的使用不冲突。当其他列的默认值与键冲突时，只更新相应的字段值。com_insert会增加1Innodb_rows_inserted会增加1实验显示表结构createtablehelei1(idint(10)unsignedNOTNULLAUTO_INCREMENT,namevarchar(20)NOTNULLDEFAULT'',agetinyint(3)unsignedNOTNULLdefault0,PRIMARYKEY(id),UNIQUEKEYCHUL_name(name))ENGINESET=INCRFAUutf8;表数据root@127.0.0.1(helei)>select*fromhelei1;+----+---------+-----+|id|name|年龄|+----+---------+-----+|1|何磊|26||2|小明|28||3|小红|26|+----+------------+-----+3rowsinset(0.00sec)replaceintousageroot@127.0.0.1(helei)>replaceintohelei1(name)values('何磊');QueryOK,2rowsaffected(0.00sec)root@127.0.0.1(helei)>select*fromhelei1;+----+------------+-----+|id|name|年龄|+----+------------+-----+|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)replaceusage当没有键冲突时，replaceinto等同于insert，其余ofcolumnsaredefaultvalues当键冲突时，更新自增列，替换冲突列，其余列的默认值为Insertinto...onduplicatekey: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('Helei',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|姓名|年龄|+----+------------+-----+|2|小明|28||3|小红|26||4|何磊|100||5|艾轩|0|+----+------------+-----+4rowsinset(0.00sec)root@127.0.0.1(helei)>insertintohelei1(name)values('Aixuan')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)总结了replaceinto的使用，相当如果发现有冲突的key，先执行delete操作，再执行insert操作。未指定的列使用默认值。这种情况会引起自增主键发生变化。如果表中有外键或者业务逻辑依赖于主键，那么就会出现异常，所以推荐使用Insertinto...onduplicatekey。由于写作时间仓促，文章中难免存在一些错误或不准确之处。不当之处敬请广大读者批评指正。
