http://www.livejournal.com/login.bml?returnto=http%3A%2F%2Farjen-lentz.livejournal.com%2F44290.html&errmsg=notloggedin
Login required? No thanks. Time to go back and find an alternate host. (No login required here at 3ice.hu!)
Now it shall live at http://web.archive.org/web/200812242240 ... 44290.html And here:
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:
Code: Select all
mysql> SELECT INET_ATON('192.168.0.10') AS ipn;
+------------+
| ipn |
+------------+
| 3232235530 |
+------------+
mysql> SELECT INET_NTOA(3232235530) AS ipa;
+--------------+
| ipa |
+--------------+
| 192.168.0.10 |
+--------------+
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:
Code: Select all
LOAD DATA INFILE 'filename'
INTO TABLE tbl
...
(col1, ..., @ipa1, ..., coln)
SET ipn = INET_ATON(@ipa);