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?
The easiest way to determine the range is to convert the IP into a decimal format and then do the ordinary comparing statements necessary. You can do this by assigning a weight for each part of the IP. Each part has a maximum value of 255. Thus, the best weight you can choose is 256.
The SQL query SHOW VARIABLES WHERE Variable_name = 'hostname' will show you the hostname of the MySQL server which you can easily resolve to its IP address. Will give you the port number. You can find details about this in MySQL's manual: https://dev.mysql.com/doc/refman/8.0/en/show-variables.html.
MySQL. While MySQL doesn't have a data type for IP addresses, it's still easy to compare IPs in MySQL. Using inet_aton you can convert your IP addresses to integers before you compare.
Try this
SELECT *
FROM TABLE_NAME
WHERE (INET_ATON("193.235.19.255") BETWEEN INET_ATON(ipStart) AND INET_ATON(ipEnd));
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With