如何将主键作为自动增量的文本文件导入表 [英] How to import text file to table with primary key as auto-increment

查看:33
本文介绍了如何将主键作为自动增量的文本文件导入表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在一个文本文件中有一些批量数据需要导入到 MySQL 表中.该表由两个字段组成..

I have some bulk data in a text file that I need to import into a MySQL table. The table consists of two fields ..

  1. ID(带自动递增的整数)
  2. 名称(varchar)

文本文件是一大堆名称,每行一个名称...

The text file is a large collection of names with one name per line ...

(示例)

John Doe
Alex Smith
Bob Denver

我知道如何通过 phpMyAdmin 导入文本文件,但是,据我所知,我需要导入与目标表具有相同字段数的数据.有没有办法将我的文本文件中的数据导入一个字段并让 ID 字段自动增加?

I know how to import a text file via phpMyAdmin however, as far as I understand, I need to import data that has the same number of fields as the target table. Is there a way to import the data from my text file into one field and have the ID field auto-increment automatically?

在此先感谢您的帮助.

推荐答案

使用 LOADTABLE INFILE 导入时不正确,只需创建自动增量列作为最后一列/字段...作为其解析,如果您的表是定义为 30 列,但文本文件只有 1 个(或更少),它将首先按直接顺序导入前导列,因此请确保您的分隔符...在字段之间是正确的(对于将来的任何导入).同样,在您知道要导入的列数之后放置自动增量.

Not correct on import with the LOADTABLE INFILE, just create the auto-increment column as the LAST column/field... As its parsing, if your table is defined with 30 columns, but the text file only has 1 (or anything less), it will import the leading columns first, in direct sequence, so ensure your delimited with... is correct between fields (for any future imports). Again, put the auto-increment AFTER the number of columns you know are being imported.

create table YourMySQLTable
(  FullName varchar(30) not null ,
   SomeOtherFlds varchar(20) not null,
   IDKey int not null AUTO_INCREMENT,
   Primary KEY (IDKey)
);

请注意,IDKey 在表的最后一个字段中是自动递增的...无论您的 INPUT 流文本文件的列数可能少于最终表实际包含的列数.

Notice the IDKey is auto-increment in the last field of the table... regardless of your INPUT stream text file which may have less columns than your final table will actually hold.

然后,通过...导入数据

Then, import the data via...

LOAD DATA
    INFILE `C:SomePathWhereTextFileIsActualFile.txt`
    INTO TABLE YourMySQLTable
    COLUMNS TERMINATED BY `","`  
    LINES TERMINATED BY `
` ;

以上示例基于逗号分隔列表,每个字段都带有引号,例如myfield1"、anotherField"、LastField".此外,终止的是典型文本文件每行分隔的 cr/lf

Above example is based on comma seperated list with quotes around each field such as "myfield1","anotherField","LastField". Also, the terminated is the cr/lf that typical text files are delimited per row

在将全名作为单列的文本文件示例中,所有数据都将加载到YourMySQLTable"中的 FullName 列中.由于 IDKey 位于列表的 END,它仍然是从 1-?并且与入站文本中的列没有任何冲突.

In the sample of your text file having the full name as the single column, all the data would get loaded into the "YourMySQLTable" into the FullName column. Since the IDKey is at the END of the list, it will still be auto-increment assigned values from 1-? and not have any conflict with the columns from the inbound text.

这篇关于如何将主键作为自动增量的文本文件导入表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