Mysql IP地址存储

发表于 更新于 分类于 ， 阅读次数： 阅读次数： Artalk： 本文字数： 1.4k 阅读时长 ≈ 1 分钟

参考文章：
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为例

查询：

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

插入：

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