针对不明确insert还是update的操作，mysql数据库表设计

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)