How MySQL stores IP addresses

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.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.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

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.

Source:
Http://www.qttc.net/201208193.html
http://blog.sina.com.cn/s/blog_499740cb0100giny.html

/** *  @author  sunchong */public class iputil {    /**      *  convert string IP to int type ip     *  @param  strip      *  @return      */    public  static int ip2int (String strip) {        string[]  ss = strip.split ("\ \");         if (ss.length != 4) {             return 0;        }         byte[] bytes = new byte[ss.length];         for (int i = 0; i < bytes.length;  i++) {            bytes[i] = (byte)  integer.parseint (Ss[i]);        }         return byte2int (bytes);    }    /**      *  convert int Type IP to string ip     *  @param  intIp      *  @return      */    public  Static string int2ip (Int intip) {        byte[]  Bytes = int2byte (intip);         stringbuilder sb =  new stringbuilder ();         for (int i = 0;  i < 4; i++) {             Sb.append (BYTES[I]&NBSP;&AMP;&NBSP;0XFF);             if (i <  3) {                sb.append ( ".");             }         }        return sb.tostring ();     }    private static byte[] int2byte (int i)  {         byte[] bytes = new byte[4];         bytes[0] =  (Byte)   (0xff & i);         bytes[1] =  (Byte)   ((0xff00 & i)  >> 8);         bytes[2] =  (Byte)   ((0xff0000 & i)  >> 16);        bytes[3] =  (byte)   (( 0xff000000 & i)  >> 24);        return bytes;     }    private static int byte2int (byte[] bytes)  {         int n = bytes[0] & 0xFF;         n |=  ((bytes[1] << 8)  & 0xff00);         n |=  ((bytes[2] << 16)  &  0xff0000);        n |=  ((bytes[3] <<  )  & 0xff000000);        return n;     }    public static void main (String[] args)  {         String ip1 =  "192.168.0.1";         int  Intip = ip2int (IP1);        string ip2 =  Int2ip (Intip);         system.out.println (Ip2.equals (ip1));     }}

How MySQL stores IP addresses