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;

上述两个语句是等价的

  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>
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 博客之星2020 设计师:CY__ 返回首页