MySQL在共享3列中的至少1列的时间段内统计多个条目
我有一个记录无效用户登录尝试的表。每次进行无效尝试时,用户名,用户IP,用户电子邮件和时间/日期都存储在数据库中。MySQL在共享3列中的至少1列的时间段内统计多个条目
我想要做的是检查是否在任何24小时的时间段内有相同的用户超过X次无效尝试。但是,用户可以随时更改电子邮件,用户名或IP。所以,我需要检查这三个字段中的任何一个是否相同。
例如:
- 用户ID:1; IP:1.1.1.1;电子邮件:[email protected]
- 用户ID:2; IP:1.1.1.1;电子邮件:[email protected]
- 用户ID:1; IP:1.1.1.2;电子邮件:[email protected]
- 用户ID:4; IP:1.1.1.4;电子邮件:[email protected]
- 用户ID:5; IP:1.1.1.4;电子邮件:[email protected]
所有这些将与SAME用户匹配,因为他们共享用户ID,IP或电子邮件。然后,我需要输出所有用户ID,IP和电子邮件,以便我可以禁止任何符合这些条件的表中的用户。
这太长了评论。
你有什么是记录之间的连接图,边缘是电子邮件,用户名和IP。您需要遍历此图来查找连接的子图。这是困难的。在你的例子中,例如,id2和id2已连接,但它们没有共同的字段。
所以,你需要一个图形行走算法。 MySQL没有直接在SQL中支持这种算法的构造。你可以写一个存储过程来找到这样的群体,但是这是不是你可以用一个SQL语句做
编辑:
当我以前也遇到过这个问题,我已经使用SQL,反复update
陈述。这个想法是为每个记录分配遇到的最低用户标识。
create table tgroup as
select t.*, id as grpid
from table t;
update tgroup join
(select email, min(id) as minid
from tgroup t
group by email
) tt
on tt.email = tgroup.email and
tt.minid < tgroup.id
set tgroup.id = least(tt.minid, tgroup.id);
update tgroup join
(select ip, min(id) as minid
from tgroup t
group by ip
) tt
on tt.ip = tgroup.ip and
tt.minid < tgroup.id
set tgroup.id = least(tt.minid, tgroup.id);
然后您必须重复此操作,直到没有更新。
谢谢,这是有道理的。你会有什么建议这样的算法在PHP中?我正在想用数组的东西,但是我无法把头绕在它的周围。 – 2014-09-22 01:57:07
如果'1.1.1'与所示的所有ID一致,则可以基于前三组IP地址。这似乎是与你发布的所有内容唯一密切的关系。 – 2014-09-22 01:58:57
谢谢。知识产权只是一个例子,实际上它们将是真实而且变化多端的。这些用户仍然以某种方式连接,因为每个用户都共享一个ID,IP或一封电子邮件。即使id2和id5是相关的,因为虽然它们不共享直接字段,但它们通过id4具有二阶关系,等等。 – 2014-09-22 02:05:20
啊,我明白了。是的,我可以看到戈登的答案如何解释试图解决问题的复杂性。这是一个头部爆竹。 – 2014-09-22 02:07:39