How to find similar IPs in MySQL?

user_7089726 0
Viewed 94

I have a table which includes the last IP of the user. Using the following query I am able to find all duplicate IP addresses

SELECT id, ip, COUNT(ip) AS ip_count FROM users GROUP BY ip HAVING ip_count > 1

I am trying to select IPs that are different by the last part only. Here are some examples:

+--------------+---------------+---------+
|     IP 1     |     IP 2      | Similar |
+--------------+---------------+---------+
| 230.15.26.79 | 230.15.26.230 | true    |
| 32.82.0.5    | 32.82.0.180   | true    |
| 230.15.26.79 | 193.230.15.26 | false   |
| 230.15.26.79 | 230.15.39.115 | false   |
+--------------+---------------+---------+

I could manually find if there are similar IPs to one in particular using the following command:

SELECT id, ip FROM users where ip LIKE "230.15.26.%"

However, this would mean that I have to loop the entire database, which is quite sizably voluminous.

Is there another way that I can use to do the described above with one to two queries only?

similarity ipv4 mysql
2 Answers

You can extract the required data with a query similar to:

SELECT SUBSTRING_INDEX( ip, '.', 3), COUNT(*)  
FROM ipadd
GROUP BY SUBSTRING_INDEX( ip, '.', 3)
HAVING COUNT(*) > 1

assuming a table structure in the lines of

create table ipadd(id INT, ip VARCHAR(15));

You can see it in action here

user_15051460

There is also a workaround with MySQL 8 Window Functions and Common Table Expressions. Maybe it will be faster than usual GROUP BY but it's needed to check:

WITH tmp AS (
    SELECT *, COUNT( * ) OVER ( PARTITION BY SUBSTRING_INDEX( ip, '.', 3 ) ) AS three_parts_of_this_ip_are_similar_in_N_ips FROM user_ips 
)
SELECT * 
FROM tmp 
WHERE three_parts_of_this_ip_are_similar_in_N_ips > 1

Supposed table and data:

DROP TABLE IF EXISTS user_ips;
CREATE TABLE user_ips ( user_id INT, ip VARCHAR ( 15 ) );
INSERT INTO user_ips ( user_id, ip )
VALUES
    ( 1, '230.15.26.79' ),
    ( 1, '32.82.0.5' ),
    ( 1, '230.15.26.230' ),
    ( 1, '32.82.0.180' ),
    ( 1, '193.230.15.26' ),
    ( 1, '230.15.39.115' );

You can see a demo here.

If you need count per user just add user field to PARTITION BY section.

user_74995540
Related