user3467855 user3467855 - 1 month ago 11
Linux Question

Convert multiple ip addresses from long form to integer

I am trying to convert multiple ip addresses IPv4 and IPv6 from 86.120.51.222 to 1450718174. Currently trying to build the query but don't know much sql

TABLE ip_city_country_location

+----+---------+---------------+-------------+
| ID | ip_from | ip_to | city |
+----+---------+---------------+-------------+
| 1 | 1.2.3.4 | 1.255.255.255 | city_name_1 |
| 2 | 1.3.4.4 | 1.6.0.0 | city_name_2 |
| 3. | 1.0.0.0 | 1.5.5.5 | city_name_3 |
+----+---------+---------------+-------------+


What I am thinking on

UPDATE ip_city_country_location SET ip_from = INET_ATON(SELECT ip_from FROm ip_city_country_location), ip_to = INET_ATON(SELECT ip_to FROm ip_city_country_location);


Notice can also be done with
CASE
and
WHEN
AND
THEN
, but need another a solution that converts automatically because this is a dbs of 5 million rows and need to run it from shell (xampp). Like to create a function in sql or smth.

EXPECTED OUTPUT

+----+---------+---------------+-------------+
| ID | ip_from | ip_to | city |
+----+---------+---------------+-------------+
| 1 | 16909060 | 33554431 | city_name_1 |
| 2 | 16974852 | 17170432 | city_name_2 |
| 3. | 16777216 | 17106181 | city_name_3 |
+----+---------+---------------+-------------+

Answer

The query you are looking for is pretty straightforward.

Here's the query:

UPDATE ip_city_country_location SET ip_from = INET_ATON(ip_from), ip_to = INET_ATON(ip_to);

Note:

After converting the ip_addresses to integer value you should change the datatype to INTEGER.

Index on ip_from and ip_to fields would speed up your select queries.

Comments