MySQL INSERT语句，如果主键有重复，则更新

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;

上述两个语句是等价的

2. 从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>