1、背景
当我们需要对已插入mysql的数据进行更新时，会碰到两种情况：
1、该记录可能存在，则进行内容更新；
2、该记录不存在，则进行插入。
如果采用两段sql进行处理，数据处理不能保证原子性，即两段sql处理同一条数据存在时间差，在并发请求下会导致数据出错。
此时，可以采用
insert into table (col1,col2...) values
(v11, v12...),(v21,v22...)...
on duplicate key update col1=VALUES(col1),col2=VALUES(col2)... ;
2、举例
1、创建一张mysql的测试表。
注意表中的主键、索引
CREATE TABLE `test` (
`id` bigint(20) NOT NULL COMMENT '主键id',
`productinfo_id` int(11) NULL DEFAULT NULL COMMENT '产品信息Id',
`rank` int(11) NULL DEFAULT NULL COMMENT '排名',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `IX_productinfo_id` (`productinfo_id`) USING BTREE
)
COMMENT='测试'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
2、插入原始数据
insert into `test` (id, productinfo_id, rank) values
(1, 1, 1)
,(2, 2, 2)
,(3,3, 3)
;
图片.png
3、进行数据更新
insert into `test`(id, productinfo_id, rank) values
(4, 1, 5), (5, 2, 2)
ON DUPLICATE KEY UPDATE
productinfo_id=VALUES(productinfo_id),
rank=VALUES(rank),
id=VALUES(id)
;
图片.png
3、说明
1、ON DUPLICATE KEY UPDATE 会以表的索引或主键（索引优先）为标识，进行数据查询更新。
（1）当存在索引时，以索引为身份标识，判定新旧两条记录是否为同一对象，从而进行更新；
（2）当索引不存在时，则以主键作为身份标识，进行数据更新。
2、匹配到多条记录时，只会更新一条记录，其它记录会忽略。
3、原表中存在的数据，但在新表中不存在时，新表数据更新到旧表中后，该记录不会被删除。
注：如果需要标识数据已失效，需采用其它办法。
4、ON DUPLICATE KEY UPDATE仅为mysql特有的语句，其它数据库并不适用。
参考文档：
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
网友评论