【mysql】on duplicate key update-蒲公英云

【mysql】on duplicate key update

桃扇骨 2022-12-03 02:26 233阅读 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，在数据大的时候

在这里插入图片描述

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>