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?
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
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.