How MySQL stores IP addresses

During the two-day interview, the loan-treasure interviewer asked how your previous project stored the IP address, and my answer was that it was stored as a varchar string type (in fact, I did take the varchar type to store the project I was involved in, but now I think that the scene was just the address that logged the user's actions, There is not much to use).

Then, the interviewer asked me, how do you find the Class A IP address of the content, this time I am ignorant, because I do not know the IP address is also classified, only know the basic format is from 0.0.0.0 ~ 255.255.255

Therefore, the interview is also a kind of knowledge of learning, but unfortunately the process is too painful, young time to learn more points is not the bad, regret the original in the company too comfortable. Here is an article in the park to find a good explanation of how to store the IP address of the problem, about the IP address of learning to see additional articles:

Why ask how to store IP

First of all to clarify some people have to ask: why should be asked how to save the IP, direct varchar type is not it?

In fact, any program design should be based on functional implementation of the maximum performance optimization. The database design is an important part of the programming, so the smart storage IP address can be greatly improved to some extent.

Using the function algorithm to process

The IP Type field is not directly available in MySQL, but if there are two functions that can transfer IP with a maximum length of 10-bit numeric type, the use of the int type storage IP is much better than the varchar type storage IP address performance, which reduces a lot of space. Because varchar is a variable-length shape, extra bytes are required to store the length. In addition, the int type is faster than the varchar speed in the logical operation.

IP to numeric function Inet_aton ()

We convert the next few common IP addresses

mysql> select Inet_aton (' 255.255.255.255 '); +--------------------------+| Inet_aton (' 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, the field of the IP table can be set to int (10), if the IP can not be obtained directly to save 0 means to obtain the meaning of the IP

Digital to IP function Inet_ntoa ()

MySQL> select Inet_ntoa (4294967295);+-----------------------+| Inet_ntoa (4294967295) |+-----------------------+| 255.255.255.255 |+-----------------------+1 row in Set (0.00sec)MySQL> select Inet_ntoa (3232235777);+-----------------------+| Inet_ntoa (3232235777) |+-----------------------+| 192.168.1.1 |+-----------------------+1 row in Set (0.00sec)MySQL> select Inet_ntoa (168430090);+----------------------+| Inet_ntoa (168430090) |+----------------------+| 10.10.10.10 |+----------------------+1 row in Set (0.00sec)MySQL> select Inet_ntoa (0);+--------------+| Inet_ntoa (0) |+--------------+| 0.0.0.0 |+--------------+1 row in Set (0.00 sec)

Note that 0 is converted to 0.0.0.0

The comparison of integer fields is much more efficient than string, which also conforms to an optimization principle: The field type definition uses the most appropriate (minimum) and simplest data type.
The Inet_aton () algorithm, in fact, borrows the IP number used in the international distinction of IP addresses of countries.
The IP number of the A.B.C.D is:
A * 256 of 3 parties + b * 256 2 times Square + c * 256 1 Times Square + D * 256 0.

Transferred from: http://www.cnblogs.com/cnsanshao/p/3326648.html

How MySQL stores IP addresses