Mysql统计技巧：ON DUPLICATE KEY UPDATE用法

ON DUPLICATE KEY UPDATE是mysql的特殊语法，与INSERT INTO一起使用，意思就是记录存在就更新，否则就添加。

用法：

INSERT INTO user(userid,username,age) VALUES(1,'ssy',20) ON DUPLICATE KEY UPDATE age = age + 1;

sql语句执行结果分析：

假设未执行此语句前的记录是这样：

userid	username	age
1	ssy	20

第一种情况：

如果userid、username、age三个字段中的任何一个被设置了PRIMARY KEY或者UNIQUE，那么这条语句的效果是UPDATE。

假设userid被设成了PRIMARY KEY，那么上面的SQL相当于：

UPDATE user SET age = age + 1 WHERE userid = 1;

执行后返回被影响的行数是2，结果是：

userid	username	age
1	ssy	21

需要注意的是： 这种情况下只认被设成PRIMARY KEY的userid字段和被ON DUPLICATE KEY UPDATE的age字段，语句里的其他字段都被忽略。

第二种情况： 如果userid、username、age三个字段都不是PRIMARY KEY或者UNIQUE，那这条语句的效果是INSERT INTO。

上面的SQL语句相当于：

INSERT INTO user(userid,username,age) VALUES(1,'ssy',20);

执行后返回被影响的行数是1，结果是：

userid	username	age
1	ssy	20
2	ssy	20

适用场景： 统计计数

INSERT INTO … ON DUPLICATE KEY UPDATE … 的作用简单讲就是，当记录存在时更新，否则插入。这非常适用于数量统计。

比如用户操作日志统计表叫count，字段为id,username,opnum,date
其中 id 为 PRIMARY KEY。username和date为不重复的 UNIQUE组合，也就是说只要username和date这对组合不能重复。创建表语句为：

CREATE TABLE `user`(
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(30) NOT NULL DEFAULT '',
    `opnum` INT(11) NOT NULL DEFAULT '0',
    `date` INT(11) NOT NULL DEFAULT '0',
    UNIQUE KEY `unique` (`username`, `date`)
)

假设原数据为：

userid	username	opnum	date
1	ssy	0	20150424

那么如果有某用户执行了某个操作，可以这样：

INSERT INTO count(username,opnum,date) VALUES('ssy',5,'20150424') ON DUPLICATE KEY UPDATE opnum = opnum + 1;

结果为：

userid	username	opnum	date
1	ssy	1	20150424

INSERT INTO count(username,opnum,date) VALUES('ssy',10,'20150425') ON DUPLICATE KEY UPDATE opnum = opnum + 1;
INSERT INTO count(username,opnum,date) VALUES('dlm',10,'20150425') ON DUPLICATE KEY UPDATE opnum = opnum + 1;

结果为：

userid	username	opnum	date
1	ssy	1	20150424
2	ssy	10	20150425
3	dlm	10	20150425

这样，如果用户ssy在2015年4月24日这一天如果有操作，就追加操作数，如果该用户今天尚无操作，则增加一条今天的操作记录。即实现了多个用户的每日统计。
当然INSERT INTO … ON DUPLICATE KEY UPDATE的使用场景还有很多，知道原理后大家就可以自行探索了。