I'm trying to find the ideal setup for logging ip's to a mysql database. I want to accomodate both ipv4 and ipv6 and I have a few questions about how to best store them.
Should I keep two columns, one for each type? The number schema's are completely different and have two different potential ideal data types if you convert the ip to an int/binary. This can save a lot of space, especially if you start accumulating millions of records. One issue is that it adds coding as you need to convert the ip to human readable form everytime you want to use it.
preferred ipv4 = INT UNSIGNED
preferred ipv6 = BINARY(16)
Another idea is that I could just keep them as varchar; The thought here is that even though it takes up more space, it cuts down on programming and processing. Any time an ip is needed it would need to be converted first, which could add a bit of coding. It seems like maybe it would be easier on cpu resources if any time an ip was used it didn't need to be converted.
ipv4 = varchar(15)
ipv6 = varchar(39)
I could also just keep a generic ip column, using the larger varchar(39) since it'd hold both ip types.
ipv6 = varchar(39)
Thoughts?
Should I keep two columns, one for each type? The number schema's are completely different and have two different potential ideal data types if you convert the ip to an int/binary. This can save a lot of space, especially if you start accumulating millions of records. One issue is that it adds coding as you need to convert the ip to human readable form everytime you want to use it.
preferred ipv4 = INT UNSIGNED
preferred ipv6 = BINARY(16)
Another idea is that I could just keep them as varchar; The thought here is that even though it takes up more space, it cuts down on programming and processing. Any time an ip is needed it would need to be converted first, which could add a bit of coding. It seems like maybe it would be easier on cpu resources if any time an ip was used it didn't need to be converted.
ipv4 = varchar(15)
ipv6 = varchar(39)
I could also just keep a generic ip column, using the larger varchar(39) since it'd hold both ip types.
ipv6 = varchar(39)
Thoughts?
Facebook
Twitter