Mysql查询计算登录天数

It is common to count the number of days that users login to the system (like stackexchange). In an ordinary user table, I update login information as

通常计算用户登录系统的天数(如stackexchange)。在普通用户表中,我将登录信息更新为

UPDATE users SET 
last_login='date', number_of_login=number_of_login + 1, number_of_days=? 
WHERE user_id='user_id'

where last_date is datetime

其中last_date是datetime

what is the best to check that current day is different from last_login to update number of days with number_of_day=number_of_day+1.

什么是最好检查当前天与last_login不同,以更新number_of_day = number_of_day + 1的天数。

I have two methods in mind, but they seems to be naive:

我有两种方法,但它们似乎很天真:

Method 1: having a SELECT to catch last_login and compare it with current day in PHP

方法1:使用SELECT来捕获last_login并将其与PHP中的当前日期进行比较

Method 2: using a trick like sub-SELECT

方法2:使用类似子SELECT的技巧

I hope to do this with one simple query (if possible).

我希望通过一个简单的查询(如果可能)来做到这一点。

2 个解决方案

#1

So, if I don't get your comment wrong, you want to always update last_login, but number_of_days only if last_login != curdate(). Try this:

所以,如果我的评论不对,你想要总是更新last_login,但只有last_login!= curdate()才能更新number_of_days。尝试这个:

UPDATE users SET 
number_of_days = if (last_login = curdate(), number_of_days, number_of_days + 1),
last_login='date', number_of_login = number_of_login + 1
WHERE user_id='user_id'

#2

I would use DATEDIFF MySql function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff.

我会使用DATEDIFF MySql函数:http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff。

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

DATEDIFF()返回expr1 - expr2,表示为从一个日期到另一个日期的天数值。 expr1和expr2是日期或日期和时间表达式。在计算中仅使用值的日期部分。

After you fetch results, you should check if fetched column is greater than zero to determine if dates are different.

获取结果后,应检查fetched列是否大于零,以确定日期是否不同。

#1