参考文章:
MySQL如何有效的存储IP地址?-阿里云开发者社区 (aliyun.com)
如何在mysql数据库中ipv6 in整数? - 问答 - 腾讯云开发者社区-腾讯云 (tencent.com)
在MySQL中存储IPv6地址 - VoidCC
MySQL中如何正确存储IP地址 - 实用教程 - 慢慢来比较快 (gaoyaxuan.net)
MySQL中存储IP地址(IPv4&IPv6)的方案对比 (copyfuture.com)

IP地址介绍

  1. IPV4:
    1. 具有32位(4字节)地址长度。
    2. 数据包需要576个字节,碎片可选
    3. 包头的长度为20个字节,不识别用于QoS处理的数据包流包含checksum,包含最多40个字节的选项字段
    4. (A)记录,映射主机
    5. IPv4协议的IPSec支持只是可选的
    6. 不提供提供身份验证和加密
  2. IPV6:
    1. 具有128位(16字节)地址长度。
    2. IPv6协议的数据包需要1280个字节,不会碎片
    3. 包头的长度为40个字节,包含指定QoS处理的数据包流的Flow Label字段,不包含checksum,没有选项字段,但IPv6扩展标头可用。
    4. (AAAA)记录,映射主机名
    5. 提供身份验证和加密

使用MySql存储IP 地址

Varchar

V4 && v6

IPV4地址为:xxx.xxx.xxx.xxx字符存储则需要varchar(15)即可存储,但是Mysql中变长字符会多占用一个Byte来存储位置。存储与索引速度较慢
IPV6需要:varchar(39)

优点:

  1. 可以存储IPv6,增加一个判断是否为IPv6的字段可以判断类型是IPv4还是IPv6。
  2. IP地址直观,不需要任何转换就能看出是什么IP
  3. 只要位数设置足够长就能存储IPv6的地址
  4. 可以添加索引加快查询速度

缺:

  1. 不能对IP地址进行准确的排序和范围查找
  2. 同一个IP换了一种形式出现会被划分为不同的IP,如:IPv6的缩写形式和非缩写形式
  3. 需要的存储空间相对较大

UNSIGNED INT

**UINT(4)**:IPV4
IP地址实际上就是一串4Byte的无符号数,为了方便划分我们使用.隔开,这里我们可以通过转化压缩为无符号数进行存储。

优:

  1. 占用内存空间少
  2. 可以对IP排序和指定范围查找
  3. MySQL中有对应的转换函数支持
  4. 因为是数值类型,加上索引之后查找速度相对以下方法都要快

缺:只支持IPv4类型的IP地址,IPv6转换成整形数据后长度不足

Varbinary

  1. 采用数据库自带函数进行转化:
    1. V4:varbinary(32)
      1. IP -> UInt:INET_ATON()
      2. UInt -> IP:INET_NTOA()
    2. V6:varbinary(128)
      1. IP -> UInt:INET6_ATON()
      2. UInt -> IP:INET6_NTOA()

因此,需要范围查询,且数据量很大(如亿级以上),采用数值存储IP地址的方式更优。如果均是唯一IP精确查询,或数据量不大,那么使用字符串操作更为简单。

查询插入

IPV6为例

查询:

SELECT id,INET6_NTOA(ip_address) AS ip FROM `example`;

插入:

INSERT INTO example VALUES (INET6_ATON('AD80:0000:0000:0000:ABAA:0000:00C2:0002'));