MySQL check if an IP-address is in range?

Question

I have a table that contains two columns ipStart and ipEnd

These two fields contain a range of ip address. For example:

`ipStart` = 193.235.18.0

and

ipEnd = 193.235.21.255

I want to be able to select the row with an ip that is within the range, for example:

193.235.19.255

Is this possible?

I wouldn't do this in SQL. Do it in your fav. programming language which is the better way. — cb0, Jun 12 '14 at 08:12
have you tried between ? SELECT * FROM Products WHERE Price BETWEEN 10 AND 20; — Dwza, Jun 12 '14 at 08:12
@cb0 doing this in an application would mean retrieving the whole table every time. If the table is big, that is a problem. — ypercubeᵀᴹ, Jun 12 '14 at 08:50

score 24 · Answer 1 · answered Jun 12 '14 at 08:17

24

Try this

SELECT *
FROM TABLE_NAME
WHERE (INET_ATON("193.235.19.255") BETWEEN INET_ATON(ipStart) AND INET_ATON(ipEnd));

answered Jun 12 '14 at 08:17

Sadikhasan

18,365
21
80
122

How "heavy" would this be on a database containing over 60 mil records? – Alosyius Jun 12 '14 at 08:18
3

Very heavy since no index will be used. Better to have IPs as numerical values in the DB, then create an index on them. – Déjà vu Jun 12 '14 at 08:21
So I would store: 193.235.18.0 as this: 193235180 ? – Alosyius Jun 12 '14 at 08:21
5

No. Use the INET_ATON() function to do the conversion. It is effectively (a*256^3)+(b*256^2)+(c*256)+d. – Jaydee Jun 12 '14 at 08:46
@alosylus: No, not like that. It would be better to convert the IPs with the `INET_ATON()` function to a `BIGINT` and store the `ipSstart` and `ipEnd` as bigints. – ypercubeᵀᴹ Jun 12 '14 at 08:47
1

nice one. never heard about this one! :D *thumbs_up* – Dwza Jun 12 '14 at 10:31
But this will always be at least as slow as a full table table scan. If you want to use indexes to speed up the queries you need tuse spatial indexes - https://www.bignerdranch.com/blog/using-mysql-spatial-extensions-for-range-queries/ – symcbean Oct 23 '20 at 21:57

score 7 · Answer 2 · answered Jun 12 '14 at 08:44

To explain converting an ip address to a number which a few answers have relied on (and which I agree with).

The ip address needs to be treated as one 32 bit number rather than 4 8 bit numbers

For example the ip address

193.235.18.0

converted to binary is:-

11000001.11101011.00010010.00000000

Which you translate into (ie, take the dots out):-

11000001111010110001001000000000

Working that out you get:-

1 * 2147483648 = 2147483648 
1 * 1073741824 = 1073741824 
0 * 536870912 = 0
0 * 268435456 = 0
0 * 134217728 = 0
0 * 67108864 = 0
0 * 33554432 = 0
1 * 16777216 = 16777216 
1 * 8388608 = 8388608 
1 * 4194304 = 4194304 
1 * 2097152 = 2097152 
0 * 1048576 = 0
1 * 524288 = 524288 
0 * 262144 = 0
1 * 131072 = 131072 
1 * 65536 = 65536 
0 * 32768 = 0
0 * 16384 = 0
0 * 8192 = 0
1 * 4096 = 4096 
0 * 2048 = 0
0 * 1024 = 0
1 * 512 = 512 
0 * 256 = 0
0 * 128 = 0
0 * 64 = 0
0 * 32 = 0
0 * 16 = 0
0 * 8 = 0
0 * 4 = 0
0 * 2 = 0
0 * 1 = 0

Adding those together you get 3253408256

You an short cut that a bit by treating the original IP address as a base 256 number. So you have 0 units, 18 of 256s, 235 of 65536 (ie, 256 * 256) and 193 of (ie, 256 * 256 * 256)

0 * 1 + 18 * 256 + 235 * 256 * 256 + 193 * 256 * 256 * 256

INET_ATON function that is mentioned does this for you.

MySQL check if an IP-address is in range?

2 Answers2

Linked

Related