Storing an IP address in a database table

Arjen's Journal - Storing an IP address in a database table

Storing an IP address in a database table
Say you have an IP address, 192.168.0.10 and want to store that in a database table. You could of course store it in a CHAR(15) and that is in fact what many people do. But you probably want to search on this field and therefore want it indexed also. So can we do better than using a 15 byte character field? We sure can.MySQL has two built-in functions: INET_ATON() and INET_NTOA(). They are actually based on the equivalent inet_aton() and inet_ntoa() which are C library functions present on pretty much every TCP/IP capable system. Why? These two functions are used allover the place in any TCP/IP stack implementation or even application.The INET_ATON() function converts Internet addresses from the numbers-and-dots notation into a 32-bit unsigned integer, and INET_NTOA() does the opposite. Isn't that handy!Let's put it to the test:
mysql> SELECT INET_ATON('192.168.0.10') AS ipn;
+------------+
ipn
+------------+
3232235530
+------------+

mysql> SELECT INET_NTOA(3232235530) AS ipa;
+--------------+
ipa
+--------------+
192.168.0.10
+--------------+

So you can store an IP address in an INT UNSIGNED (4 bytes) which is of course much more efficient and faster than a CHAR(15). Naturally, you can call the function while you're inserting, so something like this is fine also:INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...)
In MySQL 5.0, you can even do this transformation inside a LOAD DATA INFILE command, without using temporary columns:

LOAD DATA INFILE 'filename'
INTO TABLE tbl
...
(col1, ..., @ipa1, ..., coln)
SET ipn = INET_ATON(@ipa);

So in the list of columns you assign this column to a server-side variable, and then assign the transformed value to the proper column in the SET clause. Quite elegant, really.