• <tfoot id='QHKff'></tfoot>

      <legend id='QHKff'><style id='QHKff'><dir id='QHKff'><q id='QHKff'></q></dir></style></legend>

          <bdo id='QHKff'></bdo><ul id='QHKff'></ul>

        <i id='QHKff'><tr id='QHKff'><dt id='QHKff'><q id='QHKff'><span id='QHKff'><b id='QHKff'><form id='QHKff'><ins id='QHKff'></ins><ul id='QHKff'></ul><sub id='QHKff'></sub></form><legend id='QHKff'></legend><bdo id='QHKff'><pre id='QHKff'><center id='QHKff'></center></pre></bdo></b><th id='QHKff'></th></span></q></dt></tr></i><div id='QHKff'><tfoot id='QHKff'></tfoot><dl id='QHKff'><fieldset id='QHKff'></fieldset></dl></div>

        <small id='QHKff'></small><noframes id='QHKff'>

        将 MaxMind 的 GeoLite2 导入 MySQL

        Importing MaxMind#39;s GeoLite2 to MySQL(将 MaxMind 的 GeoLite2 导入 MySQL)

        <small id='J1Bdc'></small><noframes id='J1Bdc'>

          <tbody id='J1Bdc'></tbody>
          <bdo id='J1Bdc'></bdo><ul id='J1Bdc'></ul>

            <i id='J1Bdc'><tr id='J1Bdc'><dt id='J1Bdc'><q id='J1Bdc'><span id='J1Bdc'><b id='J1Bdc'><form id='J1Bdc'><ins id='J1Bdc'></ins><ul id='J1Bdc'></ul><sub id='J1Bdc'></sub></form><legend id='J1Bdc'></legend><bdo id='J1Bdc'><pre id='J1Bdc'><center id='J1Bdc'></center></pre></bdo></b><th id='J1Bdc'></th></span></q></dt></tr></i><div id='J1Bdc'><tfoot id='J1Bdc'></tfoot><dl id='J1Bdc'><fieldset id='J1Bdc'></fieldset></dl></div>
            <legend id='J1Bdc'><style id='J1Bdc'><dir id='J1Bdc'><q id='J1Bdc'></q></dir></style></legend>

                  <tfoot id='J1Bdc'></tfoot>
                  本文介绍了将 MaxMind 的 GeoLite2 导入 MySQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  MaxMind 的

                  我记得很久以前为 CSV 数据库编写了一个导入脚本,但是您今天可以下载的 CSV 格式非常难以理解,至少对我来说:

                  <块引用>

                  network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider1.0.0.0/24,2077456,2077456,,0,01.0.1.0/24,1814991,1814991,,0,01.0.2.0/23,1814991,1814991,,0,01.0.4.0/22,2077456,2077456,,0,01.0.8.0/21,1814991,1814991,,0,01.0.16.0/20,1861060,1861060,,0,01.0.32.0/19,1814991,1814991,,0,01.0.64.0/18,1861060,1861060,,0,01.0.128.0/17,1605651,1605651,,0,0

                  我真的很专注于这里的基础知识.将数据库从其 CSV 表示形式导入 MySQL 的最有效和最简单的方法是什么?

                  解决方案

                  用一个简单的 SQL 脚本似乎真的不可能做到这一点,所以我用 C# 编写了一个.而且由于导入如此大的 MySQL 数据库并不是那么简单,我在脚本本身中实现了一个直接的 INSERT INTO.

                  需要像问题中草图一样的表格结构才能工作.

                  使用 MySql.Data.MySqlClient;使用系统;使用 System.Collections.Generic;使用 System.IO;使用 System.Linq;命名空间 GeoIPConvert{公共静态类程序{public static void Main(string[] args){//https://dev.maxmind.com/geoip/geoip2/geolite2/列表<国家>国家 = File.ReadAllLines("Countries.csv").Select(line => line.Split(',')).Where(line => line[4] != "" && line[5] != "").Select((line, index) => new Country{ID = Convert.ToInt32(line[0]),数据库 ID = 索引 + 1,Flag = line[4].ToLower(),Name = line[5].Replace(""", "")}).ToList();列表<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();//字符串 sql =//"插入 geoip_countries(Flag, Name) VALUES
                  " +//string.Join(",
                  ", countries.Select(country => "("" + country.Flag + "", "" + country.Name + "")").ToArray()) + "
                  " +//"插入 geoip_ipranges(CountryID, `From`, `To`) VALUES
                  " +//string.Join(",
                  ", 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){new MySqlCommand("INSERT INTO geoip_ipranges(CountryID, `From`, `To`) VALUES("" + ipRange.Country.DatabaseID + "", "" + ipRange.From + "", "" +ipRange.To + "")", sql).ExecuteNonQuery();Console.WriteLine(ipRange.To);}sql.关闭();}}私有静态元组ConvertCidrToRange(字符串cidr){string[] 部分 = cidr.Split('.', '/');uint ipnum = Convert.ToUInt32(parts[0]) <<24 |Convert.ToUInt32(parts[1])<<16 |Convert.ToUInt32(parts[2])<<8 |Convert.ToUInt32(parts[3]);uint 掩码 = uint.MaxValue <<(32 - Convert.ToInt32(parts[4]));return Tuple.Create(ipnum & mask, ipnum | (mask ^ uint.MaxValue));}}公共课国家{公共 int ID { 获取;放;}公共 int DatabaseID { 获取;放;}公共字符串标志 { 获取;放;}公共字符串名称 { 获取;放;}}公共类IPRange{公共国家国家{得到;放;}公共单位从{得到;放;}公共 uint 到 { 得到;放;}}}

                  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
                  " +
                              //  string.Join(",
                  ", countries.Select(country => "("" + country.Flag + "", "" + country.Name + "")").ToArray()) + "
                  " +
                              //  "INSERT INTO geoip_ipranges(CountryID, `From`, `To`) VALUES
                  " +
                              //  string.Join(",
                  ", 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  How to import CSV into sqlite using RSqlite?(如何使用 RSqlite 将 CSV 导入 sqlite?)
                  Import CSV to Update rows in table(导入 CSV 以更新表中的行)
                  Import / Export database with SQL Server Server Management Studio(使用 SQL Server Server Management Studio 导入/导出数据库)
                  How do you import a large MS SQL .sql file?(如何导入大型 MS SQL .sql 文件?)
                  How to save a Google maps overlay shape in the database?(如何在数据库中保存谷歌地图叠加形状?)
                  Is there a performance hit using decimal data types (MySQL / Postgres)(使用十进制数据类型(MySQL/Postgres)是否会影响性能)

                    <bdo id='l0HIc'></bdo><ul id='l0HIc'></ul>
                      • <tfoot id='l0HIc'></tfoot>

                      • <legend id='l0HIc'><style id='l0HIc'><dir id='l0HIc'><q id='l0HIc'></q></dir></style></legend>
                            <tbody id='l0HIc'></tbody>

                            <i id='l0HIc'><tr id='l0HIc'><dt id='l0HIc'><q id='l0HIc'><span id='l0HIc'><b id='l0HIc'><form id='l0HIc'><ins id='l0HIc'></ins><ul id='l0HIc'></ul><sub id='l0HIc'></sub></form><legend id='l0HIc'></legend><bdo id='l0HIc'><pre id='l0HIc'><center id='l0HIc'></center></pre></bdo></b><th id='l0HIc'></th></span></q></dt></tr></i><div id='l0HIc'><tfoot id='l0HIc'></tfoot><dl id='l0HIc'><fieldset id='l0HIc'></fieldset></dl></div>
                          1. <small id='l0HIc'></small><noframes id='l0HIc'>