MySQL计算时间段内的多个条目，共享3列中的至少1列

Question

I have a table that logs invalid user login attempts. Every time an invalid attempt is made, the username, user IP, user email and time/date is stored in the database.

What I'd like to do is check if within ANY 24 hour time period there has been more than X invalid attempts by the same user. However, the users can change the email, username or IP at any point. So, I need to check that anyone of these 3 fields is in common.

For example:

• User ID: 1; IP: 1.1.1.1; Email: [e-mail]
• User ID: 2; IP: 1.1.1.1; Email: [e-mail]
• User ID: 1; IP: 1.1.1.2; Email: [e-mail]
• User ID: 4; IP: 1.1.1.4; Email: [e-mail]
• User ID: 5; IP: 1.1.1.4; Email: [e-mail]

All of these would match as the SAME user because they share EITHER the user ID, the IP or the email. Then I need to output all user IDs, IPs and emails so I can ban any user in another table that matches these criteria.

Answer 1

This is too long for a comment.

What you have is a graph of connections between records, where the edges are email, username, and IP. You need to traverse this graph to find connected subgraphs. This is difficult. In your example, for instance, id2 and id2 are connected, but they have no fields in common.

So, you need a graph walking algorithm. MySQL does not have constructs that support such algorithms directly in SQL. You can write a stored procedure to find such groups, but this is not something you can do with a single SQL statement

EDIT:

When I've encountered this problem before, I have used SQL, with repeated update statements. The idea is to assign to each record the lowest userid encountered.

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);

You then have to repeat this until nothing gets updated.