MySQL datatype for storing IP addresses

The best way to store a (version 4) IP address in a MySQL database is as an integer. This may sound strange but MySQL has two powerful functions to enable you to store an IP address as an unsigned INT. Searching an integer is much faster than searching a string and additionally integers take up less storage space which is great when working with large datasets.

mysql> SELECT INET_ATON('10.0.5.9');
---------------------
INET_ATON('10.0.5.9')
---------------------
167773449
---------------------
1 row in set (0.00 sec)

and the reverse function

mysql> SELECT INET_NTOA(167773449);
---------------------
INET_NTOA(167773449)
---------------------
10.0.5.9
---------------------
1 row in set (0.00 sec)

Leave a Reply

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