创建一个表:
CREATE TABLE `xxx_stat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`appkey` varchar(100) DEFAULT NULL,
`value` bigint(20) DEFAULT '0',
`date` varchar(10) DEFAULT NULL,
`hour` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk` (`appkey`,`date`,`hour`),
KEY `i_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
执行一条insert:
insert into xxx_stat (appkey, date, hour, value) values ("123", "2018-05-10", "12", 1);
然后再执行insert or update:
insert into xxx_stat (appkey, date, hour, value) values ("123", "2018-05-10", "12", 1) on duplicate key update value = value + VALUES(value);
insert into xxx_stat (appkey, date, hour, value) values ("123", "2018-05-10", "12", 1) on duplicate key update value = value + VALUES(value);
insert into xxx_stat (appkey, date, hour, value) values ("123", "2018-05-10", "12", 1) on duplicate key update value = value + VALUES(value);
insert into xxx_stat (appkey, date, hour, value) values ("123", "2018-05-10", "12", 1) on duplicate key update value = value + VALUES(value);
这时候再看数据库表:
CREATE TABLE `xxx_stat` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`appkey` varchar(100) DEFAULT NULL,
`value` bigint(20) DEFAULT '0',
`date` varchar(10) DEFAULT NULL,
`hour` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk` (`appkey`,`date`,`hour`),
KEY `i_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8