Sample Header Ad - 728x90

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