MySQL-增加列值或插入数据(如果不存在) [英] MySQL - Increment Column Value or Insert Data if not Exists

问题描述

我有用户访问该网站.用户可以执行许多不同的操作.我想要一个计数器来计算用户执行该操作的次数.问题是，它每天都在发生，每天都在开始. 因此该模型具有 Id，用户，操作，时间，日期

I have users visiting the site. The user can do a bunch of different actions. I'd like a counter to count the amount of times the user does that action. The problem is, it's per day, it starts over each day. So the model has, Id, User, Action, Times, Date

我想使用它，但是我不能，因为 Action 不是键，也不能.其他字段也不可以是键.

I'd like to use this, but I can't because Action is not a key, and cannot be. None of the other fields can be a key either.

insert into useractions (user, action, times) values (2, 3, 1)
on duplicate key update times = times + 1;

推荐答案

您是否绝对要在插入操作时计算计数器值?仅用时间戳跟踪用户和操作可能会更简单，例如:

Do you absolutely want to calculate the counter value at the time of inserting the action? It may be simpler to simply keep track of the users and actions with timestamps, like so:

+--------+----------+---------------------+
| UserID | ActionID | Time                |
+--------+----------+---------------------+
|      1 |        1 | 2012-01-19 14:47:03 |
|      1 |        2 | 2012-01-19 14:48:12 |
|      1 |        3 | 2012-01-19 14:48:15 |
|      2 |        1 | 2012-01-19 14:49:33 |
|      2 |        1 | 2012-01-18 14:49:42 |

然后通过查询来计算每日帐目记录:

And then calculate the daily tallies with a query:

SELECT UserID,
       ActionID,
       DATE(Time) AS Date,
       COUNT(*) AS n
FROM actions
GROUP BY UserID,ActionID,Date
ORDER BY Date,UserID,ActionID;

+--------+----------+------------+---+
| UserID | ActionID | Date       | n |
+--------+----------+------------+---+
|      1 |        2 | 2012-01-17 | 2 |
|      1 |        3 | 2012-01-17 | 2 |
|      3 |        2 | 2012-01-17 | 6 |
|      1 |        1 | 2012-01-18 | 1 |
|      1 |        2 | 2012-01-18 | 1 |
|      1 |        3 | 2012-01-18 | 4 |

这篇关于MySQL-增加列值或插入数据(如果不存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！