Скачиваем файл с CSV данными: GeoLite2-City-CSV_xxxxx.zip, где xxxxx какая-то дата, из личного кабинета Maxmind.
Распаковываем.
Теперь необходимо создать две таблицы:
CREATE TABLE `ip_blocks` (
`ip_from` int unsigned NOT NULL,
`ip_to` int unsigned NOT NULL,
`network` varchar(32) NOT NULL,
`geoname_id` int unsigned NOT NULL,
`registered_country_geoname_id` int unsigned NOT NULL,
`represented_country_geoname_id` int unsigned NOT NULL,
`is_anonymous_proxy` tinyint NOT NULL,
`is_satellite_provider` tinyint NOT NULL,
`postal_code` varchar(32) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`accuracy_radius` smallint unsigned NOT NULL
);
и
CREATE TABLE `ip_locations` (
`geoname_id` int unsigned NOT NULL,
`locale_code` varchar(32) NOT NULL,
`continent_code` char(2) NOT NULL,
`continent_name` varchar(32) NOT NULL,
`country_iso_code` char(2) NOT NULL,
`country_name` varchar(64) NOT NULL,
`subdivision_1_iso_code` varchar(3) NOT NULL,
`subdivision_1_name` varchar(128) COLLATE 'utf8mb4_general_ci' NOT NULL,
`subdivision_2_iso_code` varchar(3) NOT NULL,
`subdivision_2_name` varchar(128) COLLATE 'utf8mb4_general_ci' NOT NULL,
`city_name` varchar(128) COLLATE 'utf8mb4_general_ci' NOT NULL,
`metro_code` smallint unsigned NOT NULL,
`time_zone` varchar(64) NOT NULL
);
В первой будут храниться диапазоны IP адресов, а во второй текстовые данные городов и стран. Maxmind предлагает на выбор несколько вариантов перевода: английский, русский, немецкий, испанский, французский, японский, португальский, и китайский.
Следующим этапом мы будем загружать наши данные в базу. Это довольно длительная процедура, так как нам предстоит импортировать миллионы строк.
Если в настройках вашей базы запрещен прямой импорт из файлов, то необходимо эту функцию включить:
SET GLOBAL local_infile=1;
Теперь переходим непосредственно к импорту данных из CSV файла:
LOAD DATA LOCAL INFILE 'd:/GeoLite2/GeoLite2-City-Blocks-IPv4.csv' INTO TABLE ip_blocks COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
@network,
geoname_id,
registered_country_geoname_id,
represented_country_geoname_id,
is_anonymous_proxy,
is_satellite_provider,
postal_code,
latitude,
longitude,
accuracy_radius) SET
ip_from = INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)),
ip_to = (INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)) + (pow(2, (32-CONVERT(SUBSTRING(@network, LOCATE('/', @network) + 1), UNSIGNED INTEGER)))-1));
В первой строке укажите путь до вашего файла на диске:
LOAD DATA LOCAL INFILE 'd:/GeoLite2/GeoLite2-City-Blocks-IPv4.csv'
Импорт данных займет какое-то время. Зависит от мощности вашего компьютера.
Теперь импортируем текстовые данные:
LOAD DATA LOCAL INFILE 'd:/GeoLite2/GeoLite2-City-Locations-en.csv' INTO TABLE ip_locations CHARACTER SET UTF8MB4 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
geoname_id,
locale_code,
continent_code,
continent_name,
country_iso_code,
country_name,
subdivision_1_iso_code,
subdivision_1_name,
subdivision_2_iso_code,
subdivision_2_name,
city_name,
metro_code,
time_zone);
Выберите нужный вам языковой вариант файла.
Теперь, когда все данные загружены в базу, давайте создадим два поисковых индекса:
ALTER TABLE `ip_blocks` ADD PRIMARY KEY `ip_to` (`ip_to`);
ALTER TABLE `ip_locations` ADD PRIMARY KEY `geoname_id` (`geoname_id`);
Всё, наши данные готовы. Для удобства работы с ними вы можете создать функцию поиска:
DELIMITER $$
CREATE FUNCTION `IP2Location`(`ip` varchar(50))
RETURNS int(11)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE loc_id INT;
SELECT geoname_id INTO loc_id FROM ip_blocks WHERE ip_to >= INET_ATON(TRIM(ip)) ORDER BY ip_to LIMIT 1;
RETURN IFNULL(loc_id, 0);
Работать с функцией можно так:
SELECT * FROM ip_locations WHERE geoname_id = IP2Location("198.199.81.169");
Если по какой-то причине вы не любите функции, можно воспользоваться запросом:
SELECT geoname_id INTO loc_id FROM ip_blocks WHERE ip_to >= INET_ATON(TRIM(ip)) ORDER BY ip_to LIMIT 1;
Пользуйтесь на здоровье!
Перевел и исправил Феськов Кузьма
Maxmind предлагает два варианта использования гео-данных: CSV файл и бинарный mmdb, что не всегда является удобным. Сегодня мы научимся загружать данные в базу Mysql и работать с ними.ProxyNova.com, перевод Феськов Кузьма2020-12-03 http://www.sesmikcms.ru/resources/img/000/000/001/img_198_original-640-480.jpg http://www.sesmikcms.ru/pages/read/maxmind-geolite2-to-mysql/