文章目录

  • VARCHAR
  • UNSIGNED INT
  • VARCHAR VS UNSIGNED INT
  • 应尽量使用 UNSIGNED INT
  • 如何使用 UNSIGNED INT 存储IPv4
  • MySQL层
  • IP字符串 -> 整数：INET_ATON()
  • IP整数 -> 字符串：INET_NTOA()
  • 应用层
  • 如何存储IPv6
  • MySQL层
  • 方案4-VARBINARY
  • 应用层


VARCHAR

在人类可读的格式中，IP地址是一个字符串，最小长度为 7 个字符 (0.0.0.0)，最大长度为 15 个 (255.255.255.255)。这使它的平均长度（假设均匀随机分布）为 13.28 个字符。因此，可以将其存储在类型为VARCHAR(15)的数据库字段中。

MySQL在保存变长的字符串时，还需要额外的一个字节来保存此字符串的长度（255以内，超过255两个字节表示）。这使得以字符串表示的 IP 的实际数据存储成本平均为 14.28 字节（假设每个字符可以由一个字节表示，如编码latin1）。

UNSIGNED INT

使用32位的无符号整数（UNSIGNED INT）来存储IPv4地址，有以下好处：

  • 节省空间，不管是数据存储空间，还是索引存储空间
  • 便于使用范围查询（BETWEEN AND），且效率更高

缺点也比较明显：

  • 不便于阅读
  • 需要手动转换

VARCHAR VS UNSIGNED INT

虽然现今磁盘空间很便宜，对于这方面可能没什么实质的差距。但是，还有更大的数据字段带来的其他相关成本，如：

  • 如果该列被索引，索引也会更大；较大的索引往往比较小的索引执行得慢；
  • 虽然磁盘空间充足且便宜，但内存RAM的限制要大得多，因此将使用更多内存来缓存数据或索引，从而可能将其他更有价值的内容推出缓存。
  • 虽然磁盘逐渐变得更快，但从磁盘读取数据（写入数据甚至更长）仍然需要相对较长的时间，并且 CPU 的速度比磁盘快得多。移动的数据越多，CPU 浪费的时间就越多，而不是执行其他工作。

应尽量使用 UNSIGNED INT

  • 如果对存储的 IP 不需要进行很多查询，那么更改现有数据库可能没有意义。
  • 如果需要对 IP 范围进行很频繁的查询，还是建议更改为使用UNSIGNED INT存储IPP地址。
  • 默认情况下，任何新开发都应将 IP 地址作为整数存储在数据库中。节省的空间和时间是值得的。
  • 一旦 IPv6 变得更加流行，节省的成本只会越来越大：当以人类可读格式表示时，128 位（16 字节）IPv6 地址最多可以包含 39 个字符（ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff）（在数据库中存储 IPv6 地址会有点困难，因为 MySQL 没有本地 16 字节宽的数据类型，当前MySQL最大整型是8字节的BIGINT）。

如何使用 UNSIGNED INT 存储IPv4

IP字符串 <-> 整数 转换可以放在MySQL层函数处理，也可以在应用层手动自己处理。

MySQL层

IP字符串 -> 整数：INET_ATON()

select inet_aton('192.168.0.1'); //3232235521

IP整数 -> 字符串：INET_NTOA()

select inet_ntoa(3232235521); //192.168.0.1

应用层

public class IPv4LongUtils {
    /**
     * 把字符串IP转换成long
     *
     * @param ipStr 字符串IP
     * @return IP对应的long值
     */
    public static long ip2Long(String ipStr) {
        String[] ip = ipStr.split("\\.");
        return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16)
                + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]);
    }

    /**
     * 把IP的long值转换成字符串
     *
     * @param ipLong IP的long值
     * @return long值对应的字符串
     */
    public static String long2Ip(long ipLong) {
        StringBuilder ip = new StringBuilder();
        ip.append(ipLong >>> 24).append(".");
        ip.append((ipLong >>> 16) & 0xFF).append(".");
        ip.append((ipLong >>> 8) & 0xFF).append(".");
        ip.append(ipLong & 0xFF);
        return ip.toString();
    }

    public static void main(String[] args) {
        System.out.println(ip2Long("192.168.0.1")); //3232235521
        System.out.println(long2Ip(3232235521L)); //192.168.0.1
        System.out.println(ip2Long("10.0.0.1")); //167772161
    }

}

如何存储IPv6

MySQL层

  1. VARCHAR类型存储，这样需要8(段) * 4(字符) = 32个字符即可，加上记录字符串长度的一个字节(255以内，超过255两个字节表示)，总共33字节（假设每个字符可以由一个字节表示，如编码为latin1）；
  2. 2 * BIGINT，用2个UNSIGNED BIGINT字段进行存储；
  3. 4 * INT，用4个UNSIGNED INT字段进行存储；
  4. VARBINARY，分配8(段) * 4(字符) * 4(位) = 128 BIT空间。

方案4-VARBINARY

BINARY和VARBINARY与 CHAR和VARCHAR类型有点类似，不同的是BINARY和VARBINARY存储的是二进制的字符串，而非字符型字符串。

也就是说，BINARY和VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比。

BINARY(N)和VARBINARY(N)中的N指的是字节长度，而CHAR(N)和VARCHAR(N)中N指的是的字符长度。

对于BINARY(N)，其可存储的字节固定为10，而对于CHAR(N)，其可存储的字节视字符集的情况而定。

show create table test;

结果：

CREATE TABLE `test` (
  `ip` varbinary(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

与IPv4相似的，字符串 <-> 整数转换，MySQL同样对IPv6提供了相互转换的函数：INET6_ATON <-> INET6_NTOA

insert test (ip) values (inet6_aton('ABCD:EF01:2345:6789:ABCD:EF01:2345:6789'));
select inet6_ntoa(ip) from test;

应用层

同IPv4一样，也可以在应用层处理。



参考：

如果要存 IP 地址，用什么数据类型比较好？大部人都会答错！

MySQL Performance Benefits of Storing Integer IP Addresses