MySQL: How to store IP address

It is still common to store IP addresses as a varchar(15) field though it is possible to use integer type instead. Unlike the varchar type, integer has fixed size and uses only 4 bytes.

INET_ATON() is used to convert an IP address to a number and INET_NTOA() – for the reverse operation.

SELECT INET_ATON('127.0.0.1');
SELECT INET_NTOA(2130706433);

It is important to use INT UNSIGNED with INET_ATON() so that IP addresses for which the first octet is greater than 127 is stored correctly.

Also PHP has similar functions – ip2long() and long2ip(). However ip2long() function may return negative results in certain cases. To make it always positive unsigned intereger ip2long() call has to be paired with printf() or sprintf() function:

printf('%u', ip2long('128.0.0.1');

Thus plan the IP address column datatype accordingly.

No Comment

No comments yet

Leave a reply