MySql 文件导入（LOAD DATA LOCAL INFILE）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4658550/
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
MySql file import (LOAD DATA LOCAL INFILE)
提问by Flukey
I have a table called city:
我有一张名为 city 的表：
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| country_id | mediumint(9) | NO | MUL | NULL | |
| region_id | bigint(20) | NO | MUL | NULL | |
| city | varchar(45) | NO | | NULL | |
| latitude | float(18,2) | NO | | NULL | |
| longitude | float(18,2) | NO | | NULL | |
| timezone | varchar(10) | NO | | NULL | |
| dma_id | mediumint(9) | YES | | NULL | |
| code | varchar(4) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
I have a simple file (just a test file) to import:
我有一个简单的文件（只是一个测试文件）要导入：
"id","country_id","region_id","city","latitude","longitude","timezone","dma_id","code"
42231,1,833,"Herat","34.333","62.2","+04:30",0,"HERA"
5976,1,835,"Kabul","34.517","69.183","+04:50",0,"KABU"
42230,1,852,"Mazar-e Sharif","36.7","67.1","+4:30",0,"MSHA"
42412,2,983,"Korce","40.6162","20.7779","+01:00",0,"KORC"
5977,2,1011,"Tirane","41.333","19.833","+01:00",0,"TIRA"
5978,3,856,"Algiers","36.763","3.051","+01:00",0,"ALGI"
5981,3,858,"Skikda","36.879","6.907","+01:00",0,"SKIK"
5980,3,861,"Oran","35.691","-0.642","+01:00",0,"ORAN"
I run this command:
我运行这个命令：
LOAD DATA LOCAL INFILE 'cities_test.txt' INTO TABLE city FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Output:
输出：
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0
No records are inserted and I don't know why.
没有插入记录，我不知道为什么。
Any ideas?
有任何想法吗？
Thanks!
谢谢！
Jamie
杰米
回答by Flukey
Worked it out. Silly mistake.
解决了。愚蠢的错误。
Had to change this:
不得不改变这个：
LINES TERMINATED BY '\r\n'
To this:
对此：
LINES TERMINATED BY '\n'
:-)
:-)
回答by Onirec
I had the same problem, but I try this, erase the first row
我有同样的问题，但我试试这个，擦除第一行
`("id","country_id","region_id","city,"latitude","longitude",
"timezone","dma_id","code")` in your file to import.
Now when you run the comand write like this
现在当你运行命令时，像这样写
mysql> LOAD DATA LOCAL
INFILE 'cities_test.txt'
INTO TABLE city FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
And that is all.
这就是全部。
It worked for me :D
它对我有用：D
回答by devOpsTools
I had same issue on mac, Try this if you are using mac
我在 mac 上遇到了同样的问题，如果您使用的是 mac，请尝试此操作
LOAD DATA INFILE 'sqlScript1.txt' INTO TABLE USER
FIELDS TERMINATED BY ',' LINES STARTING BY '\r';
回答by Hadas Zeilberger
For me, what worked on a mac was
对我来说，在 Mac 上有效的是
LOAD DATA LOCAL
INFILE 'cities_test.txt'
INTO TABLE city FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r';
Since Macs use carriage return for its line break you must use '/r'
由于 Mac 使用回车作为换行符，因此您必须使用 '/r'
