Ipv6 creating table load data and retriving in mysql using ip_poly
1
vote
1
answer
43
views
CREATE TABLE ipv6_table
(
id
INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
ip_poly
POLYGON NOT NULL,
start_network
BIGINT(20) NOT NULL DEFAULT '0',
end_network
BIGINT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id
),
SPATIAL KEY idx_ip_poly
(ip_poly
)
) ENGINE=Innodb;
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/19225.csv'
INTO TABLE ipv6_table
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(
@start_network,
@end_network )
SET
id = NULL,
ip_poly = ST_GeomFromText(CONCAT(
'POLYGON((',
@start_network - 1, ' -1, ',
@end_network + 1, ' -1, ',
@end_network + 1, ' 1, ',
@start_network - 1, ' 1, ',
@start_network - 1, ' -1))'
)),
start_network = @start_network,
end_network = @end_network ;
SELECT * FROM ipv6_table WHERE ST_Intersects(ip_poly, ST_GEOMFROMTEXT(concat('POINT(', 2306128953120655672, ' 0)')));
I have raw data in csv file like this
2306128950956392448,2306128951224827903
2306128951224827904,2306128951241605119
2306128951241605120,2306128951493263359
2306128951493263360,2306128951510040575
2306128951510040576,2306128952030134271
2306128952030134272,2306128952046911487
Like this I'm trying to create table for Ipv6 data. and I'm loading data from csv file into it and I'm trying to fetch ipv6 data using start_network or end_network or range between them but it is returning multiple data. can anyone help me in this.
Asked by Aravind
(11 rep)
Feb 26, 2025, 10:17 PM
Last activity: Feb 27, 2025, 05:40 AM
Last activity: Feb 27, 2025, 05:40 AM