如何在 mysql 中加载数据 INFILE,第一个列是自动增量?

2021-11-20 00:00:00 sql mysql

目前,我们有一个类似这样的表格:

Currently, We have a table similar to this:

---------------------
ID | AField | BField|
---------------------

ID 为自动递增

如何创建一个 CSV 文件,让数据库使用自动递增编号自动填充 ID 字段?

How do I create a CSV that can let the database auto populate the ID field with auto increment number?

我们已经尝试了以下 CSV,但它不起作用:

We've tried the following CSV but it doesn't work:

afieldvalue, bfieldvalue (With Column definition but still doesn't work)
0,afieldvalue,bfieldvalue 
NULL,afieldvalue,bfieldvalue

推荐答案

最好的做法是在 CSV 中包含 2 个非自增列,然后在加载数据中将 ID 列显式设置为 NULLinfile 语句.

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.

像这样:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(AField, BField)
SET ID = NULL;

相关文章