mysql load data infile auto_increment mutiple incrementation

Question

while executing same file multiple times the auto incrementation value will be wrong. my file is sample.csv

name,phone,address
a,9401003026,dsa
b,9658746542,fsa
c,9865742310,hgfh
d,9865869537,hf
e,9401003026,hf
s,9658746542,hf
h,9865742310,hf
j,9865869537,hf

and my query is

LOAD DATA LOCAL INFILE '/home/anson/AnsonBackup/python/newtest/sample.csv'    
           INTO TABLE `sample`  COLUMNS TERMINATED BY ','  
           LINES TERMINATED BY '\n' IGNORE 1 LINES 
           (named,phone,address);

if i excecute one time the value of id will be 8 but when i reexecute the same file the id starts from 16..why????

my table is

CREATE TABLE `sample` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `named` VARCHAR(30) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

i tried this SQLyog community - MYSQL GUI v 11.27(64 bit) (c) 2001-2013 Webyog Inc — bob marti, Nov 17 '16 at 16:45
when i excecute the file at once the last id value will be 8 and its auto_increment is not 9.it displayed as 16 and when i reexecute the same file the id starts from 16..why???? — bob marti, Nov 18 '16 at 14:06

score 0 · Answer 1 · edited May 23 '17 at 12:01

0

If possible, never depend on auto_increment columns to have certain values, as the same issue you describe can happen. The id gets increased every time you insert in the table.

If you need to, you could include the id field in the csv also, so your import sentence would be:

LOAD DATA LOCAL INFILE '/home/anson/AnsonBackup/python/newtest/sample.csv'    
       INTO TABLE `sample`  COLUMNS TERMINATED BY ','  
       LINES TERMINATED BY '\n' IGNORE 1 LINES 
       (id, named, phone, address);

Or, if you can't do that, you have to reset the counter, like this:

ALTER TABLE tablename AUTO_INCREMENT = 1;

With the correct auto_increment you need instead of 1. More info about that here: related answer

edited May 23 '17 at 12:01

Community

1
1

answered Nov 17 '16 at 16:27

Mpacheco

165
3
15

i must be need to depend on auto increment. i cant pass the id like this (id, named, phone, address); – bob marti Nov 17 '16 at 16:54
if i use the ALTER TABLE tablename AUTO_INCREMENT = 1; i must be execute this value each time(more than one time) for my file execution – bob marti Nov 17 '16 at 17:02

score 0 · Accepted Answer · answered Jan 04 '17 at 13:56

Finally i got an answer

we must change its ENGINE from INNODB to MyISAM

CREATE TABLE `sample` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `named` VARCHAR(30) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
)  ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

mysql load data infile auto_increment mutiple incrementation

2 Answers2