env

MySQL 存储 IP 地址需要存 int ？

最近搜索MySQL表数据类型，查到一篇文章，IPv4 地址存 MySQL 的讲究。这个存法好像工程实践中并不常见（或许是没注意到），搜索学习一下。

举例

mysql提供了两个方法来处理IP地址：

  • inet_aton 把IP转为无符号整型(4-8位)
  • inet_ntoa 把整型的IP转为点隔地址

示例：

1
2
3
4
5
6
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL COMMENT '用户名',
  `ip` bigint(20) DEFAULT NULL COMMENT 'IP地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

bigint? 应该是 int unsigned

1
2
3
4
5
INSERT INTO `t_user` ( `id`, `name`, `ip` )
VALUES
    ( 2, 'babala', inet_aton( '127.0.0.1' ) ),
    ( 3, 'maly', inet_aton( '192.168.1.1' ) ),
    ( 4, 'kaven', inet_aton( '111.175.7.143' ) );

查询时显示点号间隔的地址：

1
select id,name,inet_ntoa(ip) as ip from `t_user`;

原理

IP的格式是A.B.C.D，其中A,B,C,D均为0~255内的整数。
0~255就是一个8位的2进制的数，00000000（0） - 11111111（255）。

整个ip就是一个32位的2进制数，范围是：
00000000 00000000 00000000 00000000 - 11111111 11111111 11111111 11111111，即 0 - 4294967295。

mysql里面的数据类型

1
2
3
4
5
TINYINT           -128到127
SMALLINT        -32768到32767
MEDIUMINT      -8388608到8388607
INT             -2147483648到2147483647
BIGINT          -9223372036854775808到9223372036854775807

一个IPv4的地址总体上刚好是32位二进制数，只是用了“.”符号每八位进行了一个分割，所以我们只要使用一个32位的无符号整型来存储即可，这样只要4字节，如果使用字符串则需要更多的字节，我们需要做的就是每次在使用IP地址的时候从无符号整形到IP地址进行一步转换工作就可以了。

java里面的数据类型
Long.MAX_VALUE = 0x7fffffffffffffffL = 9223372036854775807
Integer.MAX_VALUE = 0x7fffffff = 2147483647 < 4294967295
所以java里面计算ip转成整型需要使用 Long 类型。

mysql 的 inet_aton() 将IP转为十进制数字：就是把IP的每一段转为二进制拼接起来，然后将这个32位的二进制数字转为10进制。

为什么这么存储？

有人说节省空间，也对，不过现在都在玩大数据海量存储，各种长文本各种埋点乱存，还在乎这点空间？
更为合理的解释是， 方便查询， 比如要查某一网段的所有IP就可以：

1
select* from table where ip between inet_aton('192.168.0.1') and inet_aton('192.168.0.255');

Java 处理

（备注：代码来源https://zhuanlan.zhihu.com/p/148667889）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
package com.java.mmzsit;

/**
 * @author ：mmzsblog
 * @description：Ipv4地址的转换
 * @date ：2020/5/27 22:43
 */
public class Ipv4Covert {
    public static void main(String[] args) {
        String ip = "10.108.149.219";

        // step1: 分解IP字符串，并对应写对字节数组
        byte[] ip1 = ipToBytes(ip);

        // step2: 对字节数组里的每个字节进行左移位处理，分别对应到整型变量的4个字节
        int ip2 = bytesToInt(ip1);
        System.out.println("整型ip ----> " + ip2);

        // step3: 对整型变量进行右位移处理，恢复IP字符串
        String ip3 = intToIp(ip2);
        System.out.println("字符串ip---->" + ip3);

    }

    
    /**
     * 把IP地址转化为int
     * @param ipAddr
     * @return int
     */
    public static byte[] ipToBytesByReg(String ipAddr) {
        byte[] ret = new byte[4];
        try {
            String[] ipArr = ipAddr.split("\\.");
            ret[0] = (byte) (Integer.parseInt(ipArr[0]) & 0xFF);
            ret[1] = (byte) (Integer.parseInt(ipArr[1]) & 0xFF);
            ret[2] = (byte) (Integer.parseInt(ipArr[2]) & 0xFF);
            ret[3] = (byte) (Integer.parseInt(ipArr[3]) & 0xFF);
            return ret;
        } catch (Exception e) {
            throw new IllegalArgumentException(ipAddr + " is invalid IP");
        }
    }



    /**
     * 第一步，把IP地址分解为一个btye数组
     */
    public static byte[] ipToBytes(String ipAddr) {
        // 初始化字节数组，定义长度为4
        byte[] ret = new byte[4];
        try {
            String[] ipArr = ipAddr.split("\\.");
            // 将字符串数组依次写入字节数组
            ret[0] = (byte) (Integer.parseInt(ipArr[0]));
            ret[1] = (byte) (Integer.parseInt(ipArr[1]));
            ret[2] = (byte) (Integer.parseInt(ipArr[2]));
            ret[3] = (byte) (Integer.parseInt(ipArr[3]));
            return ret;
        } catch (Exception e) {
            throw new IllegalArgumentException("invalid IP : " + ipAddr);
        }
    }

    /**
     * 根据位运算把 byte[] -> int
     * 原理：将每个字节强制转化为8位二进制码，然后依次左移8位，对应到Int变量的4个字节中
     */
    public static int bytesToInt(byte[] bytes) {
        // 先移位后直接强转的同时指定位数
        int addr = bytes[3] & 0xFF;
        addr |= ((bytes[2] << 8) & 0xFF00);
        addr |= ((bytes[1] << 16) & 0xFF0000);
        addr |= ((bytes[0] << 24) & 0xFF000000);
        return addr;
    }

    /**
     * 把int->string地址
     *
     * @param ipInt
     * @return String
     */
    public static String intToIp(int ipInt) {
        // 先强转二进制，再进行移位处理
        return new StringBuilder()
                // 右移3个字节（24位），得到IP地址的第一段也就是byte[0],为了防止符号位是1也就是负数，最后再一次& 0xFF
                .append(((ipInt & 0xFF000000) >> 24) & 0xFF).append('.')
                .append((ipInt & 0xFF0000) >> 16).append('.')
                .append((ipInt & 0xFF00) >> 8).append('.')
                .append((ipInt & 0xFF))
                .toString();
    }
}