Storing an IP address in a database table (mirror)
Posted: Thu Jun 14, 2018 5:25 pm
This content originally lived at http://arjen-lentz.livejournal.com/44290.html and used to be freely accessible but it is now closed/private/whatever and redirects to:
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:
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:
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.
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);