How IP addresses are stored in the database

Most of the company's table structure needs to be audited by the DBA, sometimes you will see the storage IP address is varchar (15), this is the traditional way, this method needs to occupy 15 bytes, then there is a more space-saving approach? There must be, it is stored in int. If you have an int store, there are 2 more ways to handle it.

1. Use the MySQL function for processing. You can use the Inet_aton,inet_ntoa function to convert.

2. Use the function of the development language for processing, using PHP for example. You can use the IP2LONG,LONG2IP function to convert.

The results obtained from the above 2 methods are consistent. Because the algorithms are all the same.

The following is a demonstration of function usage. First look at how to play with MySQL's own function. First, convert the IP address to int.

Mysql> SELECTInet_aton ('192.168.0.1');+--------------------------+|Inet_aton ('192.168.0.1')|+--------------------------+|               3232235521 |+--------------------------+1Rowinch Set(0.00sec) MySQL> 

Then convert the int type to an IP address:

Mysql> SELECTInet_ntoa (3232235521); +-----------------------+|Inet_ntoa (3232235521)|+-----------------------+| 192.168.0.1           |+-----------------------+1Rowinch Set(0.00sec) MySQL> 

Let's look at the use of PHP functions:

<? PHP Echo Ip2long (' 192.168.0.1 ');? >

3232235521

You can see the result is the same, if you want to convert to an IP address, and then use PHP Long2ip () on the line, here is no longer written.
MySQL Store this value is a field that needs to be UNSIGNED with Int. Without unsigned, more than 128 of the IP segment will not be stored. Of course you can use bigint, but keep in mind that you can be stingy. Save a little is a little, haha.

When PHP is deposited: $ip = Ip2long ($IP);
MYSQLL when removed: SELECT Inet_aton (IP) from table ...
When PHP is removed, one more step: $ip = Long2ip ($IP);

So that was the varchar type, so how do you convert it? Here's the slow way.

1. The SQL statement that converts the previous varchar () data to the int type is as follows.

UPDATE SET =  WHERE  is  not NULL ;

Mysql> Select *  fromT1;+------+-------------+|Id|Ip|+------+-------------+|    1 | 192.168.0.1 ||    2 | 192.168.0.2 |+------+-------------+2Rowsinch Set(0.00sec) MySQL> UPDATET1SETIp=Inet_aton (IP)WHEREInet_aton (IP) is  not NULL ; Query OK,2Rows Affected (0.00sec) Rows matched:2Changed:2Warnings:0MySQL> Select *  fromT1;+------+------------+|Id|Ip|+------+------------+|    1 | 3232235521 ||    2 | 3232235522 |+------+------------+2Rowsinch Set(0.00Sec

2. Change the field to int type.

Mysql>ShowCreate Tablet1\g*************************** 1. Row***************************       Table: T1Create Table:CREATE TABLE' T1 ' (' ID ')int( One)DEFAULT NULL, ' IP 'varchar( the)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=UTF81Rowinch Set(0.00Sec

ALTER TABLE INT  not NULL;

3. program code changes.

Summarize:

field type with the right, enough on the line, can save the province, after the amount of data up, 10 bytes and 5 bytes of data will surprise you.

How IP addresses are stored in the database