MySQL中存储IP地址(IPv4&IPv6)的方案对比
- 前言
- 正文
- 方案一: 使用BIGINT数据类型存储IP地址
- 方案二: 使用两个BIGINT或者4个INT类型存储IP地址
- 方案三: 使用VARCHAR直接存IP地址
- 方案四: 使用VARCHAR直接存IP地址的INT十进制类型数值
- 方案五: 使用VARCHAR存储IP地址的十六进制(推荐)
- 总结
前言
本文讨论的是使用MySQL进行存储IP地址的方案,并对比每个方案的优缺点和适合的场景,并总结出我个人认为其中比较好的方案。
其实在条件允许的情况下存储IP地址最好的方案(结构化数据)是使用postgreSQL数据库进行存储,因为postgreSQL中有一个专门用来存储IP地址的数据类型inet。但是很多时候可能是项目已经使用了MySQL数据库了在后面的需求才增加了IP地址的存储,此时如果需要更换数据库可能需要更多的成本。
以下提出的方案是我目前想到的方案,如果有其他方案(不一定最优方案,可行就可以)也可以留言告诉我,咋们一起探讨。
正文
方案一: 使用BIGINT数据类型存储IP地址
这个方法十分的流行,可以观察得到很多地方的IP地址都是用这个方式进行存储
- 优点:
- 占用内存空间少
- 可以对IP排序和指定范围查找
- MySQL中有对应的转换函数支持
- 因为是数值类型,加上索引之后查找速度相对以下方法都要快
- 缺点:
- 只支持IPv4类型的IP地址,IPv6转换成整形数据后长度不足
- 适用场景:
明确只会存储IPv4的IP地址时这个方案十分好用,但是如果出Pv6这个方案完全失效。
方案二: 使用两个BIGINT或者4个INT类型存储IP地址
- 优点:
- 能够存储IPv6数据。并且不需要特定的判断IPv4和IPv6的字段,高位是否为0直接能判断IPv4还是IPv6。
- 能对IP进行排序和范围查找
- 缺点:
- 不好添加索引
- MySQL没有提供对应的函数,需要比较复杂的位运算
- 适用场景
在旧版MySQL还没提供IPv6转换其他类型的时候使用,个人觉得此方案卖力不讨好,使用此方案还不如更新一下数据库版本换个方案。
方案三: 使用VARCHAR直接存IP地址
- 优点:
- 可以存储IPv6,增加一个判断是否为IPv6的字段可以判断类型是IPv4还是IPv6。
- IP地址直观,不需要任何转换就能看出是什么IP
- 只要位数设置足够长就能存储IPv6的地址
- 可以添加索引加快查询速度
- 缺点:
- 不能对IP地址进行准确的排序和范围查找
- 同一个IP换了一种形式出现会被划分为不同的IP,如:IPv6的缩写形式和非缩写形式
- 需要的存储空间相对较大
- 适用场景
仅仅用作展示IP地址(不需要进行查找、排序等操作)的时候可以考虑使用
方案四: 使用VARCHAR直接存IP地址的INT十进制类型数值
- 优点:
- 可以存储IPv6,直接通过实际字符串长度判断类型是IPv4还是IPv6。
- 方便加索引
- 能对IP进行排序和范围查找
- 能区分同一个IP不同形式的展现
- 缺点:
- 存储空间相对较大
- 有时候可能需要使用多层嵌套内置函数转换最终想要的数据呈现
- 适用场景
因为只需要一个函数就能把varchar转成int但是转为一般的IP地址形式需要多层函数并且还要增加判断语句,所以当IP只用作排序或范围查找不用做展示的情况下可以使用这个方法
方案五: 使用VARCHAR存储IP地址的十六进制(推荐)
- 优点:
- 能存储IPv6,可以通过字符位数或者内置函数判断IPv6还是IPv4
- 在能存储IPv6的方案中存储空间相对较少
- 新版MySQL有提供较多的对应类型转换的函数
- 能对IP进行排序和范围查找
- 能区分同一个IP不同形式的展现
- 缺点:
- 有时候可能需要使用多层嵌套内置函数转换最终想要的数据呈现
- 适用场景
此方案无论展示还是运算都不算复杂,所以推荐此方案。