我们在进行插入数据时,如果出现主键存在的情况,单纯的insert语句显然已经无法满足要求,以下时三种避免重复插入数据的方式
方式一:ignore
插入时检索主键列表,如存在相同主键记录,不更改原记录,只插入新的记录。
INSERT IGNORE INTO test (id,name,created_time) VALUES (2,'test',now());
ignore关键字所修饰的SQL语句执行后,在遇到主键冲突时会返回一个0,代表并没有插入此条数据。如果主键是由后台生成的(如uuid),我们可以通过判断这个返回值是否为0来判断主键是否有冲突,从而重新生成新的主键key。
mybaits中使用方式
<insert id="test" parameterType="com.model.test"> INSERT IGNORE INTO test <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> name, </if> created_time </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INT}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> now() </trim> </insert> |
方式二:replace
插入时如发现主键已存在,则替换原记录,即先删除原记录,后insert新记录。否则insert新记录
REPLACE INTO test (id,name,created_time) VALUES (2,'test',now());
Replace修饰的SQL语句,如果遇到主键冲突时会返回2,代表删除原记录后,重新插入新纪录
mybaits中使用方式
<insert id="test" parameterType="com.model.test"> REPLACE INTO test <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> name, </if> created_time </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INT}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> now() </trim> </insert>
|
方式三:INSERT INTO … ON DUPLICATE KEY UPDATE
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。
INSERT INTO test (id,name,created_time) VALUES (2,'test',now()) ON DUPLICATE KEY UPDATE name=VALUES(name), created_time = VALUES(created_time);
此SQL语句为当主键id相同时,则会更新该列记录的name值为test。同REPLACE相同,如果遇见主键冲突并更新结果返回2,插入新记录时返回1。
Mybaits中使用方法
<insert id="test" parameterType="com.model.test"> INSERT INTO test <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> name, </if> created_time </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INT}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> now() </trim> ON DUPLICATE KEY UPDATE <if test="name != null"> name=VALUES(name), </if> created_time = VALUES(created_time) </insert>
|
replace into和on duplcate key update都是只有在primary key或者unique key冲突的时候才会执行。如果数据存在,replace into则会将原有数据删除,再进行插入操作,这样就会有一种情况,如果某些字段有默认值,但是replace into语句的字段不完整,则会设置成默认值。而on duplicate key update则是执行update后面的语句。
参考:
MySQL忽略主键冲突,避免重复插入数据的三种方式
https://blog.csdn.net/u014745069/article/details/80233374
replace into和insert into on duplicate key update的区别