Method for storing IP addresses in MySQL _ MySQL

This article describes how to store IP addresses in MySQL to maximize performance. For more information, see Why is IP storage required?

First, let's clarify some people's questions: why do I have to know how to store IP addresses? isn't the direct varchar type?

In fact, any program design should optimize performance to the maximum extent on the basis of function implementation. Database Design is an important part of programming, so the storage IP address can be greatly improved to a certain extent.

Processing Using function algorithms

The IP type field is not directly provided in MySQL. However, if there are two functions that can convert the IP address and the maximum length to 10 digits, therefore, the performance of int-type storage IP addresses is much higher than that of varchar storage IP addresses, reducing the storage space. Because varchar is a variable length, an extra byte storage length is required. In addition, int type is faster than varchar in logical operation.

IP to Digital function inet_aton ()

We can convert several common IP addresses.

 mysql> select inet_aton('255.255.255.255'); +------------------------------+ | inet_aton('255.255.255.255') | +------------------------------+ |          4294967295 | +------------------------------+ 1 row in set (0.00 sec)   mysql> select inet_aton('192.168.1.1');   +--------------------------+ | inet_aton('192.168.1.1') | +--------------------------+ |        3232235777 | +--------------------------+ 1 row in set (0.00 sec)   mysql> select inet_aton('10.10.10.10'); +--------------------------+ | inet_aton('10.10.10.10') | +--------------------------+ |        168430090 | +--------------------------+ 1 row in set (0.00 sec)

Therefore, you can set the IP table field to INT (10). If the IP address cannot be obtained, you can directly store 0 to indicate that the IP address cannot be obtained.

Number to IP function inet_ntoa ()

 mysql> select inet_ntoa(4294967295); +-----------------------+ | inet_ntoa(4294967295) | +-----------------------+ | 255.255.255.255    | +-----------------------+ 1 row in set (0.00 sec)  mysql> select inet_ntoa(3232235777); +-----------------------+ | inet_ntoa(3232235777) | +-----------------------+ | 192.168.1.1      | +-----------------------+ 1 row in set (0.00 sec)  mysql> select inet_ntoa(168430090); +----------------------+ | inet_ntoa(168430090) | +----------------------+ | 10.10.10.10     | +----------------------+ 1 row in set (0.00 sec)   mysql> select inet_ntoa(0);     +--------------+ | inet_ntoa(0) | +--------------+ | 0.0.0.0   | +--------------+ 1 row in set (0.00 sec)

Note: 0 is converted to 0.0.0.0

Integer fields are much more efficient than strings, which also conforms to an optimization principle: the most appropriate (minimum) and simplest data type for field type definition.
The inet_aton () algorithm actually uses the ip number used in international ip address differentiation.
The ip number of a. B. c. d is:
A * 256 power 3 + B * 256 power 2 + c * 256 power 1 + d * 256 power 0.

The above explains how to store IP addresses in MySQL, hoping to help you learn.