将MaxMind的GeoLite2导入MySQL [英] Importing MaxMind's GeoLite2 to MySQL
问题描述
MaxMind的
我记得很久以前为CSV数据库编写了一个导入脚本，但今天你可以下载的CSV有一个非常难以理解的格式，至少对我来说：
network，geoname_id，registered_country_geoname_id ，represent_country_geoname_id，is_anonymous_proxy，is_satellite_provider
1.0.0.0/24,2077456,2077456,,0,0
1.0.1.0/24,1814991,1814991,,0,0
1.0.2.0/ 23,1814991,1814991，，0,0
1.0.4.0/22,2077456,2077456,,0,0
1.0.8.0/2 1,1814991,1814991，，0,0
1.0.16.0/20,1861060,1861060,,0,0
1.0.32.0/19,1814991,1814991,,0,0
1.0.64.0/18,1861060,1861060,,0,0
1.0.128.0/17,1605651,1605651,,0,0
我真的很喜欢这里的基础知识。 将数据库从CSV表示导入MySQL的最有效，最简单的方法是什么？
使用简单的SQL脚本似乎无法做到这一点，所以我在 C＃中编写了一个。由于导入如此大的MySQL数据库并不那么简单，我在脚本本身实现了直接 INSERT INTO 。
表格结构就像问题中的草图一样，它需要工作。
使用MySql.Data .MySqlClient;
使用System;
使用System.Collections.Generic;
使用System.IO;
使用System.Linq;
命名空间GeoIPConvert
{
公共静态类程序
{
public static void Main（string [] args）
{
// https://dev.maxmind.com/geoip/geoip2/geolite2/
列表<国家> countries = File.ReadAllLines（Countries.csv）
.Select（line => line.Split（'，'））
.Where（line => line [4]！= && line [5]！=）
。选择（（line，index）=> new Country
{
ID = Convert.ToInt32（line [0] ），
DatabaseID = index + 1，
Flag = line [4] .ToLower（），
Name = line [5] .Replace（\，）
}）
.ToList（）;
List< IPRange> ipRanges = File.ReadAllLines（GeoIP.csv）
.Select（line => line .Split（'，'））
.Where（line => line [2]！=）
.Select（line => new IPRange
{
Country = countries.First（country => country.ID == Convert.ToInt32（line [2]）），
From = ConvertCidrToRange（line [0]）。Item1，
To = ConvertCidrToRange（line [0]）。Item2，
}）
.ToList（）;
// string sql =
//INSERT INTO geoip_countries（Flag，Name）VALUES\\ n+
// string.Join（， \\ n，countries.Select（country =>（\+ country.Flag +\，\+ country.Name +\））。ToArray（ ））+\\\\ n+
//INSERT INTO geoip_ipranges（CountryID，`From`，`To`）VALUES\\ n+
// string。加入（，\\\\ n，ipRanges.Select（iprange =>（\+ iprange.Country.DatabaseID +\，\+ iprange.From +\ ，\+ iprange.To +\））。ToArray（））;
//File.WriteAllText(\"Import.sql，sql）;
使用（MySqlConnection sql = new MySqlConnection（Server = localhost; Database = test_db; Uid = root;））
{
sql.Open（）;
foreach（国家/地区）
{
new MySqlCommand（INSERT INTO geoip_countries（Flag，Name）VALUES（\+ country.Flag +\ ，\+ country.Name +\），sql）.ExecuteNonQuery（）;
}
foreach（IPRange ipRange in ipRanges）
{
new MySqlCommand（INSERT INTO geoip_ipranges（CountryID，`From`，`To`）VALUES（\+ ipRange.Country.DatabaseID +\，\+ ipRange.From +\，\+ ipRange.To +\），sql）.ExecuteNonQuery（）;
Console.WriteLine（ipRange.To）;
}
sql.Close（）;
}
}
private static Tuple< uint，uint> ConvertCidrToRange（string cidr）
{
string [] parts = cidr.Split（'。'，'/'）;
uint ipnum = Convert.ToUInt32（parts [0]）<< 24 | Convert.ToUInt32（parts [1]）<< 16 | Convert.ToUInt32（parts [2]）<< 8 | Convert.ToUInt32（份[3]）;
uint mask = uint.MaxValue<< （32 - Convert.ToInt32（parts [4]））;
返回Tuple.Create（ipnum& mask，ipnum |（mask ^ uint.MaxValue））;
}
}
公共类国家
{
public int ID {get;组; }
public int DatabaseID {get;组; }
public string Flag {get;组; }
public string Name {get;组; }
}
公共类IPRange
{
public Country Country {get;组; }
public uint来自{get;组; }
public uint To {get;组; }
}
}
MaxMind's GeoLite2 is a wonderful database and is very useful if you want to map IP addresses to countries.
To do this efficiently, I want to import it into a MySQL database with a scheme like this:
I remember writing an import script for the CSV database long time ago, but the CSV as you can download it today has a very difficult to understand format, at least to me:
network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider 1.0.0.0/24,2077456,2077456,,0,0 1.0.1.0/24,1814991,1814991,,0,0 1.0.2.0/23,1814991,1814991,,0,0 1.0.4.0/22,2077456,2077456,,0,0 1.0.8.0/21,1814991,1814991,,0,0 1.0.16.0/20,1861060,1861060,,0,0 1.0.32.0/19,1814991,1814991,,0,0 1.0.64.0/18,1861060,1861060,,0,0 1.0.128.0/17,1605651,1605651,,0,0
I'm really stuck at the basics here. What is the most efficient and easiest way to import the database from its CSV representation into MySQL?
It really doesn't seem possible to do this with a simple SQL script, so I've written one in C#. And since importing MySQL databases that are so big is not that simple, I implemented a direct INSERT INTO into the script itself.
A table structure like the one on the sketch in the question is required for it to work.
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace GeoIPConvert
{
public static class Program
{
public static void Main(string[] args)
{
// https://dev.maxmind.com/geoip/geoip2/geolite2/
List<Country> countries = File.ReadAllLines("Countries.csv")
.Select(line => line.Split(','))
.Where(line => line[4] != "" && line[5] != "")
.Select((line, index) => new Country
{
ID = Convert.ToInt32(line[0]),
DatabaseID = index + 1,
Flag = line[4].ToLower(),
Name = line[5].Replace("\"", "")
})
.ToList();
List<IPRange> ipRanges = File.ReadAllLines("GeoIP.csv")
.Select(line => line.Split(','))
.Where(line => line[2] != "")
.Select(line => new IPRange
{
Country = countries.First(country => country.ID == Convert.ToInt32(line[2])),
From = ConvertCidrToRange(line[0]).Item1,
To = ConvertCidrToRange(line[0]).Item2,
})
.ToList();
//string sql =
// "INSERT INTO geoip_countries(Flag, Name) VALUES\r\n" +
// string.Join(",\r\n", countries.Select(country => "(\"" + country.Flag + "\", \"" + country.Name + "\")").ToArray()) + "\r\n" +
// "INSERT INTO geoip_ipranges(CountryID, `From`, `To`) VALUES\r\n" +
// string.Join(",\r\n", ipRanges.Select(iprange => "(\"" + iprange.Country.DatabaseID + "\", \"" + iprange.From + "\", \"" + iprange.To + "\")").ToArray());
//File.WriteAllText("Import.sql", sql);
using (MySqlConnection sql = new MySqlConnection("Server=localhost;Database=test_db;Uid=root;"))
{
sql.Open();
foreach (Country country in countries)
{
new MySqlCommand("INSERT INTO geoip_countries(Flag, Name) VALUES(\"" + country.Flag + "\", \"" + country.Name + "\")", sql).ExecuteNonQuery();
}
foreach (IPRange ipRange in ipRanges)
{
new MySqlCommand("INSERT INTO geoip_ipranges(CountryID, `From`, `To`) VALUES(\"" + ipRange.Country.DatabaseID + "\", \"" + ipRange.From + "\", \"" + ipRange.To + "\")", sql).ExecuteNonQuery();
Console.WriteLine(ipRange.To);
}
sql.Close();
}
}
private static Tuple<uint, uint> ConvertCidrToRange(string cidr)
{
string[] parts = cidr.Split('.', '/');
uint ipnum = Convert.ToUInt32(parts[0]) << 24 | Convert.ToUInt32(parts[1]) << 16 | Convert.ToUInt32(parts[2]) << 8 | Convert.ToUInt32(parts[3]);
uint mask = uint.MaxValue << (32 - Convert.ToInt32(parts[4]));
return Tuple.Create(ipnum & mask, ipnum | (mask ^ uint.MaxValue));
}
}
public class Country
{
public int ID { get; set; }
public int DatabaseID { get; set; }
public string Flag { get; set; }
public string Name { get; set; }
}
public class IPRange
{
public Country Country { get; set; }
public uint From { get; set; }
public uint To { get; set; }
}
}
这篇关于将MaxMind的GeoLite2导入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
