I had this same issue and found the following blog post that suggests how to get what you are looking for. I took inspiration from that post but modified it some. Here is my solution:
DELIMITER //
-- Returns the network address using an IPv4 address and the network length.
CREATE FUNCTION ipv4_subnet(ip BINARY(4), net_len int) RETURNS BINARY(4)
DETERMINISTIC
BEGIN
DECLARE zeroIp BINARY(4) DEFAULT b'0';
RETURN ip & (~zeroIp << (32 - net_len));
END
//
-- Returns the host mask of an IPv4 address given the network length.
CREATE FUNCTION ipv4_host_mask(net_len int) RETURNS BINARY(4)
DETERMINISTIC
BEGIN
DECLARE zeroIp BINARY(4) DEFAULT b'0';
RETURN ~zeroIp >> net_len;
END
//
-- Returns the network address using an IPv6 address and the network length.
CREATE FUNCTION ipv6_subnet(ip BINARY(16), net_len int) RETURNS
BINARY(16)
DETERMINISTIC
BEGIN
DECLARE zeroIp BINARY(16) DEFAULT b'0';
RETURN ip & ((~zeroIp << (128 - net_len)));
END
//
-- Returns the host mask of an IPv6 address given the network length.
CREATE FUNCTION ipv6_host_mask(net_len int) RETURNS binary(16)
DETERMINISTIC
BEGIN
DECLARE zeroIp BINARY(16) DEFAULT b'0';
RETURN (~zeroIp >> net_len);
END
//
Note that I plan to store both the IPv4 and IPv6 from and to addresses in the same table declared as a VARBINARY(16). Also, I switched to declaring a BINARY(4|16) for the functions I declared rather than calling INET6_ATON because I found that INET6_ATON('0.0.0.0'); returns 0x00 and declaring the return as BINARY guarantees it will be left padded with the zero's you want for the methods and doesn't depend on the behavior of the INET6_ATON function. I suspect that this implementation may be better in performance as well but I haven't done any testing to prove that, but it is simply more straightforward, in my opinion, to declare a BINARY of a certain size and set it to 0 than to depend on the behavior of another function that returns a VARBINARY to do that for you.
You then determine the range for an IPv4 addresses and IPv6 addresses similar to what is suggested in the blog post. If you wanted to define functions for them:
-- Determine from range for IPv4 address in CIDR format.
CREATE FUNCTION ipv4_from_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
DETERMINISTIC
BEGIN
RETURN ipv4_subnet(ip, net_len);
END
//
-- Determine to range for IPv4 address in CIDR format.
CREATE FUNCTION ipv4_to_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
DETERMINISTIC
BEGIN
RETURN ipv4_subnet(ip, net_len) | ipv4_host_mask(net_len);
END
//
-- Determine from range for IPv6 address in CIDR format.
CREATE FUNCTION ipv6_from_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
DETERMINISTIC
BEGIN
RETURN ipv6_subnet(ip, net_len);
END
//
-- Determine to range for IPv6 address in CIDR format.
CREATE FUNCTION ipv6_to_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
DETERMINISTIC
BEGIN
RETURN ipv6_subnet(ip, net_len) | ipv6_host_mask(net_len);
END
//
I know your question was only about IPv6, but since one response suggested you could do both IPv4 and IPv6 with INET6_ATON, I thought I'd answer his thought as well.
EDIT: 7/25/2022
I just realized that the functions to determine the "to" addresses don't need to call the subnet functions. In other words:
-- Determine to range for IPv6 address in CIDR format.
CREATE FUNCTION ipv6_to_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
DETERMINISTIC
BEGIN
RETURN ip | ipv6_host_mask(net_len);
END
//
-- Determine to range for IPv4 address in CIDR format.
CREATE FUNCTION ipv4_to_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
DETERMINISTIC
BEGIN
RETURN ip | ipv4_host_mask(net_len);
END
//
should suffice (and be slightly more efficient since you are doing fewer operations).
