MySQL IPv4/IPv6高效存储

TrumanWong
2/24/2022
TrumanWong

IPv4简介+高效存储

人们经常使用VARCHAR(15)列来存储IP地址。然而,它们实际上是4字节的无符号整数,不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整型存储IP地址。MySQL提供INET_ATON()INET_NTOA()函数在这两种表示方法之间转换。

IPv6简介+高效存储(兼容IPv4

但是,不幸的是IPv4地址(约43亿个)已经用完了。所以,现在很多网址使用IPv6。简单的来说,IPv6是一个16字节的整数。如何高效地存储这个16字节的整数?当然,我们可以套用存储IPv4的思路,使用16字节的整数存储IPv6。可惜的是,MySQL不支持16字节的整数(最大为8字节的bigint类型),所以无法使用整数存储IPv6。那么,此时varchar似乎又成了首选答案,但真的应该使用varchar吗?不妨让我们先看看IPv6的三种常见的表示方式。

如果使用varchar类型保存IPv6的话,那么在存储以冒分十六进制表示的IPv6时,就需要39 + 1 = 40 个字节。可IPv6本质上一个16字节的数字,难道真的没有其它办法了吗?在《高性能MySQL 第三版》中有句话与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串和常规字符串非常相似,但是二进制字符串存储的是字节码而不是字符。同时,要注意到VARBINARY(m),其中m表示字节数。这样我们可以使用VARBINARY(16)存储IPv6地址,并且兼容存储IPv4地址。

mysql> CREATE TABLE `test` (
    ->   `ip` varbinary(16) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test(ip) values(INET6_ATON('2409:8924:1201:1e3e:3d41:3233:7791:ef3a'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(ip) values(INET6_ATON('2408:8426:d481:f9e4::1'));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(ip) values(INET6_ATON('172.241.112.79'));
Query OK, 1 row affected (0.01 sec)

mysql> select *, INET6_NTOA(ip) from test;
+------------------------------------+-----------------------------------------+
| ip                                 | INET6_NTOA(ip)                          |
+------------------------------------+-----------------------------------------+
| 0x2409892412011E3E3D4132337791EF3A | 2409:8924:1201:1e3e:3d41:3233:7791:ef3a |
| 0x24088426D481F9E40000000000000001 | 2408:8426:d481:f9e4::1                  |
| 0xACF1704F                         | 172.241.112.79                          |
+------------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

INET6_ATON() 将IPv6的字符串地址转换成数值(MySQL5.6+), INET6_NTOA() 将IPv6的数值转换成字符串地址(MySQL5.6+)。