【mysql】on duplicate key update

1 on duplicate key update

在向数据库进行插入数据时，字段有唯一索引，并且插入的数据和数据库已存在的数据有唯一性冲突，这时会报 唯一字段异常；

解决办法：
1、 先根据唯一性先对表进行查询，如果存在，则不插入，不存在，则保存（这种方法仅仅限于单条数据插入）;

// 比如User 对象中的username是唯一性字段
public int add(User model) {
	User temp = dao.selectByUsername(model.getUsername());
	if (temp == null) {
		// insert
	} else {
		// update 或其他操作
	}
}

2、 通过on duplicate key update 进行操作

INSERT INTO user( username, sex, password,create_time)  
VALUES 
('aaa',1,'pwd-123',SYSDATE()) 
on duplicate key update password= VALUES(password), create_time = VALUES(create_time)
或者
INSERT INTO user( username, sex, password,create_time)  
VALUES 
('aaa',1,'pwd-123',SYSDATE()) 
on duplicate key update password= '123', create_time = sysdate()

项目中遇到的问题：批量插入，重复性问题

表结构： device_no 是unique key,如果单条查询判断是否存在是很消耗IO，在数据大的时候

CREATE TABLE `device_sharable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID，sys_user.user_id',
  `device_no` varchar(100) NOT NULL COMMENT '设备序列号',
  `share_status` int(1) NOT NULL DEFAULT '0' COMMENT '共享状态： 0非共享 ， 1 共享',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ds_device_no_union` (`device_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COMMENT='可共享设备表，设备控制测试';


INSERT INTO device_sharable(user_id, device_no, share_status, create_time)  
VALUES 
(199,'978796000627',0,SYSDATE()),
(198,'985690000507',0,SYSDATE())
on duplicate key update user_id = VALUES(user_id), create_time = VALUES(create_time)

代码中用的是批量插入

<insert id="batchInsert" parameterType="List">
		INSERT INTO device_sharable (user_id, device_no, share_status,create_time)
		VALUES
		<foreach collection="list" item="model"  separator=",">
		(
			#{model.userId},
			#{model.deviceNo},
			#{model.shareStatus},
			#{model.createTime}
		)
		</foreach>
		ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), create_time = VALUES(create_time)
	</insert>