Mysql Storage IP Address method _mysql

Why should I ask how to store IP?

First of all to clarify a part of the people have to ask: why do you want to know how to save the IP, direct varchar type is not it?

In fact, any program design should be based on the implementation of the function to maximize the optimization performance. And the database design is an important part that can not be neglected in the programming, so the clever deposit IP address may obtain the very big promotion.

Using function algorithm to deal with

The IP Type field is not provided directly in MySQL, but if there are two functions that can transfer IP to a maximum length of 10-bit numeric type, the use of an int type storage IP has a much higher performance than the varchar type storage IP address, reducing a lot of space. Because the varchar is a variable-length shape, it requires an extra byte storage length. In addition, the int type is faster than the varchar in logic operation.

IP Spin digital function Inet_aton ()

Let's convert the next few 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)

So the IP table field can be set to int (10) is good, if the IP can not get a direct deposit of 0 of the means to get no IP

Digital Transfer 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 that 0 is converted to 0.0.0.0

An integral field is a lot more efficient than a string, which is also consistent with an optimization principle: The field type definition uses the most appropriate (minimal) and simplest data type.
Inet_aton () algorithm, in fact, borrowed from the international IP address in the distinction between the use of IP number.
The IP number for A.B.C.D is:
A * 256 of 3 times + b * 256 of 2 square + c * 256 of 1 of square + D * 256 of the 0 times.

The above explanation is the MySQL storage IP address method, hoped can be helpful to everybody's study.