UPON DUPLICATE KEY增加多列吗? [英] UPON DUPLICATE KEY increment multiple columns?

问题描述

我正在运行数据库日志，每天我都登录新行.因此，Mysql查询将检查日期(日期(唯一键))是否已经存在，如果存在，它将尝试将日志行的所有可记录值增加一.如果日期记录还不存在，它将创建一个新行.

我的SQL查询是:

INSERT INTO `log` (`date`,`hits`,`stale`)
VALUES ('2012-03-06',1,1)   
ON DUPLICATE KEY
UPDATE `hits`=`hits`+1,`stale`=`stale`+1
WHERE `date`='2012-03-06';"

所有列的默认值均为0，因此如果此查询在午夜之后直接运行，则仅将"stale"和"hits"设置为1.否则，"stale"和"hits"都将递增.

我希望！ (无效).

我想念什么?在'hits'='hits'+1 和'stale'='stale'+ 1 之间应该使用逗号以外的分隔符?

解决方案

您不应具有WHERE子句. ON DUPLICATE KEY UPDATE自动将其影响的行限制为具有现有键的行.

将其删除，您的查询应该可以正常工作.

Im running a database log and every day I log on a new row. My Mysql query therefore checks if the day (date (the unique key)) already exists, and if so, it tries to increment all the loggable values of the log-row by one. If the date record doesnt eyist yet, it will create a new row.

My SQL query is:

INSERT INTO `log` (`date`,`hits`,`stale`)
VALUES ('2012-03-06',1,1)   
ON DUPLICATE KEY
UPDATE `hits`=`hits`+1,`stale`=`stale`+1
WHERE `date`='2012-03-06';"

All columns have 0 as default value, so if this query runs directly after midnight only 'stale' and 'hits' are set to 1. Otherwise 'stale' and 'hits' are both incremented.

I wish! (it doesn't work).

What am I missing? Which separator other then a comma should I use between 'hits' = 'hits' +1 and 'stale'='stale'+1?

解决方案

You shouldn't have the WHERE clause. ON DUPLICATE KEY UPDATE automatically limits the row it affects to the one that has the existing key.

Remove it and your query should work fine.

这篇关于UPON DUPLICATE KEY增加多列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！