MYSQL的 on duplicate key update实现插入或更新操作

一、背景

1、业务上经常有这样的场景，存在进行更新，不存在进行新增。

2、用过主键或唯一索引对数据库进行判断是否存在，再根据结果进行insert或update。这样处理效率低，若并发比较高时会引起数据问题。

二、概述

1. 为了解决这种业务场景，MySQL有一种语法（

   insert  into …… on duplicate key update

   ）。应用这种语法，一条SQL语句实现插入或更新，可单条可批量。
2. 使用说明
   1. 表要求必须有主键或唯一索引才可以，否则insert或update无效。
   2. 该语法是根据主键或唯一键来判断是新增还是更新。
   3. update 后面应为需要更新的字段，不需要更新的字段不用罗列，字段必须发生改变，不然存在时不会update(通过modified_time 时间字段验证)。
   4. values()里面的字段需用数据库字段，不能用参数字段。
   5. 遇到已存在记录（根据唯一键或主键）时，自动更新已有的数据；如果表中有多个唯一键（可以是单列索引或复合索引），则任意一个唯一键冲突时，都会自动更新数据。
   6. 所有操作由底层SQL处理，不需代码分析，大幅提高程序执行效率。

3. on duplicate key update 与 replace into 

   1. 相较于replace into（insert加强版，不存在时insert，存在时先delete后insert）虽然也能达到批量更新目的，但因为删除和添加需要重复维护索引，所以大批量比on duplicate key update 性能要差，小量可忽略，需使用者自行选择。
   2. replace into 遇到已存在的记录，会先删除掉表中原有的记录后，再插入新的记录，这样会导致该记录的主键发生变化，如果该表的主键和其它表有业务关联，那么会导致关联数据丢失。
   3. replace into 插入的主键在某些时候不是连续自增的，这样会导致主键增长的数据很快，有时候会超过 int(10) 的最大值

三、使用

1 mysql使用

1.1、创建表

CREATE TABLE table_user(
    id              BIGINT(20)  NOT NULL AUTO_INCREMENT  COMMENT '主键',
    name            VARCHAR(64) NOT NULL   COMMENT '名称',
    sex             VARCHAR(4)  NOT NULL   COMMENT '性别',
    age             INT         NOT NULL   COMMENT '年龄',
    created_time    timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    modified_time   timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uniq_name (name)
);

1.2、现有数据

 1.3、on duplicate key update 插入或更新数据

insert into table_user (name, sex, age) values ('小红', '1', 12) on duplicate key update sex=values(sex), age = values(age);

 1.4、执行后结果

  2 mybatis使用

2.1、 单条insert或update

    /**
     * 插入或更新信息
     */
    int saveOrUpdate(tableUser tableUser);

<insert id="saveOrUpdate"  keyColumn="id" keyProperty="id" parameterType="com.tableUser" useGeneratedKeys="true">
        insert into table_user (name, sex, age)
        values (#{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})
        on duplicate key update sex=values(sex), age=values(age)
 </insert>

2.2、批量insert或update

     /**
     * 批量插入或更新信息
     */
    int batchSaveOrUpdate(List<TableUser> tableUserList);

<insert id="batchSaveOrUpdate" keyColumn="id" keyProperty="id" parameterType="java.util.List" useGeneratedKeys="true">
        insert into table_user (name, sex, age) values
        <foreach collection="tableUserList" item="tableUser" separator=",">
            (#{tableUser.name,jdbcType=VARCHAR}, #{tableUser.sex,jdbcType=VARCHAR}, #{tableUser.age,jdbcType=INTEGER})
        </foreach>
        on duplicate key update sex=values(sex), age=values(age)
 </insert>