How to store IP addresses in MySQL efficiently?

A few days ago, a friend of mine went to an interview. He came back and told me that the interviewer asked him how the IP address was stored in the database. He didn’t think much about it at the time and directly answered that it was stored in a string (thinking: Such a simple question, I’m afraid he is looking down on me)

The previous paragraph is just for reference. After all, an IP address is originally a string, and it is understandable to store it in the database as a character string. However, I am a person who likes to think from the perspective of others. From the perspective of the interviewer, do you think I would ask such a low-level question? Then of course the answer is no. Therefore, what the interviewer wants to know is whether you have a deep thought about this question, and from this, to a certain extent judge whether you are just a simple "brick-moving" coder in your daily development, or a coder with soul.

[[328314]]

Preface

Regarding this question, first of all, let me state that it is completely fine to store IP addresses in the database as strings. Then you may have a question? If there is no problem, then why are you comparing it here?

Although this is a contradictory topic, we have other storage methods besides storing strings. For example, the more common method is to store IP addresses as int data. Although this storage method is not complicated to implement, the fact that you can think of this method also shows that you are a good thinker and have a good grasp of the underlying data foundation.

Because an int type data occupies 4 bytes, each byte is 8 bits, and its range is 0~(2^8-1), and the IPv4 address can be divided into 4 segments, each segment ranges from 0 to 255, which is just enough to be stored, so by slightly converting it, the IP address is cleverly stored in the database with the smallest space (unless otherwise specified in the following description, it refers to the IPv4 address). You may think that this small change does not matter, but when the amount of data increases, the difference between 15 bytes and 4 bytes will surprise you. So when designing a database, use the appropriate field type, just enough, and save as much as possible.

As in Section 4.1.7 of High Performance MySQL 3rd Edition, the author recommends that when storing IPv4 addresses, 32-bit unsigned integers (UNSIGNED INT) should be used to store IP addresses instead of using strings.

Compared with string storage, using unsigned integers for storage has the following advantages:

• Save space, both data storage space and index storage space
• It is convenient to use range query (BETWEEN...AND) and more efficient

Usually, when saving an IPv4 address, an IPv4 address requires at least 7 characters and at most 15 characters, so VARCHAR(15) is sufficient. When MySQL saves a variable-length string, it also requires an extra byte to save the length of the string. However, if an unsigned integer is used for storage, only 4 bytes are required. In addition, 4 fields can be used to store each part of the IPv4 address separately, but usually this will not be very efficient in terms of storage space and query efficiency (although some scenarios are suitable for this storage method). However, using unsigned integers for storage also has the disadvantages of being difficult to read and requiring manual conversion.

Tool class to implement conversion

To store the IP address as an int type in the database, one method is to use the shift operation and & calculation in the Java code to get the corresponding value:

 package com.java.mmzsit; 
 
 /**
 * @author ：mmzsblog
 * @description: IPv4 address conversion
 * @date ：2020/5/27 22:43
 */
 public class Ipv4Covert {
    public static void main(String[] args) {
        String ip = "10.108.149.219" ; 
 
 // step1: decompose the IP string and write the corresponding byte array
        byte[] ip1 = ipToBytes (ip); 
 
 // step2: Shift each byte in the byte array to the left, corresponding to the 4 bytes of the integer variable
        int ip2 = bytesToInt (ip1);
 System.out.println("integer ip ---- > " + ip2); 
 
 // step3: right shift the integer variable to restore the IP string
        String ip3 = intToIp (ip2);
 System.out.println("string ip---- > " + ip3); 
 
 } 
 
     
 /**
 * Convert IP address to 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");
 } 
 
 } 
 
 
 
 /**
 * The first step is to decompose the IP address into a bittype array
 */
    public static byte[] ipToBytes(String ipAddr) {
 // Initialize the byte array and define the length as 4
        byte[] ret = new byte[4];
 try {
            String[] ipArr = ipAddr .split("\\.");
 // Write the string array into the byte array one by one
            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);
 }
 } 
 
 /**
 * Convert byte[] - > int based on bitwise operation
 * Principle: Each byte is converted into 8-bit binary code, and then shifted left by 8 bits in sequence, corresponding to the 4 bytes of the Int variable
 */
    public static int bytesToInt(byte[] bytes) {
 // Shift first and then force conversion directly while specifying the number of digits
        int addr = bytes [3] & 0xFF;
        addr |= ((bytes[2] < <   8 ) & 0xFF00);
        addr |= ((bytes[1] < <   16 ) & 0xFF0000);
        addr |= ((bytes[0] < <   24 ) & 0xFF000000);
 return addr;
 } 
 
 /**
 * Convert int- > string address
 *
 * @param ipInt
 * @return String
 */
    public static String intToIp(int ipInt) {
 //First force binary conversion, then shift processing
        return new StringBuilder()
 // Shift right 3 bytes (24 bits) to get the first segment of the IP address, which is byte[0]. In order to prevent the sign bit from being 1, which is a negative number, & 0xFF again at the end
                .append(((ipInt & 0xFF000000) > > 24) & 0xFF).append('.')
                .append((ipInt & 0xFF0000) > > 16).append('.')
                .append((ipInt & 0xFF00) > > 8).append('.')
                .append((ipInt & 0xFF))
 .toString();
 }
 }

In fact, this is a kind of binary thinking, and it is also a number system widely used in computing technology. Although it is not used much in daily life, once you master it, it will help us strengthen our understanding of machine language and improve our coding level, especially when facing scenarios with tight resources (memory), it will help us analyze and optimize problems.

Database function implementation conversion

Another way is to use the database's built-in functions INET_ATON and INET_NTOA for conversion:

 mysql > SELECT INET_ATON('192.168.0.1');
 +--------------------------+
 | INET_ATON('192.168.0.1') |
 +--------------------------+
 | 3232235521 |
 +--------------------------+
 1 row in set 
 
 mysql > SELECT INET_NTOA(3232235521);
 +-----------------------+
 | INET_NTOA(3232235521) |
 +-----------------------+
 | 192.168.0.1 |
 +-----------------------+
 1 row in set

If it is an IPv6 address, use the functions INET6_ATON and INET6_NTOA to convert it:

 mysql > SELECT HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')); 
 
 +------------------------------------------------+
 | HEX(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')) |
 +------------------------------------------------+
 | 1030000000000000C9B4FF1248AA1A2B |
 +------------------------------------------------+
 1 row in set 
 
 mysql > SELECT INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B'));
 +-------------------------------------------------------------+
 | INET6_NTOA(UNHEX('1030000000000000C9B4FF1248AA1A2B')) |
 +-------------------------------------------------------------+
 | 1030::c9b4:ff12:48aa:1a2b |
 +-------------------------------------------------------------+
 1 row in set

Then the database is defined as varbinary type and 128 bits of space is allocated (because IPv6 uses 128 bits, 16 bytes); or defined as char type and 32 bits of space is allocated.

If you use the database function, you only need to do a slight conversion when entering the IP address into the database, which is convenient and quick; and at this point, don’t you think that converting the IP address into digital storage is a good choice? After all, the database has taken this into consideration for us, and it also indirectly proves that this is indeed a feasible and good storage method.