MySQL 如何将文本文件中的数据导入mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13579810/
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 import data from text file to mysql database
提问by Shiva Krishna Bavandla
I have a 350MB file named text_file.txtcontaining this tab delimited data:
我有一个 350MB 的文件，text_file.txt其中包含此制表符分隔的数据：
345868230 1646198120 1531283146 Keyword_1531283146 1.55 252910000
745345566 1646198120 1539847239 another_1531276364 2.75 987831000
...
MySQL Database name: Xml_Date
MySQL 数据库名称： Xml_Date
Database table: PerformanceReport
数据库表： PerformanceReport
I have already created the table with all the destination fields.
我已经创建了包含所有目标字段的表。
I want to import this text file data into a MySQL. I googled and found some commands like LOAD DATA INFILEand quite confused on how to use it.
我想将此文本文件数据导入 MySQL。我用谷歌搜索并发现了一些命令，比如LOAD DATA INFILE如何使用它并且很困惑。
How can I import this text file data?
如何导入此文本文件数据？
回答by Omnikrys
It should be as simple as...
它应该像...一样简单
LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE PerformanceReport;
By default LOAD DATA INFILEuses tab delimited, one row per line, so should take it in just fine.
默认情况下LOAD DATA INFILE使用制表符分隔，每行一行，所以应该很好地接受它。
回答by Eric Leschinski
Walkthrough on using MySQL's LOAD DATA command:
使用 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.5Use 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=1parameter described here: How can I correct MySQL Load ErrorThe rows get inserted:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0Check 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)
创建你的表：
CREATE TABLE foo(myid INT, mymessage VARCHAR(255), mydecimal DECIMAL(8,4));创建您的制表符分隔文件（注意列之间有制表符）：
1 Heart disease kills 1.2 2 one out of every two 2.3 3 people in America. 4.5使用加载数据命令：
LOAD DATA LOCAL INFILE '/tmp/foo.txt' INTO TABLE foo COLUMNS TERMINATED BY '\t';如果您收到无法运行此命令的警告，那么您必须启用
--local-infile=1此处描述的参数：如何更正 MySQL 加载错误行被插入：
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0检查它是否有效：
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)
How to specify which columns to load your text file columns into:
如何指定要将文本文件列加载到哪些列：
Like this:
像这样：
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;
The file contents get put into variables @col1, @col2, @col3. myid gets column 1, and mydecimal gets column 3. If this were run, it would omit the second row:
文件内容被放入变量@col1、@col2、@col3。myid 获得第 1 列，而 mydecimal 获得第 3 列。如果运行，它将省略第二行：
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)
回答by peixe
If your table is separated by others than tabs, you should specify it like...
如果您的表格由制表符以外的其他人分隔，则应将其指定为...
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
回答by priya gupta
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.
LOAD DATA INFILE 语句以非常高的速度将文本文件中的行读取到表中。
LOAD DATA INFILE '/tmp/test.txt'
INTO TABLE test
FIELDS TERMINATED BY ','
LINES STARTING BY 'xxx';
If the data file looks like this:
如果数据文件如下所示：
xxx"abc",1
something xxx"def",2
"ghi",3
The resulting rows will be ("abc",1) and ("def",2). The third row in the file is skipped because it does not contain the prefix.
结果行将是 ("abc",1) 和 ("def",2)。文件中的第三行被跳过，因为它不包含前缀。
LOAD DATA INFILE 'data.txt'
INTO TABLE tbl_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server
您还可以使用 mysqlimport 实用程序加载数据文件；它通过向服务器发送 LOAD DATA INFILE 语句来操作
mysqlimport -u root -ptmppassword --local test employee.txt
test.employee: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
回答by Balkrushna Patil
You should set the option:
您应该设置选项：
local-infile=1
into your [mysql] entry of my.cnf file or call mysql client with the --local-infile option:
进入 my.cnf 文件的 [mysql] 条目或使用 --local-infile 选项调用 mysql 客户端：
mysql --local-infile -uroot -pyourpwd yourdbname
You have to be sure that the same parameter is defined into your [mysqld] section too to enable the "local infile" feature server side.
您必须确保在 [mysqld] 部分中也定义了相同的参数以启用“本地 infile”功能服务器端。
It's a security restriction.
这是一个安全限制。
LOAD DATA LOCAL INFILE '/softwares/data/data.csv' INTO TABLE tableName;
回答by Shu Zhang
1. if it's tab delimited txt file:
1.如果是制表符分隔的txt文件：
LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event
LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE 事件
LINES TERMINATED BY '\r\n';
由 '\r\n' 终止的行；
2. otherwise:
2. 否则：
LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE event
LOAD DATA LOCAL INFILE 'D:/MySQL/event.txt' INTO TABLE 事件
FIELDS TERMINATED BY 'x' (here x could be comma ',', tab '\t', semicolon ';', space ' ')
以 'x' 结尾的字段（这里 x 可以是逗号 ','、制表符 '\t'、分号 ';'、空格 ' '）
LINES TERMINATED BY '\r\n';
由 '\r\n' 终止的行；
回答by Amitesh
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 to skip over an initial header line containing column names
- FIELDS TERMINATED BY ',' is to read the comma-delimited file
- If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.
- IGNORE 1 LINES 跳过包含列名的初始标题行
- FIELDS TERMINATED BY ',' 是读取逗号分隔的文件
- 如果您在 Windows 系统上生成了文本文件，则可能必须使用 LINES TERMINATED BY '\r\n' 来正确读取文件，因为 Windows 程序通常使用两个字符作为行终止符。某些程序（例如 WordPad）在写入文件时可能会使用 \r 作为行终止符。要读取此类文件，请使用 LINES TERMINATED BY '\r'。
回答by swapnil shashank
For me just adding the "LOCAL" Keyword did the trick, please see the attached image for easier solution.
对我来说，只需添加“LOCAL”关键字就可以解决问题，请参阅所附图片以获取更简单的解决方案。
My attached image contains both use cases:
我附加的图像包含两个用例：
(a) Where I was getting this error. (b) Where error was resolved by just adding "Local" keyword.
(a) 我在哪里收到这个错误。(b) 通过添加“Local”关键字解决错误的地方。
