具有自动增量主键的mysql LOAD DATA INFILE [英] mysql LOAD DATA INFILE with auto-increment primary key

问题描述

我正在尝试使用"LOAD DATA LOCAL INFILE'filename'INTO TABLE'tablename'"将数据文件加载到mysql表中.

I am trying to load a data file into mysql table using "LOAD DATA LOCAL INFILE 'filename' INTO TABLE 'tablename'".

问题在于源数据文件包含每个字段的数据，但缺少主键("id"列).我在创建数据库时添加了一个唯一的id字段，但是现在我需要从下一个字段开始将数据导入表中，并在导入时自动增加id字段.

The problem is the source data file contains data of every fields but the primary key is missing ('id' column). I add a unique id field while I create the database but now I need to import the data into the table starting from the next field and auto increment the id field while importing.

def create_table():
            cursor.execute ("""
                    CREATE TABLE variants
                    (
                    id integer(10) auto_increment primary key,
                    study_no CHAR(40),
                    other fields.....


                    )
                    """)

这是我的LOAD查询

query1= "LOAD DATA LOCAL INFILE '"+currentFile+"' INTO TABLE variants FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'"

有什么想法吗?

摘要: 创建一个带有附加ID字段的表，该字段将自动递增 将数据(20列)加载到21个字段的表中，跳过id字段 让id字段自动填充一个自动增量索引.

Summary: create a table with an additional id field that would auto increment load data (20 columns) into the table of 21 fields skipping the id field let the id field automatically populate with an auto increment index.

推荐答案

指定列列表:

默认情况下，当在LOAD DATA INFILE语句的末尾未提供任何列列表时，输入行应包含每个表列的字段.如果只想加载表的某些列，请指定列列表:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

这篇关于具有自动增量主键的mysql LOAD DATA INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！