MySQL での IP による位置情報の検索

CREATE TABLE geoip_ranges (
    start_ip   INT UNSIGNED NOT NULL,
    end_ip     INT UNSIGNED NOT NULL,
    country    CHAR(2),
    INDEX idx_start (start_ip),
    INDEX idx_end   (end_ip)
);

-- Import from GeoLite2-Country-Blocks-IPv4.csv after converting to int ranges
-- Then query:
SELECT country FROM geoip_ranges
WHERE INET_ATON('203.0.113.45') BETWEEN start_ip AND end_ip
LIMIT 1;

INSTALL EXTENSION vsql_network_address;

CREATE TABLE geoip_ranges (
    start_ip   INET NOT NULL,
    end_ip     INET NOT NULL,
    country    CHAR(2),
    region     VARCHAR(100),
    INDEX idx_start (start_ip)
);

-- Import IPv4 ranges
INSERT INTO geoip_ranges (start_ip, end_ip, country) VALUES
    (inet_from_string('203.0.113.0'), inet_from_string('203.0.113.255'), 'US'),
    (inet_from_string('198.51.100.0'), inet_from_string('198.51.100.255'), 'DE');

-- Import IPv6 ranges in the same table
INSERT INTO geoip_ranges (start_ip, end_ip, country) VALUES
    (inet_from_string('2001:db8::'), inet_from_string('2001:db8::ffff'), 'US');

-- Look up a country for an IPv4 address
SELECT country, region FROM geoip_ranges
WHERE inet_compare(inet_from_string('203.0.113.45'), start_ip) >= 0
  AND inet_compare(inet_from_string('203.0.113.45'), end_ip)   <= 0
LIMIT 1;

-- Same query works for IPv6
SELECT country FROM geoip_ranges
WHERE inet_compare(inet_from_string('2001:db8::42'), start_ip) >= 0
  AND inet_compare(inet_from_string('2001:db8::42'), end_ip)   <= 0
LIMIT 1;

CREATE TABLE geoip_cidrs (
    network    CIDR NOT NULL,
    country    CHAR(2),
    INDEX idx_network (network)
);

INSERT INTO geoip_cidrs (network, country) VALUES
    (cidr_from_string('203.0.113.0/24'), 'US'),
    (cidr_from_string('198.51.100.0/24'), 'DE');

-- Look up by subnet match
SELECT country FROM geoip_cidrs
WHERE cidr_to_string(inet_network(inet_set_masklen(
    inet_from_string('203.0.113.45'),
    inet_masklen(inet_from_string(cidr_to_string(network)))
))) = cidr_to_string(network)
LIMIT 1;

-- After downloading GeoLite2-Country-Blocks-IPv4.csv
-- Convert the network column to start/end ranges and load:
LOAD DATA INFILE '/path/to/ipv4_ranges.csv'
INTO TABLE geoip_ranges
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@start_ip_str, @end_ip_str, country)
SET start_ip = inet_from_string(@start_ip_str),
    end_ip   = inet_from_string(@end_ip_str);

SELECT
    g.country,
    COUNT(*) AS request_count
FROM access_log a
JOIN geoip_ranges g
  ON inet_compare(a.ip_address, g.start_ip) >= 0
 AND inet_compare(a.ip_address, g.end_ip)   <= 0
GROUP BY g.country
ORDER BY request_count DESC;