CODEWITHSUNDEEP
mysqlsqllinux
Adrian
Adrian

Reputation: 2291

Convert ipv4 and ipv6 addresses 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 |
+----+---------+---------------+-------------+

Upvotes: 2

Views: 4742

Answers (1)

1000111
1000111

Reputation: 13519

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 VARBINARY(16).
  • Index on ip_from and ip_to fields would speed up your select queries.

EDIT:

In order to convert IPV6 addresses you need MySQL server version >= 5.6.3.

And the query would look like below:

UPDATE 
ip_city_country_location 
SET ip_from = IF(IS_IPV6(ip_from),INET6_ATON(ip_from), INET_ATON(ip_from)),         
    ip_to = IF(IS_IPV6(ip_to),INET6_ATON(ip_to), INET_ATON(ip_to));

Credit: Thanks to @Michael for his valuable comment.

You cannot store the equivalent of an IPv6 address in an integer column, as IPv6 addresses are 128 bits long, while the longest integer supported by MySQL is BIGINT UNSIGNED, which is only 64 bits. INET6_ATON() returns a VARBINARY(16) for IPv6 addresses.

Upvotes: 3

Related Questions