MySQL中用什么数据类型存IP地址

提到IP地址(IPv4)，我们脑子里肯定立马浮现类似于192.168.0.1、127.0.0.1这种常见的IP地址，然后结合这个问题“MySQL中用什么数据类型存IP地址？”，于是乎脱口而出用char字符串类型存储。

然后再仔细想想发现，这个IP地址的长度是变化的，最短可以是0.0.0.0只需要 7 位，最长可以是255.255.255.255需要15位，于是自信地回答使用varchar(15)来存储 IP 地址，并为自己能够想到这一层而暗自窃喜。

人们经常使用varchar(15)列来存储 IP 地址，但事实上这并不是最优解。

IP地址的本质是32位无符号整数，类似于192.168.0.1这种点分十进制的字符串写法只是为了帮助人们理解和记忆，192.168.0.1对应的十进制表示是无符号整数3232235521。

所以说用字符串类型存IP 地址的，其实是潜意识中以为IP地址是字符串。实际存的是点分十进制的字符串，但正确的应该是存32位的无符号整数。

所谓有符号数其实就是将最高位作为符号位，比如32位的有符号int类型，最高位是符号位，剩下 31位才是真实的数值，所以有符号int类型的取值区间为：

[-2^31,2^3-1]

无符号int类型的取值区间为：

[0,2^32]

下表列出了MySQL中各个整数类型有符号和无符号的的取值范围，在定义表时，可以在数据类型后面添加关键字 UNSIGNED 来定义无符号整数，否则默认为有符号整数： 

特别说明：ASCII码中，1个汉字字符存储需要2个字节，1个英文字符存储需要1个字节。

结合上表，可以看出32位的无符号 int类型正好可以容纳 IPv4 地址，下面是 INT UNSIGNED 和 VARCHAR(15) 两种数据类型的对比：

• 存储空间： 4 字节的int类型比15字节的varchar(15)更加节省存储空间。另外varchar除了会保存需要的字符，还会另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节记录长度)，所以varchar(15)其实要占用 16 个字节。

• 检索速度：如果我们要在 IP 地址上建立索引，那么对于字符串索引来说，整数索引的检索速度简直就是降纬打击了，因为字符串类型的比较是需要从第一位字符开始遍历依次进行的，速度较慢。

MySQL非常贴心地提供了IPv4地址点分十进制和无符号整数的相互转换函数，inet_aton和 inet_ntoa（底层是二进制移位操作，速度很快）。

点分十进制 -> 十进制：

SELECT INET_ATON('192.168.0.1');

十进制 -> 点分十进制：

SELECT INET_NTOA('3232235521');

 

当然我们更应该在业务中去执行这些转换，减轻 MySQL 的压力：

public class Demo {

    public static void main(String[] args) {
        String ipStr = "192.168.0.1";
        long ip =ip2int(ipStr);
        System.out.println(ip);
    }

    public static long ip2int(String ip) {
        String[] items = ip.split("\\.");
        return Long.valueOf(items[0]) << 24 | Long.valueOf(items[1]) << 16 | Long.valueOf(items[2]) << 8 | Long.valueOf(items[3]);
    }

    public static String int2ip(long ipInt) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append((ipInt >> 24) & 0xFF).append(".");
        stringBuilder.append((ipInt >> 16) & 0xFF).append(".");
        stringBuilder.append((ipInt >> 8) & 0xFF).append(".");
        stringBuilder.append(ipInt & 0xFF);
        return stringBuilder.toString();
    }
}