如何将文本文件中的数据导入到MySQL数据库中

Question

如何将文本文件中的数据导入到MySQL数据库中

63

我有一个名为text_file.txt的350MB文件，其中包含这个制表符分隔的数据：

345868230   1646198120  1531283146  Keyword_1531283146  1.55    252910000
745345566   1646198120  1539847239  another_1531276364  2.75    987831000
...

MySQL数据库名称：Xml_Date

数据库表格名称：PerformanceReport

我已经创建了包含所有目标字段的表格。

我想将这个文本文件数据导入到MySQL中。我在谷歌上查到了一些命令，例如LOAD DATA INFILE，但使用方法让我感到很困惑。

我该如何导入这个文本文件数据？

- Shiva Krishna Bavandla

9个回答

60

使用MySQL的LOAD DATA命令的步骤说明：

Create your table:

CREATE TABLE foo(myid INT, mymessage VARCHAR(255), mydecimal DECIMAL(8,4));

Create your tab delimited file (note there are tabs between the columns):

1   Heart disease kills     1.2
2   one out of every two    2.3
3   people in America.      4.5

Use the load data command:
```
LOAD DATA LOCAL INFILE '/tmp/foo.txt' 
INTO TABLE foo COLUMNS TERMINATED BY '\t';
```
If you get a warning that this command can't be run, then you have to enable the --local-infile=1 parameter described here: How can I correct MySQL Load Error

The rows get inserted:

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Check if it worked:

mysql> select * from foo;
+------+----------------------+-----------+
| myid | mymessage            | mydecimal |
+------+----------------------+-----------+
|    1 | Heart disease kills  |    1.2000 |
|    2 | one out of every two |    2.3000 |
|    3 | people in America.   |    4.5000 |
+------+----------------------+-----------+
3 rows in set (0.00 sec)

如何指定将文本文件列加载到哪些列中：

就像这样：

LOAD DATA LOCAL INFILE '/tmp/foo.txt' INTO TABLE foo
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(@col1,@col2,@col3) set myid=@col1,mydecimal=@col3;

文件内容被放入变量@col1、@col2、@col3中。myid获取第一列，而mydecimal获取第三列。如果运行此代码，则会省略第二行。

mysql> select * from foo;
+------+-----------+-----------+
| myid | mymessage | mydecimal |
+------+-----------+-----------+
|    1 | NULL      |    1.2000 |
|    2 | NULL      |    2.3000 |
|    3 | NULL      |    4.5000 |
+------+-----------+-----------+
3 rows in set (0.00 sec)

- Eric Leschinski

1

在我的MySQL版本（Ver 14.14 Distrib 5.5.41）中，我需要从这些命令中省略LOCAL。（ERROR 1148（42000）：此MySQL版本不允许使用该命令） - ManInTheArena

1

LOCAL关键字会影响文件的预期位置和错误处理。只有在您的服务器和客户端都已配置允许使用LOCAL时，LOCAL才能正常工作。https://dev.mysql.com/doc/refman/5.0/en/load-data.html - Eric Leschinski

1

在这里分享我的愚蠢：我以为“local”是指服务器本地（从mysql服务器引擎的视角来看）...但正如你所指出的Eric，文档中已经解释得很清楚了。它说LOCAL选项会导致mysqlimport从客户端主机读取数据文件。这意味着如果我在自己的计算机上运行MySQL Workbench，LOAD DATA命令将在我的计算机上查找文件！ - Andreas Jansson

你可能也想使用CHARSET SET来正确解析你的文件。 - Marek J.

16

如果你的表格不是以制表符分隔的，那么你需要像这样指定...

LOAD DATA LOCAL 
    INFILE '/tmp/mydata.txt' INTO TABLE PerformanceReport 
    COLUMNS TERMINATED BY '\t'  ## This should be your delimiter
    OPTIONALLY ENCLOSED BY '"'; ## ...and if text is enclosed, specify here

- peixe

2

你能详细阐述一下你的答案吗？ - noobie-php

4

