on duplicate key update简单使用

╰半橙微兮° 2021-11-23 08:50 237阅读 0赞

在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新，  
在刚碰到的时候，第一反应是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有  
ON DUPLICATE KEY UPDATE一步就可以完成，感觉实在是太方便了。  
但是ON DUPLICATE KEY UPDATE为Mysql特有语法，这是个坑  
语句的作用，当insert已经存在的记录时，执行Update

在mybatis中进行批量增加或修改的sql为：

<insert id="insertBatch" parameterType="java.util.List">
        insert into tabalname(
        doctor_id,
        total_given_num,
        used_num,
        avilable_sms_num,
        delete_flag,
        created_id,
        created_time,
        modified_id,
        modified_time
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
          (
          #{item.doctorId},
          #{item.totalGivenNum},
          #{item.usedNum},
          #{item.avilableSmsNum},
          1,
          #{item.createdId},
          now(),
          #{item.modifiedId},
          now()
          )
        </foreach>
        on DUPLICATE KEY
        update
        total_given_num = total_given_num + values(total_given_num),
        used_num = used_num + values(used_num),
        avilable_sms_num = avilable_sms_num + values(avilable_sms_num),
        modified_time = now()
    </insert>

数据表DDL如下：

CREATE TABLE `tabalname` (
    	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    	`doctor_id` int(11) DEFAULT '0' COMMENT '',
    	`total_given_num` int(11) DEFAULT '0' COMMENT '',
    	`used_num` int(11) DEFAULT '0' COMMENT '',
    	`avilable_sms_num` int(11) DEFAULT '0' COMMENT '',
    	`watched_revision_page` tinyint(1) DEFAULT '0' COMMENT '',
    	`delete_flag` int(11) NOT NULL COMMENT '',
    	`created_id` int(11) NOT NULL COMMENT '',
    	`created_time` datetime NOT NULL COMMENT '',
    	`modified_id` int(11) NOT NULL COMMENT '',
    	`modified_time` datetime NOT NULL COMMENT '',
    	PRIMARY KEY (`id`),
    	UNIQUE KEY `unique_doctor_send_doctor_id` (`doctor_id`) USING BTREE,
    	KEY `index_doctor_send_doctor_id` (`doctor_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1843313 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='xxx';

**优点：**

1.  开发简单，对已有表批量插入新数据时尤其方便（特别是做表数据同步脚本，真香！）
2.  可以减少网络连接开销（减少了数据查询、操作次数），在一定量的数据操作时，效率上也提高。

**缺点：**

1.  MySQL私有语法，非SQL92标准语法，当迁移数据时会造成麻烦，需要改写代码。例如MySQL迁移PgSQL。
2.  当环境复杂时，数据量大的情况下，会出现意想不到的问题。（数据量大、产生并发，建议还是用原子操作）
3.  业务逻辑分散在应用逻辑层和数据层，会对项目维护留下隐患。

**建议：**

1.  不对存在多个唯一键的数据表使用此语句。
2.  在有可能有并发事务执行的insert 语句情况下不使用该语句。
3.  使用此语句要考虑以后是否会做数据迁移，数据量是否大，考虑后再使用！！！