mysql之ON DUPLICATE KEY UPDATE的使用

SQL语句中的ON DUPLICATE KEY UPDATE使用

背景：项目里面原来的sqlserver语法改为mysql语法，批量插入大量数据进入临时表，再从临时表加入正式表中，原来的逻辑是 临时表插入的正式表时，先由修改语句修改，再新增，这样巨麻烦，当时写这个代码的大哥也是耐心写完了， 小弟佩服
ON DUPLICATE KEY UPDAT是连新增带更新一起走，sql一步到位
走你
补充：之前是批量插入巨多条数据到临时表中，500条一个批次

INSERT INTO oc_product_tmp
		(code,`name`,updateTime)
		VALUES
		<foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
			#{item.code},#{item.name},now()
		</foreach>

传一个List进来，这个就提高了很大的效率
sql的执行效率要远比for循环高得多，相对于大批量数据来说，这个就很nice，学到了

开始进入正题

先创建一个临时表

-- ----------------------------
-- Table structure for oc_product_tmp
-- ----------------------------
DROP TABLE IF EXISTS `oc_product_tmp`;
CREATE TABLE `oc_product_tmp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `updateTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;


插入数据
INSERT INTO `oc_product_TMP` VALUES ('1', '苹果', now());
INSERT INTO `oc_product_TMP` VALUES ('2', '香蕉', now());
INSERT INTO `oc_product_TMP` VALUES ('3', '离子', now());
INSERT INTO `oc_product_TMP` VALUES ('4', '柚子', now());
INSERT INTO `oc_product_TMP` VALUES ('5', '橘子', now());

再创建正式表

DROP TABLE IF EXISTS `oc_product`;
CREATE TABLE `oc_product`  (
   `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `updateTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

开始同步数据

SELECT * FROM oc_product_tmp limit 10;

SELECT * FROM oc_product limit 10;

-- 临时表向正式表同步数据，存在就修改，不存在就新增
insert into oc_product(`code`,`name`,updateTime)
	select * FROM oc_product_tmp
		on duplicate key update
	code=values(code),name = VALUES(name),updateTime = VALUES(updateTime)
		

此时临时表是有数据的，正式表没有数据的
再执行sql去往正式表同步数据

insert into oc_product(id,`code`,`name`,updateTime)
	select * FROM oc_product_tmp
		on duplicate key update
	code=values(code),name = VALUES(name),updateTime = VALUES(updateTime)

查看正式表数据，此时数据已经同步过来了

修改临时表数据,并新增第六条数据西瓜

再此同步数据，查看新增和更新的结果
更让还是上面的sql就不再重新写了
直接看同步后的结果

修改过的1-5同步到了正式表中，新增的6 西瓜也同步到了正式表中

酒是有温度的江河
岁月如风
把甘甜和辛辣都藏在江河里
而我钟爱喝酒的原因
都是我要撑杯而起
顺江之下
直到遇见你
所有的酒标
都是我想你投诚的旗