Mask IPv6 Addresses with MySQL

MySQL stores an IPv6 address compactly as a 16-byte value returned by INET6_ATON(). Applying a binary mask byte by byte preserves that representation and works consistently across MySQL versions whose native bitwise operators handle binary strings differently.

The stored function below combines each address byte with the corresponding mask byte using bitwise AND:

DROP FUNCTION IF EXISTS inet6_mask;

DELIMITER $$

CREATE FUNCTION inet6_mask(ip BINARY(16), mask BINARY(16))
RETURNS BINARY(16)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE masked BINARY(16)
        DEFAULT X'00000000000000000000000000000000';
    DECLARE byte_index TINYINT UNSIGNED DEFAULT 1;

    WHILE byte_index <= 16 DO
        SET masked = CONCAT(
            SUBSTRING(masked, 1, byte_index - 1),
            CHAR(
                ORD(SUBSTRING(ip, byte_index, 1))
                & ORD(SUBSTRING(mask, byte_index, 1))
            ),
            SUBSTRING(masked, byte_index + 1)
        );
        SET byte_index = byte_index + 1;
    END WHILE;

    RETURN masked;
END$$

DELIMITER ;

For example, the following mask keeps the first 64 bits and the lowest four bits of the address:

SET @address = INET6_ATON('fdfe::5a55:caff:fefa:9089');
SET @mask = X'FFFFFFFFFFFFFFFF000000000000000F';

SELECT
    HEX(inet6_mask(@address, @mask)) AS masked_hex,
    INET6_NTOA(inet6_mask(@address, @mask)) AS masked_address;

The result is FDFE0000000000000000000000000009, or fdfe::9 in compressed IPv6 notation. A conventional network-prefix mask uses contiguous one-bits followed by zero-bits; the deliberately non-contiguous mask above demonstrates that the function performs a general bytewise AND.

Both arguments are declared as BINARY(16). Pass packed addresses from INET6_ATON(), not textual IPv6 strings, and use INET6_NTOA() when a readable result is needed.