//表结构
CREATE TABLE `stair_performance` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`dep_id` int(8) DEFAULT NULL COMMENT '部门id',
`product_id` varchar(64) DEFAULT NULL,
`type` tinyint(2) DEFAULT NULL COMMENT '1进件\r\n2放款笔数\r\n3放款金额',
`target` int(9) DEFAULT NULL COMMENT '目标金额',
`year` int(4) DEFAULT NULL,
`month` int(2) DEFAULT NULL,
`created` int(10) DEFAULT NULL,
`updated` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `only` (`dep_id`,`product_id`,`type`,`year`,`month`) USING HASH //设置联合唯一索引
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4;
//批量操作语句
INSERT INTO stair_performance (
dep_id,
product_id,
type,
target,
year,
month,
created,
updated
)
VALUES
(1,
'2332ty32787812ad1',
1,
44,
2020,
05,
1234567890,
1234567890
),
(1,
'2332ty32787812ad2',
1,
79,
2020,
3,
1234567890,
1234567890
),
(1,
'2332ty32787812ad3',
1,
72,
2020,
4,
1234567890,
1234567890
),(1,
'2332ty32787812ad3',
1,
72,
2020,
5,
1234567890,
1234567890
),
(1,
'2332ty32787812ad4',
1,
23,
2020,
4,
1234567890,
1234567890
)ON DUPLICATE KEY UPDATE dep_id = VALUES(dep_id) ,
product_id = VALUES(product_id),
target = VALUES(target);
SELECT COUNT(id) FROM stair_performance;
SELECT * FROM stair_performance;