面试官一脸冷漠,你顿时意识到情况不对,又仔细琢磨了一下。
然后发现,这个 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 的取值区间为:
无符号 INT 的取值区间为:
下表列出了 MySQL 出各个整数类型有符号和无符号的的取值范围,在定义表时,可以在数据类型后面添加关键字?UNSIGNED?来定义无符号整数,否则默认为有符号整数:
类型
有符号数取值范围
无符号数取值范围
TINYINT(1 字节,8 bit)
-128 ? 127
0 ? 255
SMALLINT(2 字节,16 bit)
-32768 ? 32767
0 ? 65535
MEDIUMINT(3 字节,24 bit)
-8388608 ? 8388607
0 ? 16777215
INT(4 字节,32 bit)
-2147483648 ? 2147483647
0 ? 4294967295
BIGINT(8 字节,64 bit)
-9223372036854775808 ? 9223372036854775807
0 ? 18446744073709551615
结合上表,可以看出,32 位的无符号 ?INT?正好可以容纳 IPv4 地址,下面是?INT UNSIGNED?和?VARCHAR(15)?两种数据类型的对比:
存储空间:4 字节的?INT?类型 15 字节的 ?VARCHAR(15)?更加节省存储空间。另外,VARCHAR 除了会保存需要的字符数,还会另加一个字节来记录长度(如果列声明的长度超过 255,则使用两个字节记录长度),所以?VARCHAR(15)?其实要占用 16 个字节。
检索速度:如果我们要在 IP 地址上建立索引,那么对于字符串索引来说,整数索引的检索速度简直就是降纬打击了,因为字符串类型的比较是需要从第一位字符开始遍历依次进行的,速度较慢。
MySQL 非常贴心地提供了 IPv4 地址点分十进制和无符号整数的相互转换函数,inet_aton?和?inet_ntoa(底层是二进制移位操作,速度很快):
当然你更应该在业务中去执行这些转换,减轻 MySQL 的压力。
0 留言