geoip2 lite asn mysql import

/*
curl -O http://geolite.maxmind.com/download/geoip/database/GeoLite2-ASN-CSV.zip
unzip -j GeoLite2-ASN-CSV.zip

*/

drop table if exists geoip2asn;
CREATE TABLE geoip2asn (
    i1 INT(10) UNSIGNED NULL DEFAULT inet_aton(substring_index(prefix,'/',1)) primary key,
    i2 INT(10) UNSIGNED NULL DEFAULT (inet_aton(substring_index(prefix,'/',1))+power(2,32-substring_index(prefix,'/',-1))-1),
    prefix VARCHAR(18) NULL DEFAULT NULL,
    asn INT(10) UNSIGNED NULL DEFAULT NULL,
    asname VARCHAR(100) NULL DEFAULT NULL,
    index asn(asn)
)
ENGINE=MyISAM;



truncate table geoipasn;
load data infile '/tmp/GeoLite2-ASN-Blocks-IPv4.csv' into table geoip2asn
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 lines
(prefix,asn,asname);
optimize table geoipasn;

/*
select asn,sum(1)n,sum(i2-i1)+2 ips,concat("AS",asn," - ",asname)asdescr from geoip2asn group by asn with rollup;
select i1,i2,prefix,asn,asname,convert(substring_index(prefix,'/',-1),int) as pl,i2-i1+1 ips from geoip2asn where asn=3356 order by i1,i2 desc;

-- select * from geoip2asn where inet_aton('195.177.230.0') between i1 and i2;
-- set @i:=inet_aton('8.8.8.8');select * from (select * from geoip2asn where @i>=i1 order by i1  desc limit 1) A where i2>=@i;

*/