· 8 years ago · Jan 07, 2018, 10:34 PM
1/*
2
3curl -O http://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip
4unzip -j GeoLite2-Country-CSV.zip
5
6
7*/
8
9-- geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name
10drop table if exists geoip2co2;
11CREATE 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)
19ENGINE=MyISAM;
20
21truncate table geoip2co2;
22load data infile '/tmp/GeoLite2-Country-Locations-en.csv' into table geoip2co2
23CHARACTER SET 'utf8'
24FIELDS TERMINATED BY ','
25OPTIONALLY ENCLOSED BY '"'
26LINES TERMINATED BY '\n'
27IGNORE 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
33drop table if exists geoip2co1;
34CREATE 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)
46ENGINE=MyISAM;
47
48
49truncate table geoip2co1;
50load data infile '/tmp/GeoLite2-Country-Blocks-IPv4.csv' into table geoip2co1
51CHARACTER SET 'utf8'
52FIELDS TERMINATED BY ','
53OPTIONALLY ENCLOSED BY '"'
54LINES TERMINATED BY '\n'
55IGNORE 1 lines
56(prefix,@loc0,@loc1,@loc2,proxy,sat) set loc=convert(@loc0,int),reg=convert(@loc1,int);
57update geoip2co1 set loc =null where loc=0;
58update geoip2co1 set reg =null where reg=0;
59
60optimize table geoip2co1;
61optimize table geoip2co2;
62
63drop view if exists geoip2co;
64create 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;