Categories
MySQL

Storing IP addresses in MySQL

I have always stored IP addresses as strings in VARCHAR fields in MySQL.

I am currently reading “High Performance MySQL” (O’Reilly) and it mentions the advantages of storing IP addresses as integers in an INT field rather than as a string in a VARCHAR or CHAR field.

The MySQL docs specify:  “To store values generated by INET_ATON(), use an INT UNSIGNED column”.

So, with the following table:

CREATE TABLE IF NOT EXISTS `ipaddresses` ( `ipaddress` int(11) unsigned NOT NULL ); INSERT INTO `ipaddresses` (`ipaddress`) VALUES (2130706433), (167773449);

To insert a record:

INSERT INTO `ipaddresses` (`ipaddress`) VALUES (INET_ATON('127.0.0.1'));

To read a record:

SELECT INET_NTOA(`ipaddress`) FROM `ipaddresses`;

By the way, if you are retrieving IP Addresses using $_SERVER['REMOTE_ADDR'], consider a more advanced approach, such as borrowing Zend RemoteAddress class.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.