|
DB & Storage space needed to represent address?
![[ca]](/s/countries/ca.gif) Shadow Hawkins on Tuesday, 23 September 2008 21:50:45
I am wanting to add support for logging of IPv6 addresses to my database and I am not sure how much space to allocate for this. My calculation gives a rough estimate of 48 characters, based on the following assumption:
* 128 bits = 16 bytes
* 1 byte is represented by two chars in hex, therefore 32 hex characters max
* assuming a string showing all 128 bits we should have 15 colons, since they are only present between groups
Also I debating whether it would be work prefixing the address with the protocol type, for example:
ipv4/0.0.0.0.
ipv6/2001:0:e4f:bea1:4427
Any corrections and opinions are appreciated.
DB & Storage space needed to represent address?
![[nl]](/s/countries/nl.gif) Shadow Hawkins on Tuesday, 23 September 2008 22:55:33
Maybe your database supports a data type to store IP addresses?. Postgresql has such data types:
http://www.postgresql.org/docs/8.3/interactive/datatype-net-types.html
If you want to store the IP addresses as text, you'll need 39 characters if you don't have to store subnet information:
IPv6: xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx
IPv4: 0000:0000:0000:0000:0000:ffff:xxxx:xxxx (IPv4 mapped address)
By storing all IP addresses in the above format (without zero compression or leading zero compression), sorting and searching is possible.
DB & Storage space needed to represent address?
Shadow Hawkins on Wednesday, 24 September 2008 09:59:34
Hello,
I would definitely try to avoid using an alphanumeric representation in you database. In my opinion IP addresses should *only* be stored as integers, and your database should add support for this if it cannot handle it.
Using string fields for IP addresses will e.g. break any subnet calculation you may want to do in your queries. Also, think about how hard it would be to compare two stringy IPv6 addresses in a query, when one is in abbreviated form and the other is not. There must be many more examples to think of why strings are a bad idea.
I'm glad to see Postgres has picked this up already, unfortunately MySQL is lagging terribly with something similar.
Bug filed in 2004:
http://bugs.mysql.com/bug.php?id=3318
Some activity in 2007:
http://forge.mysql.com/worklog/task.php?id=798
But all marked with low priority. Anyone here with connections amongst our Sun/MySQL friends by any chance?
Pieter
DB & Storage space needed to represent address?
Shadow Hawkins on Wednesday, 24 September 2008 16:46:39
I am using MySQL. According to this page the largest integer is 8 bytes:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
This is much smaller that the 16 bytes (128 bits) needed to store an IPv6 address. Other than splitting the value into two BIGINT fields (ugly), what other suggestions are there?
DB & Storage space needed to represent address?
Shadow Hawkins on Thursday, 25 September 2008 18:29:58
Looks like the BINARY data type would have to do instead:
http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html
DB & Storage space needed to represent address?
Shadow Hawkins on Saturday, 04 October 2008 10:21:59
Although I cannot find any references to it in the MySQL 6.0 manual yet, IPv6 support is claimed to be added in that version, according to:
http://bugs.mysql.com/bug.php?id=3318
If we can find out how it works, maybe we are able to deploy a forward-compatible solution in the mean time.
|
Posting is only allowed when you are