将MaxMind的GeoLite2导入MySQL
将MaxMind的GeoLite2导入MySQL
提问于 2015-08-14 23:16:29
MaxMind的GeoLite2是一个非常好的数据库，如果您想要将IP地址映射到国家，则非常有用。
为了有效地完成这一任务，我希望将其导入到MySQL数据库中，其方案如下：
我记得很久以前为CSV数据库编写了一个导入脚本，但是您今天可以下载的CSV有一个很难理解的格式，至少对我来说：
网络，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，01.0.2.0/23,1814991,1814991，0，01.0.4.0/22,2077456,2077456 ,0,0 1.0.8.0/21,181491,1814991 ,0,0 1.0.16.0/20,18610,18660，,0,0 1.0.32.0/19,181491,181491，0,0，1.0.128.0/17,1605651,1605651，,0,0
我真的被困在这里了。--什么是将数据库从CSV表示形式导入MySQL?的最有效和最简单的方法？
回答 4
Stack Overflow用户
回答已采纳
发布于 2015-08-15 20:50:04
使用一个简单的SQL脚本似乎是不可能的，所以我用C#编写了一个脚本。而且，由于导入这么大的MySQL数据库并不那么简单，所以我实现了一个直接INSERT INTO到脚本本身。
像问题中的草图那样的表格结构是它工作所必需的。
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; }
}
}Stack Overflow用户
发布于 2015-08-15 00:03:32
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
create table thing1
( network varchar(20) not null,
geoname_id varchar(20) not null,
registered_country_geoname_id varchar(20) not null,
represented_country_geoname_id varchar(20) not null,
is_anonymous_proxy varchar(20) not null,
is_satellite_provider varchar(20) not null
);
LOAD DATA INFILE 'c:\\dev\\ipaddr.txt'
INTO TABLE thing1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@v1,@v2,@v3,@v4,@v5,@v6)
set network=ifnull(@v1,''),
geoname_id=ifnull(@v2,''),
registered_country_geoname_id=ifnull(@v3,''),
represented_country_geoname_id=ifnull(@v4,''),
is_anonymous_proxy=ifnull(@v5,''),
is_satellite_provider=ifnull(@v6,'');以上这些对我来说都很好。
下面编辑的开始
对于下面的内容，在斯蒂金-德维特在评论中提出的要点之后，尝试改进这个答案。
但是，请注意，注释中引用的博客在update语句中出现了进入into的错误。所以，在我整理出之前，我发现了一个varchar修改，如下所示。
Edit1 (请参阅下面的注释)：
Alter the table to get a "from ip to ip range"
alter table thing1 add column from_ip varchar(20), add column to_ip varchar(20);
-- note that those two are nullable at the moment. You can always change that later刚刚添加了varchar的更新表
update thing1
set from_ip= INET_NTOA(INET_ATON( SUBSTRING_INDEX(network, '/', 1))
& 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(network, '/', -1)) ) -1 )),
to_ip= INET_NTOA(INET_ATON( SUBSTRING_INDEX(network, '/', 1))
| ((0x100000000 >> SUBSTRING_INDEX(network, '/', -1) ) -1 ))
select * from thing1;(对于上述更新声明，请在此回答中对贝恩德·布冯进行信用评估)
以上更新语句的结果：
mysql> select network,from_ip,to_ip from thing1;
+------------+---------+-----------+
| network | from_ip | to_ip |
+------------+---------+-----------+
| 1.0.1.0/24 | 1.0.1.0 | 1.0.1.255 |
| 1.0.2.0/23 | 1.0.2.0 | 1.0.3.255 |
| 1.0.4.0/22 | 1.0.4.0 | 1.0.7.255 |
+------------+---------+-----------+从这里开始，查看MySQL手册页面杂项职能 for INET_ATON(expr)。
Edit2 (再次感谢斯蒂金-德维特 )：
alter table thing1 add column uint_from_ip int unsigned, add column uint_to_ip int unsigned;
UPDATE thing1 SET uint_from_ip = inet_aton(SUBSTRING(network, 1, LOCATE('/', network) - 1)),
uint_to_ip = (inet_aton(SUBSTRING(network, 1, LOCATE('/', network) - 1)) + (pow(2, (32-CONVERT(SUBSTRING(network, LOCATE('/', network) + 1), UNSIGNED INT)))-1));结果：
select network,from_ip,to_ip,uint_from_ip,uint_to_ip from thing1;
+------------+---------+-----------+--------------+------------+
| network | from_ip | to_ip | uint_from_ip | uint_to_ip |
+------------+---------+-----------+--------------+------------+
| 1.0.1.0/24 | 1.0.1.0 | 1.0.1.255 | 16777472 | 16777727 |
| 1.0.2.0/23 | 1.0.2.0 | 1.0.3.255 | 16777728 | 16778239 |
| 1.0.4.0/22 | 1.0.4.0 | 1.0.7.255 | 16778240 | 16779263 |
+------------+---------+-----------+--------------+------------+(上面提到的一些修复后的S0BEIT博客功劳)
Stack Overflow用户
发布于 2015-08-15 02:49:26
我建议使用MaxMind's GeoIP2型CSV变换器将其放在您想要的格式中。有一个-include-integer-range选项，它将提供我认为您正在寻找的整数列。二进制文件可用于Windows、Linux (amd64)和OS。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32019915
复制相关文章
相似问题
腾讯云开发者
