MySQL中存储IP地址(IPv4&IPv6)的方案对比

前言

本文讨论的是使用MySQL进行存储IP地址的方案，并对比每个方案的优缺点和适合的场景，并总结出我个人认为其中比较好的方案。
其实在条件允许的情况下存储IP地址最好的方案（结构化数据）是使用postgreSQL数据库进行存储，因为postgreSQL中有一个专门用来存储IP地址的数据类型inet。但是很多时候可能是项目已经使用了MySQL数据库了在后面的需求才增加了IP地址的存储，此时如果需要更换数据库可能需要更多的成本。
以下提出的方案是我目前想到的方案，如果有其他方案（不一定最优方案，可行就可以）也可以留言告诉我，咋们一起探讨。

---

正文

方案一： 使用BIGINT数据类型存储IP地址

这个方法十分的流行，可以观察得到很多地方的IP地址都是用这个方式进行存储

• 优点：

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

• 缺点：

1. 只支持IPv4类型的IP地址，IPv6转换成整形数据后长度不足

• 适用场景：
  明确只会存储IPv4的IP地址时这个方案十分好用，但是如果出Pv6这个方案完全失效。

方案二： 使用两个BIGINT或者4个INT类型存储IP地址

• 优点：

1. 能够存储IPv6数据。并且不需要特定的判断IPv4和IPv6的字段，高位是否为0直接能判断IPv4还是IPv6。
2. 能对IP进行排序和范围查找

• 缺点：

1. 不好添加索引
2. MySQL没有提供对应的函数，需要比较复杂的位运算

• 适用场景
  在旧版MySQL还没提供IPv6转换其他类型的时候使用，个人觉得此方案卖力不讨好，使用此方案还不如更新一下数据库版本换个方案。

方案三： 使用VARCHAR直接存IP地址

• 优点：

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

• 缺点：

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

• 适用场景
  仅仅用作展示IP地址（不需要进行查找、排序等操作）的时候可以考虑使用

方案四： 使用VARCHAR直接存IP地址的INT十进制类型数值

• 优点：

1. 可以存储IPv6，直接通过实际字符串长度判断类型是IPv4还是IPv6。
2. 方便加索引
3. 能对IP进行排序和范围查找
4. 能区分同一个IP不同形式的展现

• 缺点：

1. 存储空间相对较大
2. 有时候可能需要使用多层嵌套内置函数转换最终想要的数据呈现

• 适用场景
  因为只需要一个函数就能把varchar转成int但是转为一般的IP地址形式需要多层函数并且还要增加判断语句，所以当IP只用作排序或范围查找不用做展示的情况下可以使用这个方法

方案五： 使用VARCHAR存储IP地址的十六进制（推荐）

• 优点：

1. 能存储IPv6，可以通过字符位数或者内置函数判断IPv6还是IPv4
2. 在能存储IPv6的方案中存储空间相对较少
3. 新版MySQL有提供较多的对应类型转换的函数
4. 能对IP进行排序和范围查找
5. 能区分同一个IP不同形式的展现

• 缺点：

1. 有时候可能需要使用多层嵌套内置函数转换最终想要的数据呈现

• 适用场景
  此方案无论展示还是运算都不算复杂，所以推荐此方案。

---

总结

以上方案都是可行的，可以根据不同的需要使用不同的方案，但是总的来说比较推荐使用方案五。光说不做还不行，我有空再把各个方案的用法和参考例子补上，暂时先这样发布单纯的文字，感兴趣可以关注我这篇文章的更新，如果有网友提供新方案我也会加进去。感谢支持，如有错漏请友好地指出，谢谢。