LOAD DATA INFILE语句可以以非常高的速度将文本文件中的行读入到表中。

LOAD DATA INFILE '/tmp/test.txt' 
INTO TABLE test
FIELDS TERMINATED BY ','
LINES STARTING BY 'xxx';

如果数据文件看起来像这样：

xxx"abc",1
something xxx"def",2
"ghi",3

生成的行将是（“abc”，1）和（“def”，2）。文件中的第三行被跳过，因为它不包含前缀。

LOAD DATA INFILE 'data.txt'
INTO TABLE tbl_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

您还可以使用mysqlimport实用程序加载数据文件；它通过向服务器发送一个LOAD DATA INFILE语句来操作。

mysqlimport -u root -ptmppassword --local test employee.txt

test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

- priya gupta

2

您需要设置以下选项：

local-infile=1

将以下内容添加到my.cnf文件中的[mysql]入口或使用--local-infile选项调用mysql客户端：

mysql --local-infile -uroot -pyourpwd yourdbname

请确保在您的[mysqld]部分中也定义了相同的参数，以启用服务器端的“local infile”功能。

这是一种安全限制。

LOAD DATA LOCAL INFILE '/softwares/data/data.csv' INTO TABLE tableName;

- Balkrushna Patil

1

LOAD DATA INFILE '/home/userlap/data2/worldcitiespop.txt' INTO TABLE cc FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;

IGNORE 1 LINES用于跳过包含列名的初始标题行
FIELDS TERMINATED BY ','用于读取逗号分隔的文件
如果您在Windows系统上生成了文本文件，则可能需要使用LINES TERMINATED BY'\r\n'来正确读取文件，因为Windows程序通常使用两个字符作为行终止符。一些程序（例如WordPad）在编写文件时可能使用\r作为行终止符。要读取这种文件，请使用LINES TERMINATED BY'\r'。

- Amitesh Bharti

1

Make Sure your Local-Infile variable is set to True (ON)

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> set global local_infile=true;
Query OK, 0 rows affected (0.01 sec)

Find the correct path to store the txt files for loading in SQL tables

mysql>  SELECT @@GLOBAL.secure_file_priv;
+------------------------------------------------+
| @@GLOBAL.secure_file_priv                      |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)

Load using data infile from the path (Use backward slashes in path)

mysql>  load data infile 'C:/ProgramData/MySQL/MySQL Server
8.0/Uploads/text_file.txt' into table TABLE_NAME fields terminated by '\t' lines terminated by '\n';

- Kavya Goyal

1

对我来说，只需添加“LOCAL”关键字即可解决问题，请参见附加的图像以获得更简单的解决方案。

我的附加图像包含两种用例：

(a) 我遇到了这个错误。 (b) 仅通过添加“Local”关键字就解决了错误。

- swapnil shashank

0

1. 如果是制表符分隔的文本文件：

LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event

LINES TERMINATED BY '\r\n';

2. 否则：

LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event

FIELDS TERMINATED BY 'x'（这里的 x 可以是逗号 ',', 制表符 '\t', 分号 ';', 空格 ' '）

LINES TERMINATED BY '\r\n';

- silly

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Omnikrys · Accepted Answer

78

它应该就像...

LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE PerformanceReport;

默认情况下，LOAD DATA INFILE 使用制表符分隔，一行代表一行数据，所以应该可以顺利导入。

- Omnikrys

2

以上代码如何知道要将记录插入哪个数据库？ - LOKESH

1

@LOKESH：就它本身而言，是不能的。在此之前，您需要使用USE命令来设置使用哪个数据库。 - Christopher Berman

我认为数据表也需要在数据库中创建才能使其工作？ - HattrickNZ

@HattrickNZ 是的，而且问题中提到的操作者已经创建了表。 - Rahul Sharma

1

将数据从文件'/tmp/mydata.txt'导入到表PerformanceReport中，字段以'\t'为分隔符。 - Manoj Kumar

您可能还想使用CHARSET SET来正确解析您的文件。 - Marek J.