Is there a commonly accepted best practice for storing IP addresses in a database? I've seen a 4 column of bytes method, a single column INT method, and a single column CHAR(15) method.

I know it is possible to do any of those and that there are pros and cons to them but which one is best? Also what is everyone going to do when IPv6 starts gaining more momentum?

what do you want to store the information for?
if its a passive audit log, ie you just need to know what IP address was used then storing as an single number may make sense

if you need to manipulate theat data then Id suggest storing as a string

I don't think it actaully matters but your solution will need to handle IPv6 when it truly conmes into force..

I am storing the IP addresses of systems that access a database. It needs the ability to be searched and and also converted to a human readable format. I was leaning towards have 4 fields IPoct1, IPoct2, IPoct3, IPoct4 all of tinyINT unsigned / Byte type. This way I can search by network and it retains human readability. But I wasn't sure if this was a proper way to store data (4 fields that technically hold data that 1 could).

-Brian

the method where the pros and cons more closely fit your particular needs

for searching, the INTEGER method is the fastest, because you always search top-down, i.e. you always start with the first octet, then the second, etc.

you never search for all IPs where the 3rd octet is 155 -- that'd be silly ™

the top-down search translates very easily into integer ranges

regarding "proper way to store data (4 fields that technically hold data that 1 could)" this depends completely on what kind of data it is

4 fields instead of 1 is much better if, for instance, you're talking about street, city, state, country

the method where the pros and cons more closely fit your particular needs

for searching, the INTEGER method is the fastest, because you always search top-down, i.e. you always start with the first octet, then the second, etc.

you never search for all IPs where the 3rd octet is 155 -- that'd be silly ™

the top-down search translates very easily into integer ranges

regarding "proper way to store data (4 fields that technically hold data that 1 could)" this depends completely on what kind of data it is

4 fields instead of 1 is much better if, for instance, you're talking about street, city, state, country

Check if your DB vendor supports a native IP format, if so use it because it's properly optimized for doing queries against it. PG for example has one : http://www.postgresql.org/docs/8.3/i...net-types.html

Otherwise use 4 fields of one byte, I believe hostip.org does the same.

Ries

I could see searching on just the third octet. Around here, we have set up the network addresses to be on the 10.0.0.0 network, which is a 'non-network" according to the powers that be. As such, the third octet came out to be the location for us. My building has 10.0.101.0 and 10.0.102.0 for clients. Easy enough to trace.

Thanks for all the replies. I am using Mysql 5.1 and I decided to go with 4 tinyint fields, although I did manage to get a working conversion to unsigned Int and back to dotted format.

-Brian

a working conversion? any chance you could share it?

also, did you see the INET_ATON() and INET_NTOA() functions?

I'm off work until Monday so I dont have the exact code, but to sum it up:

IPasINT = octet[X](X=0to3) * 256 ^ X(0to3)
This creates an unsigned integer. It is important to note that this in not the same format that Windows uses, although if read in binary it would be... Windows uses a signed int therefor 128.0.0.0 and greater would be negative numbers as signed integers.

Those NTOA and ATON functions are expecting the signed int I believe. I do know they just convert the order between network order and host order.

*edited for correct math*
The conversion to signed int went something like:
To revert it to a human readable format from an unsigned int was a little trickier. The correct way is to do modulus division but I did it like this: