mysql增加字段如果不存在,MySQL-增加列值或插入数据（如果不存在）

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

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 |