· 6 years ago · Dec 25, 2019, 01:28 AM
1-- DROP TABLE if EXISTS dbipcc_new;
2-- CREATE TABLE dbipcc_new LIKE dbipcc;
3LOAD DATA INFILE '/tmp/dbip-country-lite-2019-12.csv'
4REPLACE INTO TABLE dbipcc_new FIELDS TERMINATED BY ','
5(@c1,@c2,cc) SET i1= IFNULL(INET_ATON(@c1),2130706433), i2= IFNULL(INET_ATON(@c2),2130706433);
6
7
8
9LOAD DATA INFILE '/tmp/dbip-city-lite-2019-12.csv'
10REPLACE INTO TABLE dbipci_new CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
11(@c1,@c2,@xx,cc,state,city,lat,lon) SET i1= IFNULL(INET_ATON(@c1),2130706433), i2= IFNULL(INET_ATON(@c2),2130706433);
12
13
14
15
16
17CREATE TABLE dbipcc_new (
18 i1 INT(10) UNSIGNED NOT NULL,
19 i2 INT(10) UNSIGNED NOT NULL,
20 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','BV','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','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','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','ZZ') NOT NULL,
21 PRIMARY KEY (i1),
22 INDEX CC (CC)
23)
24;
25
26
27
28CREATE TABLE dbipci_new (
29 i1 INT(10) UNSIGNED NOT NULL,
30 i2 INT(10) UNSIGNED NOT NULL,
31 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','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','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','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','TG','TH','TJ','TK','TL','TM','TN','TO','TR','TT','TV','TW','TZ','UA','UG','US','UY','UZ','VA','VC','VE','VG','VI','VN','VU','WF','WS','XK','YE','YT','ZA','ZM','ZW','ZZ') NOT NULL DEFAULT 'ZZ',
32 state VARCHAR(56) NULL DEFAULT '-',
33 city VARCHAR(80) NULL DEFAULT '-',
34 lat FLOAT NOT NULL,
35 lon FLOAT NOT NULL,
36 PRIMARY KEY (i1),
37 INDEX CC (CC)
38);
39
40
41
42
43/*
44select @a:=GROUP_CONCAT(distinct cc ORDER BY cc ) FROM dbipci_new;
45select CONCAT('\'',REPLACE(@a,',','\',\''),'\'');
46SELECT MAX(LENGTH(state)) state_len, MAX(LENGTH(city)) city_len FROM dbipci_new;
47
48curl -LRO https://download.db-ip.com/free/dbip-country-lite-2019-12.csv.gz
49curl -LRO https://download.db-ip.com/free/dbip-city-lite-2019-12.csv.gz
50
51*/