Shulou(Shulou.com)06/02 Report--

Editor to share with you how MySQL can effectively store IP addresses and strings between IP and numeric conversion. I hope you will get something after reading this article. Let's discuss it together.

It is recommended that when storing IPv4 addresses, you should use 32-bit unsigned integers (UNSIGNED INT) 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 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 (there may be scenarios that are suitable for storage in this way).

Use strings and unsigned integers to store specific performance analysis and benchmark of IP. You can read this article.

There are also disadvantages to using unsigned integers for storage:

Not easy to read

Manual conversion is required

For conversion, MySQL provides functions to convert IP in string format to integer INET_ATON and IP in integer format to INET_NTOA in string format. As follows:

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, using VARBINARY can also get the same benefits, while MySQL also provides the corresponding conversion functions, namely INET6_ATON and INET6_NTOA.

For converting string IPv4 and numeric types, you can put them on the application layer. The following is to convert the two using java code:

Package com.mikan; / * * @ author Mikan * @ date 2015-09-22 10:59 * / public class IpLongUtils {/ * convert the string IP to the long value corresponding to the long * * @ param ipStr string IP * @ return IP * / public static long ip2Long (String ipStr) {String [] ip = ipStr.split ("\\.") Return (Long.valueOf (ip [0]) 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 as follows:

3232235521

192.168.0.1

167772161

After reading this article, I believe you have a certain understanding of "how to effectively store IP addresses and strings IP and numerical conversion between IP and numerical values". If you want to know more about it, please follow the industry information channel. Thank you for reading!

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.