mysql - mysql主键自增如何LOAD数据？

Question

我想将 csv 文件LOAD DATA加载到现有表中，并且PK仍然自动递增。

重要提示:我不想在加载数据期间在 SQL 语句中显式命名所有 (100) 列。

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;

样本.csv:

;john;doe
;jane;doe

或

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

两者都会产生:

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

Answer 1

我无法重现您的错误。它对我有用，在 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      |
+----+-----------+----------+

我的示例数据文件使用 \N 作为第一列。

我还尝试将第一列设置为某个非 NULL 值，然后在 LOAD DATA INFILE 中覆盖该值。这可能适合您，而不需要您键入每个列名称。

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      |
+----+-----------+----------+