Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets in to the MySql table. Below is the dataset example containing columns-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us

Problem Statement:-

I am planning to store the START_IP_NUM and END_IP_NUM as BIGINT in the MySql database instead of storing IP Address in the table as doing a lookup against a BIGINT is far faster than searching a string (dotted ip notation).

So my question is we will be having separate Java Program that will populate the above datasets in the MySql table. So I need to device some sort of logic which can convert the above datasets into like below-

start_ip_num
end_ip_num
country 
region  
city           
metro-code  
latitude    
longitude   
postal-code   
country-code     
region-code 
city-code   
continent-code  
country-code

I am confuse given a IP Address how should I device start_ip_num and end_ip_num here and store it into the MySql table

share|improve this question
add comment

1 Answer

If I am reading your requirements correctly.

From the dataset provided the start_ip_num and end_ip_num will be the same.

Why you may ask? you are not storing a range of ip addresses; take for example a /24 range: 192.168.1.0/24 this provides a range 192.168.1.0 - 192.168.1.255, whilst really only ip's 192.168.1.1 though 192.168.1.254 are usable (.255 being the broadcast).

If you are storing a 'range' the start and end values will differ

mysql> select INET_ATON('192.168.1.1') as start_ip_num, INET_ATON('192.168.1.254') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|   3232235777 | 3232236030 |
+--------------+------------+
1 row in set (0.00 sec)

If not they will be the same, if you want the ranges for each a whois 24.32.116.116 will reveal this information.

e.g.

24.32.116.116 has 24.32.0.0/16

and 24.32.116.0/22 "Comment: Clarksville, Tx Customers"

The later providing the required information.

As such your 'range' is strictly speaking: 24.32.116.0 - 24.32.119.255

mysql> select INET_ATON('24.32.116.0') as start_ip_num, INET_ATON('24.32.116.255') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|    404780032 |  404780287 |
+--------------+------------+
1 row in set (0.00 sec)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.