mysql 重复插入数据问题

Question

避免重复添加数据，又需要累加结果的一个需求。

表结构大致是这样子的：

Column    Type    Comments
id        int(11) 自增主键
pid       int(11) p标识
tid       int(11) t标识
count     int(11) 出现次数
duration  int(11) 时延

pid, tid 在表中的组合是唯一的，有可能没有该组合；重复插入现有的 pid, tid 组合时，需要累加 count 和 duration 数值。

在网上泡了许久，发现了 on duplicate key update 似乎是我想要的，于是写下 sql

insert into stat(pid, tid, count, duration)
    (select pid, tid, count, duration 
     from stat 
     where pid = 1 and tid = 5)
on duplicate key update
stat.count =  stat.count + 1, stat.duration = stat.duration + 1;

但是只能工作于 pid, tid 组合存在的情况，而且会说 Column 'stat.count' in field list is ambiguous；而没有 pid, tid 组成的时候，也插入不进去。

囧囧有神，求 sql 大神包养～

Answer 1

题主试试：

#建立pid、tid的UNIQUE INDEX
ALTER TABLE `stat` ADD UNIQUE INDEX `pid_tid_UNIQUE` (`pid`, `tid`);

#没有insert，主键冲突update
insert into stat(pid, tid, count, duration) values(1,5,1,1) on duplicate key update count = count + 1, duration = duration + 1;

on duplicate key update更多语法细节可参考《INSERT ... ON DUPLICATE KEY UPDATE Syntax》

Answer 2

mysql的on duplicate key update语法在insert的时候，如果insert的数据会引起唯一索引（包括主键索引）的冲突，即这个唯一值重复了，则不会执行insert操作，而执行后面的update操作。所以表结构添加索引：

alter table stat add unique (`pid`);

然后再执行插入语句就OK了：

insert into `stat`(`pid`, `tid`, `count`, `duration`)(select `pid`, `tid`, `count`, `duration` from `stat` as `s`) on duplicate key update `stat`.`count` =  `stat`.`count` + 1, `stat`.`duration` = `stat`.`duration` + 1;