API tools faq
paste
Advertisement
rg443

geoip2 lite country mysql import

rg443
Jan 7th, 2018
335
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 6.44 KB | None | 0 0
raw download clone embed print report
  1. /*
  2.  
  3. curl -O http://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip
  4. unzip -j GeoLite2-Country-CSV.zip
  5.  
  6.  
  7. */
  8.  
  9. -- geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name
  10. drop table if exists geoip2co2;
  11. CREATE TABLE geoip2co2 (
  12.    locId MEDIUMINT(6) UNSIGNED NOT NULL primary key,
  13.    cc ENUM('','AD','AE','AF','AG','AI','AL','AM','AO','AQ','AR','AS','AT','AU','AW','AX','AZ','BA','BB','BD','BE','BF','BG','BH','BI','BJ','BL','BM','BN','BO','BQ','BR','BS','BT','BW','BY','BZ','CA','CC','CD','CF','CG','CH','CI','CK','CL','CM','CN','CO','CR','CU','CV','CW','CX','CY','CZ','DE','DJ','DK','DM','DO','DZ','EC','EE','EG','EH','ER','ES','ET','FI','FJ','FK','FM','FO','FR','GA','GB','GD','GE','GF','GG','GH','GI','GL','GM','GN','GP','GQ','GR','GS','GT','GU','GW','GY','HK','HM','HN','HR','HT','HU','ID','IE','IL','IM','IN','IO','IQ','IR','IS','IT','JE','JM','JO','JP','KE','KG','KH','KI','KM','KN','KP','KR','KW','KY','KZ','LA','LB','LC','LI','LK','LR','LS','LT','LU','LV','LY','MA','MC','MD','ME','MF','MG','MH','MK','ML','MM','MN','MO','MP','MQ','MR','MS','MT','MU','MV','MW','MX','MY','MZ','NA','NC','NE','NF','NG','NI','NL','NO','NP','NR','NU','NZ','OM','PA','PE','PF','PG','PH','PK','PL','PM','PN','PR','PS','PT','PW','PY','QA','RE','RO','RS','RU','RW','SA','SB','SC','SD','SE','SG','SH','SI','SJ','SK','SL','SM','SN','SO','SR','SS','ST','SV','SX','SY','SZ','TC','TD','TF','TG','TH','TJ','TK','TL','TM','TN','TO','TR','TT','TV','TW','TZ','UA','UG','UM','US','UY','UZ','VA','VC','VE','VG','VI','VN','VU','WF','WS','XK','YE','YT','ZA','ZM','ZW') NOT NULL,
  14.    cn ENUM('','Afghanistan','Albania','Algeria','American Samoa','Andorra','Angola','Anguilla','Antarctica','Antigua and Barbuda','Argentina','Armenia','Aruba','Australia','Austria','Azerbaijan','Åland','Bahamas','Bahrain','Bangladesh','Barbados','Belarus','Belgium','Belize','Benin','Bermuda','Bhutan','Bolivia','Bonaire, Sint Eustatius, and Saba','Bosnia and Herzegovina','Botswana','Brazil','British Indian Ocean Territory','British Virgin Islands','Brunei','Bulgaria','Burkina Faso','Burundi','Cabo Verde','Cambodia','Cameroon','Canada','Cayman Islands','Central African Republic','Chad','Chile','China','Christmas Island','Cocos [Keeling] Islands','Colombia','Comoros','Congo','Cook Islands','Costa Rica','Croatia','Cuba','Curaçao','Cyprus','Czechia','Denmark','Djibouti','Dominica','Dominican Republic','East Timor','Ecuador','Egypt','El Salvador','Equatorial Guinea','Eritrea','Estonia','Ethiopia','Falkland Islands','Faroe Islands','Federated States of Micronesia','Fiji','Finland','France','French Guiana','French Polynesia','French Southern Territories','Gabon','Gambia','Georgia','Germany','Ghana','Gibraltar','Greece','Greenland','Grenada','Guadeloupe','Guam','Guatemala','Guernsey','Guinea','Guinea-Bissau','Guyana','Haiti','Hashemite Kingdom of Jordan','Heard Island and McDonald Islands','Honduras','Hong Kong','Hungary','Iceland','India','Indonesia','Iran','Iraq','Ireland','Isle of Man','Israel','Italy','Ivory Coast','Jamaica','Japan','Jersey','Kazakhstan','Kenya','Kiribati','Kosovo','Kuwait','Kyrgyzstan','Laos','Latvia','Lebanon','Lesotho','Liberia','Libya','Liechtenstein','Luxembourg','Macao','Macedonia','Madagascar','Malawi','Malaysia','Maldives','Mali','Malta','Marshall Islands','Martinique','Mauritania','Mauritius','Mayotte','Mexico','Monaco','Mongolia','Montenegro','Montserrat','Morocco','Mozambique','Myanmar [Burma]','Namibia','Nauru','Nepal','Netherlands','New Caledonia','New Zealand','Nicaragua','Niger','Nigeria','Niue','Norfolk Island','North Korea','Northern Mariana Islands','Norway','Oman','Pakistan','Palau','Palestine','Panama','Papua New Guinea','Paraguay','Peru','Philippines','Pitcairn Islands','Poland','Portugal','Puerto Rico','Qatar','Réunion','Republic of Korea','Republic of Lithuania','Republic of Moldova','Republic of the Congo','Romania','Russia','Rwanda','Saint Helena','Saint Lucia','Saint Martin','Saint Pierre and Miquelon','Saint Vincent and the Grenadines','Saint-Barthélemy','Samoa','San Marino','Saudi Arabia','São Tomé and Príncipe','Senegal','Serbia','Seychelles','Sierra Leone','Singapore','Sint Maarten','Slovakia','Slovenia','Solomon Islands','Somalia','South Africa','South Georgia and the South Sandwich Islands','South Sudan','Spain','Sri Lanka','St Kitts and Nevis','Sudan','Suriname','Svalbard and Jan Mayen','Swaziland','Sweden','Switzerland','Syria','Taiwan','Tajikistan','Tanzania','Thailand','Togo','Tokelau','Tonga','Trinidad and Tobago','Tunisia','Turkey','Turkmenistan','Turks and Caicos Islands','Tuvalu','U.S. Minor Outlying Islands','U.S. Virgin Islands','Uganda','Ukraine','United Arab Emirates','United Kingdom','United States','Uruguay','Uzbekistan','Vanuatu','Vatican City','Venezuela','Vietnam','Wallis and Futuna','Western Sahara','Yemen','Zambia','Zimbabwe') NOT NULL,
  15. -- cc varchar(2),
  16. -- cn varchar(100),
  17.    index cc (cc)
  18. )
  19. ENGINE=MyISAM;
  20.  
  21. truncate table geoip2co2;
  22. load data infile '/tmp/GeoLite2-Country-Locations-en.csv' into table geoip2co2
  23. CHARACTER SET 'utf8'
  24. FIELDS TERMINATED BY ','
  25. OPTIONALLY ENCLOSED BY '"'
  26. LINES TERMINATED BY '\n'
  27. IGNORE 1 lines
  28. (locId,@locale_code,@continent_code,@continent_name,cc,cn);
  29.  
  30.  
  31.  
  32. -- network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
  33. drop table if exists geoip2co1;
  34. CREATE TABLE geoip2co1 (
  35.     i1 INT(10) UNSIGNED NULL DEFAULT inet_aton(substring_index(prefix,'/',1)) primary key,
  36.     i2 INT(10) UNSIGNED NULL DEFAULT (inet_aton(substring_index(prefix,'/',1))+power(2,32-substring_index(prefix,'/',-1))-1),
  37.     prefix VARCHAR(18) NULL DEFAULT NULL,
  38.     loc  mediumint unsigned NULL,    
  39.     reg  mediumint unsigned NULL,
  40. --    loc2  varchar(20) NULL,
  41.     proxy ENUM('0','1') NOT NULL,
  42.     sat ENUM('0','1') NOT NULL,
  43.     index loc(loc),
  44.     index reg(reg)
  45. )
  46. ENGINE=MyISAM;
  47.  
  48.  
  49. truncate table geoip2co1;
  50. load data infile '/tmp/GeoLite2-Country-Blocks-IPv4.csv' into table geoip2co1
  51. CHARACTER SET 'utf8'
  52. FIELDS TERMINATED BY ','
  53. OPTIONALLY ENCLOSED BY '"'
  54. LINES TERMINATED BY '\n'
  55. IGNORE 1 lines
  56. (prefix,@loc0,@loc1,@loc2,proxy,sat) set loc=convert(@loc0,int),reg=convert(@loc1,int);
  57. update geoip2co1 set loc =null where loc=0;
  58. update geoip2co1 set reg =null where reg=0;
  59.  
  60. optimize table geoip2co1;
  61. optimize table geoip2co2;
  62.  
  63. drop view if exists geoip2co;
  64. create view geoip2co as select A.*,B.cc,C.cc regcc ,B.cn ,C.cn regcn from geoip2co1 A left join geoip2co2 B on A.loc=B.locid left join geoip2co2 C on A.reg=C.locid;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!