MySQL INSERT语句,如果主键有重复,则更新
参考MySQL官方文档
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
INSERT … ON DUPLICATE KEY UPDATE Statement
官方例子:
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;
上述两个语句是等价的
- 从INSERT部分引用列值,使用别名
INSERT INTO t1 SET a=1,b=2,c=3 AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
Mybatis中编写Mapper中使用
<insert id="insertProfile" parameterType="Account">
INSERT INTO PROFILE
(LANGPREF, FAVCATEGORY, USERID)
VALUES (#{languagePreference}, #{favouriteCategoryId}, #{username})
ON DUPLICATE KEY UPDATE
LANGPREF = #{languagePreference}, FAVCATEGORY = #{favouriteCategoryId}
</insert>