原文链接:https://blog.csdn.net/weixin_40533111/article/details/84137404 作者四月天五月雨^_^,转载请注明出处,谢谢
前言
本文介绍几种数据库SQL插入问题,如果存在该记录,则不操作或者执行更新,每种方式都有各自适应的场景,一一举例
目录:
1.虚表dual 加条件 not EXISTS
2.使用replace into
3.使用ON DUPLICATE KEY UPDATE
4.从其他表获取数据
5.设置唯一索引
正文:
1.虚表dual 加条件 not EXISTS
表现状:一张新建标签表tag,主键id自增,另外几个普通字段
现在有个 需求: 新插入一个标签, 要求item_key,item_value不能同时重复,否则不插入
选择: 首先排除4,接着排除3,5,因为普通列上没有设置索引,(可以设置唯一索引在两个列上,这样插入重复时,会索引重复导致失败,会抛出异常,如果频繁插入重复,损耗性能,不建议); 现有的标签在其他表中可能存在引用,故不能使用2的replace,综合考虑使用方式1
步骤:
使用虚表dual后跟条件 EXISTS,
eg:
insert into tag (category,item_key,item_value) select 'test1','key1','value1' from dual where NOT EXISTS (SELECT id FROM tag WHERE category='test1' and item_key='key1' and item_value='value1')
2.使用replace into
场景:如果确定存量的数据不重要,那么可以使用replace,简单高效,类似insert into的升级版,但操作的列必须存在唯一索引(主键包含唯一索引),
1 在三个列上加上unique索引(根据实际列数)
2.使用replace
replace into tag (category,item_key,item_value)
values( 'test1','key1','value1' );
可以看到,id为1的那条记录被删除,之后又插入一条新纪录,id为1240;
3.使用ON DUPLICATE KEY UPDATE
需求:如果不存在则插入,存在则更新value键,进行加1(举例用的字符串,实际生产中可能是int),
DUPLICATE 命令需作用在有唯一索引的的列上,如果这列索引有重复则执行update,否则执行insert.
INSERT INTO tag (category,item_key,item_value) VALUES ('test1','key1','value1') ON DUPLICATE KEY UPDATE item_value='value_3';
4.从其他表获取数据
这点和题目不符了,算是个批处理
INSERT INTO tag (category,item_key,item_value)
SELECT category,item_key,item_value from tag_copy where id in(SELECT id from tag_copy where item_value like 'value%');
5.设置唯一索引
步骤可参考第三条,此处做点补充,因为是使用索引来控制插入不重复,那么有个问题,当索引重复时程序会异常,需要cache异常,而在批处理中,如果再使用这种高频度,性能不高的,应通过其他方式控制.
春水碧于天,画船听雨眠。