Detailed explanation of how to effectively store IP addresses in MySQL and how to convert between string IP and numerical values

When looking at High Performance MySQL Version 3 (Section 4.1.7), the author recommends that when storing IPv4 addresses, 32-bit unsigned integers (UNSIGNED INT) should be used to store IP addresses instead of strings. But no specific reason was given. In order to find out the reason, I checked some information and recorded it.

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) can be used. When MySQL saves a variable-length string, it requires an extra byte to save the length of the string. If you use unsigned integers to store it, only 4 bytes are needed. In addition, you can use 4 fields to store each part of IPv4 separately, but usually this should not be very high in terms of storage space and query efficiency (there may be some scenarios where this storage method is suitable).

For detailed performance analysis and benchmarks of using strings and unsigned integers to store IP, see this article.

Using unsigned integers for storage also has disadvantages:

• Not easy to read
• Manual conversion required

For conversion, MySQL provides corresponding functions to convert IP in string format into integer INET_ATON, and to convert IP in integer format into string INET_NTOA. As shown below:

 mysql> select inet_aton('192.168.0.1');
+--------------------------+
| inet_aton('192.168.0.1') |
+--------------------------+
| 3232235521 |
+--------------------------+
1 row in set (0.00 sec)
 
mysql> select inet_ntoa(3232235521);
+-----------------------+
| inet_ntoa(3232235521) |
+-----------------------+
| 192.168.0.1 |
+-----------------------+
1 row in set (0.00 sec)

For IPv6, the same benefits can be obtained by using VARBINARY, and MySQL also provides corresponding conversion functions, namely INET6_ATON and INET6_NTOA.
For converting string IPv4 and numeric types, you can put it in the application layer. The following is the Java code to convert the two:

 package com.mikan;
 
/**
 * @author Mikan
 * @date 2015-09-22 10:59
 */
public class IpLongUtils {
    /**
     * Convert string IP to long
     *
     * @param ipStr string IP
     * @return the long value corresponding to IP*/
    public static long ip2Long(String ipStr) {
        String[] ip = ipStr.split("\\.");
        return (Long.valueOf(ip[0]) << 24) + (Long.valueOf(ip[1]) << 16)
                + (Long.valueOf(ip[2]) << 8) + Long.valueOf(ip[3]);
    }
 
    /**
     * Convert the IP long value into a string*
     * @param ipLong IP's long value* @return the string corresponding to the long value*/
    public static String long2Ip(long ipLong) {
        StringBuilder ip = new StringBuilder();
        ip.append(ipLong >>> 24).append(".");
        ip.append((ipLong >>> 16) & 0xFF).append(".");
        ip.append((ipLong >>> 8) & 0xFF).append(".");
        ip.append(ipLong & 0xFF);
        return ip.toString();
    }
 
    public static void main(String[] args) {
        System.out.println(ip2Long("192.168.0.1"));
        System.out.println(long2Ip(3232235521L));
        System.out.println(ip2Long("10.0.0.1"));
    }
    
}

The output is:
3232235521
192.168.0.1
167772161

This concludes this article on how to effectively store IP addresses in MySQL and how to convert between string IPs and numerical values. For more information on how to effectively store IP addresses in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. We hope that everyone will support 123WORDPRESS.COM in the future!