Geek Answers Handbook

Does MySQL check if the IP address is in the range?

I have a table containing two columns ipStart and ipEnd

These two fields contain the range of ip addresses. For example:

 `ipStart` = 193.235.18.0

and

 ipEnd = 193.235.21.255

I want to be able to select a string with ip within the range, for example:

 193.235.19.255

Is it possible?

+7
mysql
source share
2 answers

try it

 SELECT * FROM TABLE_NAME WHERE (INET_ATON("193.235.19.255") BETWEEN INET_ATON(ipStart) AND INET_ATON(ipEnd));
+11
source share

To explain the conversion of the ip address to a number that several answers have relied on (and with which I agree).

The IP address should be considered as one 32-bit number, not 4 8-bit numbers

For example, ip address

193.235.18.0

converted to binary: -

11000001.11101011.00010010.0000000000

What do you translate (i.e. take out points): -

11000001111010110001001000000000

Working on this, 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

Add them together, you get 3253408256

You cut it short by considering the source IP address as the base number of 256. Thus, you have 0 units, 18 of 256, 235 of 65536 (i.e. 256 * 256) and 193 (i.e. 256 * 256 * 256).

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

The INET_ATON function in question does this for you.

+2
source share

More articles:


All Articles