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+)。