how to filter ip address in mysql database which is stored as varchar

Question

I have user table which has username and ip-address.

Suppose I need all data whose IP address is between 5.101.108.0 and 5.101.108.255

How do I write query for it as IP is stored in varchar. Do I need to use regular expression for that as I will have to filter after 5.101.108. ?

Can you give a sample solution as I am not able to think how to filter. Thanks.

score 6 · Accepted Answer · answered Jan 15 '17 at 21:21

6

You can use INET_ATON() and INET_NTOA() to convert between IP string and integer representation:

SELECT *
  FROM ip_addresses
 WHERE INET_ATON(ip_address) BETWEEN INET_ATON('5.101.108.0') AND INET_ATON('5.101.108.255');

answered Jan 15 '17 at 21:21

shmosel

`INET_ATON(ip_address)` here ip_address is the column name or i have to write that – Phoenix Jan 15 '17 at 21:23
@Phoenix That's the assumed column name. – shmosel Jan 15 '17 at 21:23
1

How would on-the-fly conversion be on performance? I'm curious. Imagine if you have a table of 10 million IP records—there is no index to lookup on, and the engine has to convert every single IP address. Probably the best to **store** the IP addresses, converted by `INET_ATON()` into `VARBINARY` columns at the first place. – Terry Jan 15 '17 at 21:23
2

Note, though, that this will be slow on large tables, as it has to run the `INET_ATON` calculation on every row in the table. If you have to do this frequently on large tables, best to store the data as `INET_ATON` to start with. (edit: @Terry beat me by 4s!) – ceejayoz Jan 15 '17 at 21:23
I will let you as soon as it gives result. I have very big database with around 20 years of data as soon as it filters I will update you. Thanks. – Phoenix Jan 15 '17 at 21:25
SIR. YOU Are AWESOME! Thanks Worked! I can't accept until 5 min I will accept answer as soon as possible. – Phoenix Jan 15 '17 at 21:28
1

tick tock tick tock – Strawberry Jan 15 '17 at 21:33
see: http://stackoverflow.com/questions/35562416/mysql-query-to-convert-cidr-into-ip-range/35564156 – Bernd Buffen Jan 15 '17 at 21:41

1 Answers1