MySQL でのサブネットによるクエリ

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

INSERT INTO access_log (ip_address) VALUES
    (INET_ATON('192.168.1.10')),
    (INET_ATON('192.168.1.50')),
    (INET_ATON('192.168.2.1')),
    (INET_ATON('10.0.0.5'));

-- Find all IPs in 192.168.1.0/24
SELECT INET_NTOA(ip_address) FROM access_log
WHERE ip_address BETWEEN INET_ATON('192.168.1.0')
                     AND INET_ATON('192.168.1.255');

-- Or using bitwise AND with the netmask
SELECT INET_NTOA(ip_address) FROM access_log
WHERE (ip_address & 0xFFFFFF00) = INET_ATON('192.168.1.0');

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.10')),
    (inet_from_string('192.168.1.50')),
    (inet_from_string('192.168.2.1')),
    (inet_from_string('10.0.0.5')),
    (inet_from_string('192.168.1.200'));

-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, 24)))
    = '192.168.1.0/24';

SET @subnet     = '192.168.1.0/24';
SET @prefix_len = inet_masklen(inet_from_string(@subnet));
SET @network    = cidr_to_string(cidr_from_string(@subnet));

SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, @prefix_len)))
    = @network;

-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_contained_by(ip_address, inet_from_string('192.168.1.0/24'));

ERROR 3219 (HY000): Cannot initialize function 'inet_contains': argument 1 type mismatch (expected vsql_network_address.INET, got vsql_network_address.CIDR)

-- IPs in 192.168.1.0/24: from .0 to .255
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_compare(ip_address, inet_from_string('192.168.1.0'))   >= 0
  AND inet_compare(ip_address, inet_from_string('192.168.1.255')) <= 0;

CREATE TABLE ipv6_log (id INT, ip INET);
INSERT INTO ipv6_log VALUES
    (1, inet_from_string('2001:db8::1')),
    (2, inet_from_string('2001:db8::100')),
    (3, inet_from_string('2001:db9::1'));

-- Find all IPs in 2001:db8::/32
SET @v6_prefix = 32;
SET @v6_network = '2001:0db8:0000:0000:0000:0000:0000:0000/32';

SELECT inet_to_string(ip) FROM ipv6_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip, @v6_prefix)))
    = @v6_network;

CREATE TABLE networks (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    network CIDR,
    label   VARCHAR(100)
);

INSERT INTO networks (network, label) VALUES
    (cidr_from_string('192.168.1.0/24'), 'office-lan'),
    (cidr_from_string('10.0.0.0/8'),     'vpn-range'),
    (cidr_from_string('172.16.0.0/12'),  'docker-range');

-- Is an incoming IP in any authorized network?
-- inet_masklen() takes INET, not CIDR — convert via string to get the prefix length
SELECT label FROM networks
WHERE cidr_to_string(inet_network(inet_set_masklen(
    inet_from_string('192.168.1.42'),
    inet_masklen(inet_from_string(cidr_to_string(network)))
))) = cidr_to_string(network);