【mysql】on duplicate key update

桃扇骨 2022-12-03 02:26 164阅读 0赞

## 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，在数据大的时候

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpYXppbGlwaW5n_size_16_color_FFFFFF_t_70_pic_center]

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>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpYXppbGlwaW5n_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/bd179fccc95b4569be5bf356516a44e5.png