MySQL 8.0: Storing IPv6

In MySQL 8.0.0 we introduced many new features; among those, we extended the bit-wise operations to work with binary data. Because of these changes, storing and manipulating IPv6 addresses can be done in an easier manner. In this blog we will take a look at how can you do this for some of the most common use cases.

Let’s start by creating a table, where we will store the CIDR, split in two columns: one for the IPv6 address and one for the network length. The most compact way of storing IPv6 values is to use the binary(16).

In order to generate some random data, I will use a stored procedure which will insert 100,000 IP addresses in the previously created table. The addresses will be generated by inserting the first 16 bytes of the current time-stamp’s SHA value.

If you want to insert the values from a human-readable string, MySQL provides the INET6_ATON("2001:0db8:85a3:0000:0000:8a2e:0370:7334") function which will strip the colons (“:”) and convert the 32 characters to a binary(16) string.

Next I will select the first 3 IP address. In order to do that I will use the INET6_NTOA function, which will convert from the binary format to the human-readable format.

Next, I would like to select the network mask, host mask, network address and also generate the network range intervals. For this I will create a few helper functions:

Let’s start first with the network address:

Next will be the net mask:

And the host mask:

And finally the ends of the IP range:

In order to select the row which contains a certain IP address this query can be used, but for better performance an INDEX on the ip_address column should first be added:

Next let’s see how can we get the network which this IP belongs to:

This seems to be very inefficient, since we need to calculate, for each row in the table the network address for the IP address stored in the row.

So, how about creating a table that stores the network address and the net mask?

A lot faster but still, we had to do a full table scan as we still have to do the bit-wise operations for each row in order to get the result.

How about storing the network range ends and checking if the IP is between them?

That was definitely faster, and the index was also used, but, can this be even faster? If we know that the IP ranges are non-overlapping (so no parent-child networks exist) then we can use this tip and get the result instantly:

So now we can replace the compound index with an index on range_from and still get instant results as we will search for the first IP’s starting range (range_from) using the index and then we would stop there as we know the network range is unique (also because we’ve set the LIMIT  to 1).

Conclusion

The improvements added to the bit-wise operations make it easy to work with IPv6 addresses. We would like to further improve this experience and that’s why we’re looking for your feed-back. Which functions would you find helpful and in which way would you use them?

Thank you for using MySQL!

 

4 thoughts on “MySQL 8.0: Storing IPv6

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.