关于避免MySQL替换逻辑SQL的坑爹操作详解
replaceinto和insertintoonduplicatekey区别
replace的用法
当不冲突时相当于insert，其余列默认值
当key冲突时，自增列更新，replace冲突列，其余列默认值
Com_replace会加1
Innodb_rows_updated会加1
Insertinto…onduplicatekey的用法
不冲突时相当于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=1 DEFAULTCHARSET=utf8;
表数据
root@127.0.0.1(helei)>select*fromhelei1; +----+-----------+-----+ |id|name|age| +----+-----------+-----+ |1|贺磊|26| |2|小明|28| |3|小红|26| +----+-----------+-----+ 3rowsinset(0.00sec)
replaceinto用法
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冲突时，replaceinto相当于insert，其余列默认值
当key冲突时，自增列更新，replace冲突列，其余列默认值
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('贺磊',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)
总结
replaceinto这种用法，相当于如果发现冲突键，先做一个delete操作，再做一个insert操作，未指定的列使用默认值，这种情况会导致自增主键产生变化，如果表中存在外键或者业务逻辑上依赖主键，那么会出现异常。因此建议使用Insertinto…onduplicatekey。由于编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。