MySQL での IPv6 の保存

SELECT HEX(INET6_ATON('192.168.1.5'));      -- Returns 4-byte IPv4
SELECT HEX(INET6_ATON('2001:db8::1'));      -- Returns 16-byte IPv6
SELECT LENGTH(INET6_ATON('192.168.1.5'));   -- Returns 4
SELECT LENGTH(INET6_ATON('2001:db8::1'));   -- Returns 16

CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARBINARY(16),     -- 4 bytes for IPv4, 16 bytes for IPv6
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (INET6_ATON('192.168.1.5')),
    (INET6_ATON('2001:db8::1'));

SELECT INET6_NTOA(ip_address), accessed_at FROM access_log;

-- Filter only IPv6
SELECT INET6_NTOA(ip_address) FROM access_log
WHERE LENGTH(ip_address) = 16;

-- Under MySQL 8.4's default strict mode this raises ERROR 1411 and the row is
-- rejected. A bare SELECT INET6_ATON('not-an-ip') returns NULL with a warning.
INSERT INTO access_log (ip_address) VALUES (INET6_ATON('not-an-ip'));

INSTALL EXTENSION vsql_network_address;

CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INET,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (inet_from_string('192.168.1.5')),
    (inet_from_string('2001:db8::1')),
    (inet_from_string('::1')),            -- IPv6 loopback
    (inet_from_string('fe80::1'));        -- link-local

SELECT inet_to_string(ip_address), inet_family(ip_address) AS family
FROM access_log;

-- Compressed input → expanded storage
SELECT inet_to_string(inet_from_string('2001:db8::1'));
-- Returns: 2001:0db8:0000:0000:0000:0000:0000:0001

-- Extract just the address without prefix
SELECT inet_host(inet_from_string('2001:db8::1/64'));
-- Returns: 2001:0db8:0000:0000:0000:0000:0000:0001

-- Get prefix length
SELECT inet_masklen(inet_from_string('2001:db8::1/64'));
-- Returns: 64

-- Get network address
SELECT cidr_to_string(inet_network(inet_from_string('2001:db8::1/64')));
-- Returns: 2001:0db8:0000:0000:0000:0000:0000:0000/64

-- Get netmask
SELECT inet_to_string(inet_netmask(inet_from_string('2001:db8::1/64')));
-- Returns: ffff:ffff:ffff:ffff:0000:0000:0000:0000

-- Only IPv6 connections
SELECT inet_to_string(ip_address), accessed_at
FROM access_log
WHERE inet_family(ip_address) = 6;

-- Only IPv4 connections
SELECT inet_to_string(ip_address), accessed_at
FROM access_log
WHERE inet_family(ip_address) = 4;