replace into
注意,如果有多个唯一键,那么执行结果可能合并减少记录数。
- sqlserver
if not exists (select 1 from t where id = 1)
insert into t(id, update_time) values(1, getdate())
else
update t set update_time = getdate() where id = 1
- mysql
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...
ON DUPLICATE KEY UPDATE
CREATE TABLE `willow_player` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`player_id` bigint(16) NOT NULL DEFAULT '0',
`num` int(11) NOT NULL DEFAULT '0',
`award_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '类型',
`repeat_num` smallint(11) NOT NULL DEFAULT '0' COMMENT '重复轮数',
PRIMARY KEY (`id`),
UNIQUE KEY `num` (`player_id`,`award_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8mb4;
#以前写法:
select num from 表名 where uid=20001 and award_type=0 limit 1;
update 表名 set num=num+1 where uid=20001 and award_type=0
#现在
insert into table (player_id,award_type,num) values(20001,0,1) on DUPLICATE key update num=num+values(num)