How to store IP address effectively in MySQL

preface

To solve this problem, first of all, it is no problem to save the IP address as a string in the database. So you might have a question? Since it's OK, what are you doing here?

Although this is a contradictory topic, there are other ways to store strings. For example, it is quite common to store IP address as int type data. Although this storage method is not complicated to implement, it can be thought of. It also shows that you are good at thinking and have a good grasp of the underlying data.

Because an int type data takes up 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, and each segment range is 0 ~ 255, which is just enough to be saved, so if it is slightly converted, the IP address will be cleverly stored in the database with the smallest space (if there is no special description, it refers to the ipv4 address). You may think that this small change has nothing to do with it, but as the amount of data increases, the difference between 15 bytes and 4 bytes will surprise you. So when designing the database, the field type should be appropriate, enough is enough, and saving is enough.

As in section 4.1.7 of high performance MySQL version 3, the author suggests that when storing IPv4 addresses, the 32-bit 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, whether it's data storage or index storage
• Easy to use and more efficient

Generally, when saving an IPv4 address, an IPv4 needs at least 7 characters and at most 15 characters, so you can use VARCHAR(15). When MySQL saves a variable length string, it needs an extra byte to save the length of the string. If you use unsigned integers for storage, you only need 4 bytes. In addition, four fields can be used to store each part of IPv4 separately, but generally, the storage space and query efficiency should not be very high (although some scenarios are suitable for this way of storage). However, the use of unsigned integers for storage is not easy to read and requires manual conversion.

Tool class implementation transformation

In order to store the IP address as int type in the database, one is to get the corresponding value through the shift operation and & calculation in java code

package com.java.mmzsit; /** * @author : mmzsblog * @description: Ipv4 Address translation * @date : 2020/5/27 22:43 */ public class Ipv4Covert { public static void main(String[] args) { String ip = "10.108.149.219"; // step1: decompose IP string and write to byte array byte[] ip1 = ipToBytes(ip); // Step 2: shift each byte in byte array to the left, corresponding to 4 bytes of integer variable int ip2 = bytesToInt(ip1); System.out.println("integer ip ----> " + ip2); // step3: right displacement of integer variable, recover IP string String ip3 = intToIp(ip2); System.out.println("character 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 btye array */ public static byte[] ipToBytes(String ipAddr) { // Initializes a byte array with a defined length of 4 byte[] ret = new byte[4]; try { String[] ipArr = ipAddr.split("\\."); // Write string array to byte array in turn 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); } } /** * Set byte [] - > int according to bit operation * Principle: force each byte into 8-bit binary code, then move 8 bits left in turn, corresponding to 4 bytes of Int variable */ public static int bytesToInt(byte[] bytes) { // Specify the number of bits at the same time of shift first and then direct strong rotation int addr = bytes[3] & 0xFF; addr |= ((bytes[2] << 8) & 0xFF00); addr |= ((bytes[1] << 16) & 0xFF0000); addr |= ((bytes[0] << 24) & 0xFF000000); return addr; } /** * Put int - > string address * * @param ipInt * @return String */ public static String intToIp(int ipInt) { // First, convert to binary, then shift return new StringBuilder() // Move 3 bytes (24 bits) to the right to get the first segment of IP address, which is byte[0]. In order to prevent the sign bit from being 1, which is negative, the last time & 0xff .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 binary thinking and a number system widely used in computing technology. Although it is seldom used in normal times, it will help us to strengthen our understanding of machine language and improve our coding level after mastering it skillfully, especially in the face of resource shortage (storage) scenarios, it will help us to analyze and optimize problems.

Conversion of database functions

Another way is to use the function INET provided by the database_ 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 IPv6 address, use function INET6_ATON and INET6_NTOA for conversion:

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, which allocates 128bits space (because ipv6 uses 128bits, 16 bytes); or as char type, which allocates 32bits space.

If you use the function of database, you only need to do a little conversion when the IP address is put into storage, which is convenient and fast. And here, you don't think it's a good choice to convert the IP address into digital storage. After all, the database helps us consider this, and indirectly proves that it's a feasible and good storage way.

reference resources

• 1. How to save IP address to Int type variable https://www.jianshu.com/p/1090044c3522
• 2. How to store IP address in database https://www.cnblogs.com/skynet/archive/2011/01/09/1931044.html
• 3. MySQL performance advantage of storing integer IP address https://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/

Add new comment

For adding a comment, please log in

Login or create account

0 comments