避免MySQL替换逻辑SQL的坑爹操作

2018-01-13 11:01:13来源:http://database.51cto.com/art/201801/563413.htm作者:51CTO人点击

分享


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=1
DEFAULTCHARSET=utf8;
</br>

表数据
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)

总结

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


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台