如何在 mysql 中加载数据 INFILE，第一个列是自动增量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6017032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to LOAD DATA INFILE in mysql with first col being Auto Increment?
提问by DucDigital
Currently, We have a table similar to this:
目前，我们有一个类似于这样的表：
---------------------
ID | AField | BField|
---------------------
The ID is Auto Increment
ID是自动增量
How do I create a CSV that can let the database auto populate the ID field with auto increment number?
如何创建一个可以让数据库使用自动增量编号自动填充 ID 字段的 CSV？
We've tried the following CSV but it doesn't work:
我们已经尝试了以下 CSV，但它不起作用：
afieldvalue, bfieldvalue (With Column definition but still doesn't work)
0,afieldvalue,bfieldvalue
NULL,afieldvalue,bfieldvalue
回答by Ike Walker
The best thing to do is just include the 2 non-auto-increment columns in the CSV, and then explicitly set the ID column to NULL in the load data infile statement.
最好的做法是在 CSV 中包含 2 个非自动增量列，然后在 load data infile 语句中将 ID 列显式设置为 NULL。
Something like this:
像这样的东西：
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(AField, BField)
SET ID = NULL;
回答by Alexey Bakulin
you can "omit" ID field in data row by placing delimiter sign in the beginning of a row, like (for table's schema ID,AField,BField):
您可以通过在行的开头放置分隔符来“省略”数据行中的 ID 字段，例如（对于表的架构ID,AField,BField）：
,afieldvalue,bfieldvalue
...
SQL engine will assign NULLto IDfield while reading such csv file
SQL 引擎将在读取此类 csv 文件时分配NULL给ID字段
回答by rolland winner
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r'
(AField, BField);
回答by Kristofer
Try to use NULL as the value in the csv file for the ID field.
尝试使用 NULL 作为 csv 文件中 ID 字段的值。
回答by Shubhanshu Mishra
NULLin CSV file is read as a STRING in MySQL. If you want to have null values in a MySQL column instead use \Nin place of `NULL. This will fix the issue and you will get the required result.
NULLCSV 文件中的 STRING 在 MySQL 中被读取。如果您想在 MySQL 列中使用空值，请使用\N代替`NULL。这将解决问题，您将获得所需的结果。
回答by Rolf
If you have only a small number of columns, you can just name them:
LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable (AField);(assuming only ID & AField)
如果您只有少量列，则可以将它们命名为：（
LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable (AField);假设只有 ID 和 AField）
回答by Pete - iCalculator
LINES TERMINATED BY '\r\n'
That was the key to success for me when using a text file
这是我在使用文本文件时成功的关键
