需求场景
在大量数据插入,但是有很多重复数据。假设有如下数据表格
语法如下
INSERT INTO `table_name` (clo1,col2,...)
VALUES
(val1-1,val1-2,...),
(val2-1,val2-2,...),
(val3-1,val3-2,...),
ON DUPLICATE KEY UPDATE
clo1= VALUES(clo1),
clo2= VALUES(clo2);
- 首先,我们需要定义一个unique key来避免重复键
- 在 ON DUPLICATE KEY UPDATE(重复更新)的操作的时候,采用了values()方法来更新数据
案例
CREATE TABLE `bt_product_synchronize_index` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录编号',
`company_id` int(20) NOT NULL COMMENT '企业编号',
`product_id` varchar(50) DEFAULT '' COMMENT '商品编号',
`code` varchar(255) NOT NULL COMMENT '货号',
`type` tinyint(4) NOT NULL COMMENT '更新类型 1:商品 2:渠道商品',
`operate_type` tinyint(4) DEFAULT NULL COMMENT '操作方式 1:新增 2:修改 3:删除',
`creater` varchar(100) NOT NULL COMMENT '创建者',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建者',
`modifier` varchar(100) NOT NULL COMMENT '修改者',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_company_code_type` (`company_id`,`code`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2712 DEFAULT CHARSET=utf8 COMMENT='商品同步索引对列表';
注意,唯一键
UNIQUE KEY `idx_company_code_type` (`company_id`,`code`,`type`) USING BTREE
我们需要批量插入数据,但是如果有的话,那么就更新modified + modifier,现有数据如下:
执行如下脚本:
INSERT INTO `bt_product_synchronize_index`(`company_id`, `product_id`, `code`, `type`, `operate_type`,
`creater`, `created`, `modifier`, `modified`)
VALUES
(10124, 'pid-00101','1012412145873543323035', 1, 1, '', NOW(), 'updater1', NOW()),
(10124, 'pid-00101','1012412145873543323035', 1, 1, '', NOW(), 'updater2', NOW()),
(10124, 'pid-008', 'code-008',1, 1,'inserter', NOW(), 'inserter', NOW())
ON DUPLICATE KEY UPDATE
modifier = VALUES(modifier),
product_id = VALUES(product_id),
modified = VALUES(modified),
operate_type = VALUES(operate_type);
期待结果:
- 第一条数据被忽略
- 第二条数据更新第一条数据,modifier + modified
- 第三天数据执行insert
运行后结果如下:
注意
如果需要判断是否为空,用IFNULL(),判断传入是否和默认值相同,用DEFAULT()函数