相同之处:批量更新数据,目标数据存在更新,不存在插入。
不同之处:
一、REPLACE 步骤是删除旧数据,插入新数据,之前的递增自动编号不存在,会AUTO_INCREMENT一个新的自动编号,因此会刷新索引。
二、DUPLICATE 不会改变旧的递增自动编号,同样会保持旧的索引不变。
------------------------------------------
DUPLICATE 语法例子:
INSERT INTO `mytab`
(`pid`,`goodsid`,`batchid`,`batchdataid`,`code`,`status`,`packageType`, `dealerid`,`outboundTime`,`data`)
VALUE
(0,15,3,4,'888370000000483888888',1,0,4,1649988295,''),
(0,15,3,4,'789550000000462455620',1,0,4,1649988295,''),
(0,15,3,4,'654370000000483554368',1,0,4,1649988295,'')
ON DUPLICATE KEY UPDATE
`pid` = VALUES(`pid`),
`goodsid` = VALUES(`goodsid`),
`batchid` = VALUES(`batchid`),
`batchdataid` = VALUES(`batchdataid`),
`status` = VALUES(`status`),
`packageType` = VALUES(`packageType`),
`dealerid` = VALUES(`dealerid`),
`outboundTime` = VALUES(`outboundTime`),
`data` = VALUES(`data`)
;
------------------------------------------
REPLACE 语法例子:
REPLACE INTO `mytab`
(`pid`,`goodsid`,`batchid`,`batchdataid`,`code`,`status`,`packageType`, `dealerid`,`outboundTime`,`data`)
VALUES
(0,5,3,4,'850070000000448910627',1,0,4,1649988295,''),
(0,5,3,4,'789550000000462455620',1,0,4,1649988295,''),
(0,5,3,4,'654370000000483554368',1,0,4,1649988295,'')
;
FROM: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html