在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,
在刚碰到的时候,第一反应是将其实现分为两块,分别是判断增加,判断更新,后来发现在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';
优点:
- 开发简单,对已有表批量插入新数据时尤其方便(特别是做表数据同步脚本,真香!)
- 可以减少网络连接开销(减少了数据查询、操作次数),在一定量的数据操作时,效率上也提高。
缺点:
- MySQL私有语法,非SQL92标准语法,当迁移数据时会造成麻烦,需要改写代码。例如MySQL迁移PgSQL。
- 当环境复杂时,数据量大的情况下,会出现意想不到的问题。(数据量大、产生并发,建议还是用原子操作)
- 业务逻辑分散在应用逻辑层和数据层,会对项目维护留下隐患。
建议:
- 不对存在多个唯一键的数据表使用此语句。
- 在有可能有并发事务执行的insert 语句情况下不使用该语句。
- 使用此语句要考虑以后是否会做数据迁移,数据量是否大,考虑后再使用!!!