mysql - 将 ipv4 和 ipv6 地址转换为整数

Question

我正在尝试将多个 IP 地址 IPv4 和 IPv6 从 86.120.51.222 转换为 1450718174。目前正在尝试构建查询但不太了解 sql

表 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 |
+----+---------+---------------+-------------+

我在想什么

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也可以用CASE和WHEN AND THEN来完成，但是需要另一个自动转换的解决方案，因为这是一个dbs 500 万行 并且需要从 shell (xampp) 运行它。喜欢在 sql 或 smth 中创建一个函数。

预期输出

+----+---------+---------------+-------------+
| 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 1

您要查找的查询非常简单。

这是查询:

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

注意:

• 将 ip_addresses 转换为 integer 值后，您应该将数据类型更改为 VARBINARY(16)。
• ip_from 和 ip_to 字段的索引会加快您的选择查询。

编辑:

为了转换 IPV6 地址你需要 MySQL 服务器版本 >= 5.6.3。

查询如下所示:

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));

来源:感谢@Michael 的宝贵意见。

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.