Shulou(Shulou.com)05/31 Report--

This article introduces how to effectively store IP addresses in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Compared to string storage, using unsigned integers to store has the following benefits:

Save space, whether it is data storage or index storage

Easy to use range query (BETWEEN...AND) and more efficient

Typically, when saving an IPv4 address, an IPv4 requires a minimum of 7 characters and a maximum of 15 characters, so you can use VARCHAR (15). When MySQL saves a longer 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. You can also use four fields to store parts of the IPv4 separately, but usually this should not be very efficient in terms of storage space and query efficiency (although some scenarios are suitable for storage in this way). However, using unsigned integers for storage also has the disadvantages of not being easy to read and requiring manual conversion.

Tool classes implement transformations

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

Package com.java.mmzsit; / * * @ author: translation of mmzsblog * @ description:Ipv4 addresses * @ date: 22:43 on 2020-5-27 * / 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: left-shift each byte in the byte array, corresponding to 4 bytes of the integer variable int ip2 = bytesToInt (ip1); System.out.println ("integer ip->" + ip2); / / step3: right shift of the integer variable to restore the IP string String ip3 = intToIp (ip2) System.out.println ("string ip---- >" + ip3);} / * convert the 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 an btye array * / public static byte [] ipToBytes (String ipAddr) {/ / initialize the byte array, defining a length of 4 byte [] ret = new byte [4] Try {String [] ipArr = ipAddr.split ("\\."); / / write the string array to the byte array 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) }} / * according to bit operation, byte []-> int * principle: forcibly convert each byte into an 8-bit binary code, and then move 8 bits to the left in turn In the 4 bytes corresponding to the Int variable, * / public static int bytesToInt (byte [] bytes) {/ / first shifts and then forcefully transforms directly while specifying the number of bits int addr = bytes [3] & 0xFF Addr | = (bytes [2] 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 kind of number system widely used in computing technology. although it is not often used, it will help us to strengthen our understanding of machine language and improve our coding level. especially in the face of resource constraints (storage) scenarios, it is helpful for us to analyze and optimize problems.

Database function conversion

Another way is to convert it through the functions INET_ATON and INET_NTOA that come with the database:

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:

Mysql > SELECT HEX (INET6_ATON ('1030 veg C9B4 INET6_ATON FF12 purl 48AAlav 1A2B') +-+ | HEX (INET6_ATON ('1030 veg C9B4 FF12 vig 48AAlav 1A2B')) | +-+ | 1030000000000000C9B4FF1248AA1A2B | +-+ 1 row in set mysql > SELECT INET6_NTOA (UNHEX ('10300000000000C9B4FF1248AA1A2B')) +-- + | INET6_NTOA (UNHEX ('10300000000000C9B4FF1248AA1A2B')) | +-- -+ | 1030::c9b4:ff12:48aa:1a2b | +-+ 1 row in set

The database is then defined as a varbinary type, allocating 128bits space (because ipv6 uses 128 bits, which is 16 bytes), or char, which allocates 32bits space.

If you use the function of the database, you only need to convert the IP address into the database slightly, which is convenient and fast; and here, don't you think it is a good choice to convert the IP address into digital storage? after all, the database has helped us take this into account and indirectly proved that this is indeed a feasible and good way of storage.

On how to effectively store IP addresses in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.