We have a requirement to log IP address information of all users who use a certain web application based on JEE 5. What would be an appropriate sql data type for storing IPv4 or IPv6 addressses in the following supported databases (h2, mysql, oracle). There is also a need to filter activity from certain IP addresses. Should I just treat the representation as a string field (say varchar(32) to hold ipv4, ipv6 addresses)
+3
A:
I'd store the IP addresses in a varchar(15). This is easily readable, and you can filter for specific IP's like where ip = '1.2.3.4'.
If you have to filter on networks, like 1.2.3.4/24, it becomes a different story. In that case your better off storing the IP address as a 4 byte binary.
Andomar
2010-04-04 17:16:29
+2
A:
If you have huge amounts of data and have to search through, for performance it would be better to convert string (dotted) representation of IPs to their proper integer values.
esycat
2010-04-04 17:20:54
A:
Either of these is valid
- 4 bytes, perhaps a 5th byte for CIDR
- varchar(15) or (18) to store full representation in one go
Saying that, varchar(48) for SQL Server's sys.dm_exec_connections...
gbn
2010-04-04 18:00:57