插入之前如何计数? [英] How can I count something before inserting?

问题描述

我有这张桌子:

// cookies
+---------+-------------------------+------------------+------------+
|   id    |         email           |     cookie       | date_time  |
+---------+-------------------------+------------------+------------+
| int(11) |      varchar(50)        |   varchar(128)   |  int(11)   |
+---------+-------------------------+------------------+------------+
| 1       | [e-mail]     | ojer0f934mf2...  | 1467204523 |
| 2       | [e-mail]      | ko4398f43043...  | 1467205521 |
| 3       | [e-mail] | 34fjkg3j438t...  | 1467205601 |
| 4       | [e-mail]      | 0243hfd348i4...  | 1467206039 |
+---------+-------------------------+------------------+------------+

这是我的查询:

INSERT INTO cookies VALUES(NULL, $email, $hash, unix_timestamp())

现在，在插入之前，我需要检查以下条件:

Now I need to check following condition before inserting:

(针对特定用户)的行数应小于:

每小时
• 5
每天
• 10
每月
• 50
总计
• 100

• 5 per hour
• 10 per day
• 50 per month
• 100 per total

我只能检查最后一种情况:

I just can check the last case:

INSERT INTO cookies(id, email, cookie, date_time)
SELECT NULL, $email, $hash, unix_timestamp()
FROM cookie
WHERE email = $email AND
      100  >=  ( SELECT count(1) FROM cookies WHERE email = $email )

那么，如何添加其他条件?

Well, how can I add other conditions?

推荐答案

对于(c)组中的>是否应为>=，我不是很肯定，但是我认为这样做可以你在问什么.

I'm not positive on whether the >'s (in the group by) should be >='s, but I think this will do what you are asking.

INSERT INTO cookies(id, email, cookie, date_time)
SELECT NULL, $email, $hash, unix_timestamp()
FROM cookie
WHERE email = $email 
   AND NOT EXISTS (
        SELECT COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 HOUR) 
                          THEN 1 ELSE NULL END) AS rowsInLastHour
           , COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 DAY) 
                          THEN 1 ELSE NULL END) AS rowsInLastDay
           , COUNT(CASE WHEN date_time > UNIX_TIMESTAMP(now() - INTERVAL 1 MONTH) 
                        THEN 1 ELSE NULL END) AS rowsInLastMonth
           , COUNT(1) AS rowsEver
        FROM cookie
        WHERE email = $email
        HAVING rowsInLastHour > 5 
          OR rowsInLastDay > 10 
          OR rowsInLastMonth > 50 
          OR rowsEver > 100
    )
;

• 通过使用now() - INTERVAL 1 HOUR|DAY|MONTH来查找最近一个小时| day | month的开始时间，并计算在最后一个小时| day | month中具有date_time值的所有行(用于电子邮件)，并对其进行计数开始时间.
• 然后，如果超出了您指定的任何限制，则它使用HAVING仅产生奇异的结果(诸如COUNT之类的没有关联的GROUP BY子句的聚合始终会产生1行).
• 然后，如果没有结果(因为未超出限制)，NOT EXISTS将返回true.
• It counts all the rows (for the email) that had date_time values in the last hour|day|month by using now() - INTERVAL 1 HOUR|DAY|MONTH to find when the last hour|day|month started, and counting those values that occurred after those starting times.
• It then uses the HAVING to only yield the singular result (aggregation such as COUNT that does not have an associated GROUP BY clause always results in 1 row), if any of the limits you specified were exceeded.
• Then the NOT EXISTS returns true if there were no results (because the limits were not exceeded).

根据问题的需要，更新了比较以使用单位时间戳记.

这篇关于插入之前如何计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！