CODEWITHSUNDEEP
mysqlmariadbipv6ipv4
Javadroider
Javadroider

Reputation: 2450

How to ipv6 in integer in mysql databases?

I have one table where I store ip address. For faster access I store ip int also. This is the table structure.

CREATE TABLE `ipv6_test` (
  `ip_string` varchar(20) DEFAULT NULL,
  `ip_int` double DEFAULT null
) ;

I have used following method to get integer version of ip address.

new BigInteger(1, com.google.common.net.InetAddresses.forString(ip).getAddress());

Everything was working fine for ipv4 addresses, but my code started failing while storing ipv6 addresses.

How to store BigInteger in mysql database? I'm thinking to use VARBINARY. Can I create index on VARBINARY column?

How is the VARBINARY's performance compared to index on integer?

Upvotes: 0

Views: 927

Answers (1)

danblack
danblack

Reputation: 14646

INET6_ATON converts to a varbinary(16) for IPv4 + IPv6 storage.

Yes you can use VARBINARY as indexes. Its effectively a VARCHAR without character encoding. Its probably a bit slower than integers but ints can't do the same job. You probably won't notice it so don't prematurely optimize. Its only 16 characters maximum so its not much.

If you want to keep an text version for easy retrieval create a generated column converting the varbinary back.

Upvotes: 3

Related Questions