[philiptellis] /bb|[^b]{2}/
Never stop Grokking


Monday, November 23, 2009

Storing IP addresses in a MySQL data table

For a lot of log processing, I need to store IP addresses in a database table. The standard process was always to convert it to an unsigned int in perl or php and then insert it. Today I discovered an easier way. MySQL's INET_ATON function. It takes an address in dotted quad format and converts it into an INT. So, all you have to do is this:
INSERT INTO table (ip) VALUES (INET_ATON('$ip_address'));
And done.

10 comments :

Anonymous
November 23, 2009 5:59 AM

INET_ATON() is not IPv6 compatible, so it has little value.

Philip
November 23, 2009 6:01 AM

well, mysql itself is not IPv6 compatible, so if you need IPv6, your best bet is to use a string, don't convert to superduperbigint or whatever datatype they invent to store it.

Anonymous
November 23, 2009 9:29 AM

Just be careful not to use this function (INET_ATON) in a trigger. It seems a logical thing to want to do - create a before-insert trigger that takes a dotted-quad IP and converts it automatically to unsigned INT so that you don't have to mess with it - but, alas, it doesn't work. Returns null, if I recall correctly.

Matthew Montgomery
November 23, 2009 12:32 PM

inet_aton and inet_ntoa are perfectly fine to use in triggers

http://pastebin.com/m6f1d8490

Shlomi Noach
November 23, 2009 1:08 PM

Saying ipv4 has little value does not match reality.
Many many many applications still assume ipv4; so this is still very relevant.

gtowey
November 23, 2009 5:34 PM

@Blues

I would think BINARY(16) is a much better choice than VARCHAR(39) for IPv6 addresses.

Philip
November 23, 2009 6:03 PM

@gtowney: you're right, though you'd have to deal with the complexity of converting it to/from a number in your application in that case. Two bigints - one for the network and one for the host could also work (correct me if I'm wrong).

Unknown
November 25, 2009 12:50 PM

just perfect. this will come in handy someday and i am thinking very soon... thanx

farrelley
November 25, 2009 12:51 PM

I am also pretty sure that
MYSQL -->INET_ATON() is the same as PHP-->ip2long()

and
MYSQL-->INET_NTOA() is the same as PHP-->long2ip()

Satya Prakash
November 26, 2009 2:52 AM

Here is more on IP address
Get Visitors IP address

Post a Comment

...===...