MySQL - increasing a column value or inserting data if not.

Question

I have users visiting the site. The user can perform many different actions. I would like the counter to count the number of times the user performed this action. The problem is that every day it starts every day. Thus, the model has Id, User, Action, Times, Date

I would like to use this, but I can not, because the 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; 

Answer 1

Do you absolutely want to calculate the value of the counter during the input of the action? It may be easier to just keep track of users and activities with timestamps, for example:

 +--------+----------+---------------------+ | 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 tables with the 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 | 

Answer 2

You left the data column from your insert example, but you mentioned it several times, so I assume it exists. Also, I assume this is the actual date (not a timestamp or date-time).

If you add a unique index (user, action, date), your request will work.

Here is the DDL:

 alter table useractions add unique index unique_idx (user,action,date); 

And your DML (adding a date column):

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

Answer 3

You can use a unique key in a combination of columns. Thus, you can make this combination (user, action, date) unique, and your request should work.

This is really the easiest solution. However, you need permissions to modify the table.