CODEWITHSUNDEEP
mysqlsqlload-data-infile
membersound
membersound

Reputation: 86747

How to LOAD DATA in mysql primary-key auto increment?

I want to LOAD DATA a csv file into an existing table, and still having the PK auto incremented.

Important: I don't want to explicit name all my (100) columns in the SQL statement during load data.

CREATE TABLE IF NOT EXISTS `person` (
    id int(20) NOT NULL AUTO_INCREMENT,
    firstname varchar(30),
    lastname varchar(30),   
    PRIMARY KEY (`id`)
) engine=innodb AUTO_INCREMENT=0;

sample.csv:

;john;doe
;jane;doe

or

\\N;john;doe
\\N;jane;doe

Both will produce:

mysql>  LOAD DATA INFILE 'sample.csv' INTO TABLE persons FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
mysql>  LOAD DATA INFILE 'sample.csv' INTO TABLE persons FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';
ERROR 1366 (HY000): Incorrect integer value: '\N' for column 'id' at row 1

Upvotes: 0

Views: 1691

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

I couldn't reproduce your error. It worked for me, testing on MySQL 5.6.37:

mysql> LOAD DATA LOCAL INFILE 'sample.csv' INTO TABLE persons 
  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';
Query OK, 2 rows affected (0.02 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from persons;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | john      | doe      |
|  2 | jane      | doe      |
+----+-----------+----------+

My sample data file used \N for the first column.

I also tried setting the first column to some non-NULL value, and then overriding that in my LOAD DATA INFILE. That may work for you without requiring you to type every column name.

mysql> LOAD DATA LOCAL INFILE 'sample.csv' INTO TABLE persons 
  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' 
  SET id = NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from persons;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | john      | doe      |
|  2 | jane      | doe      |
|  4 | john      | doe      |
|  5 | jane      | doe      |
+----+-----------+----------+

Upvotes: 1

Related Questions