How to reinstate auto increment during importation of CSV file into MySql while retaining pervious numbering?

ID,CREATED,FILENUMBER,ADDRESS,CITY,STATE,ZIP

1008,"02/27/2014, 10:28",142840,124 North St,Anycity,NY,91111

1007,"02/12/2014, 21:06",142839,424 Maple Ave,Anycity,NY,91111

1006,"02/12/2014, 21:06",142839,143 Great Pl,Anycity,NY,91111

I have "like above" records in the comma separated value file format. I would like to import this table into MySQL and while doing so to:

retain existing records with same ID numbers to make ID to continue increment with each record added if one of the records is is deleted I don't want records to update their number (for example, if record with ID 1007 is deleted the remaining record numbers would 1006 and 1008) And the last, but not least I would like "FILENUMBER" to Auto increment on each new record with first two numbers being the year date (e.g. 14 stands for 2014) and the remaining four numbers are just incrementing with each record (e.g 142841, 142842, 142843, etc). Another example, if record is added December 31, 2014, the file number should auto increment with like 142844 and any next record addition on January 1, 2015 it would be 152845. and I also want retain each existing records same FILENUMBER numbers and then with new records continue to auto increment.

Is there a way to import this into MySQL and to make columns ID and FILENUMBER to do the above?

I am thinking of something like this:

CREATE TABLE `records` (
  `ID` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `CREATED` TIMESTAMP DEFAULT NOW(),
  `FILENUMBER` int(6) zerofill not null auto_increment, 
  `ADDRESS` varchar(50) NOT NULL,
  `CITY` varchar(12) NOT NULL,
  `STATE` varchar(2) NOT NULL,
  `ZIP` INT(10) NOT NULL);




load data local infile 'c:\\records.csv' 
into table records 
fields terminated by ','
lines terminated by '\n'
(ID, CREATED, FILENUMBER, ADDRESS, CITY, STATE, ZIP);

Solution

Some corrections:

You can't define multiple fields to have auto increment.
If you want to keep id with auto..., then filenumber int ... auto... is wrong.

Possible options (for enabling):

You better not define auto increment for filenumber field but for id field only.
For the latest inserted filenumber value to hold current year as prefix, then you need a trigger to set the 2 digit year as prefix.
You can use primary key value as suffix with year to form the filenumber.

And, to retain the former auto_increment value in the new table into which you are importing data, you need to use alter table command.

alter table table_name 
  auto_increment = new_number_from_where_to_start_with;