Storing an IP address in a database table (mirror)

Community for Programmers and Developers. C, C++, C#, Basic, Perl, VB, VBS, Java, Javascript, PHP, CGI, HTML and More!
• If you are posting source code, proper indenting and comments will improve the readability.

Storing an IP address in a database table (mirror)

New postby 3ICE on 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/20081224224056/arjen-lentz.livejournal.com/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 |
+--------------+

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:
Code: Select all
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.
ImageImageImageImageImage
Image
ImageImage
User avatar
3ICE
Admin
 
Posts: 2271
Joined: Sat Mar 01, 2008 11:34 pm
Location: Hungary
Realm: US East
Account: 3ICE
Clan: 3ICE

Return to Programming



Who is online

Users browsing this forum: No registered users and 1 guest

cron