MYSQL 使用 LOAD DATA INFILE 从 csv 导入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14127529/
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 import data from csv using LOAD DATA INFILE
提问by MANJEET
I am importing some data of 20000 rows from a CSV file into Mysql.
我正在将 20000 行的一些数据从 CSV 文件导入 Mysql。
Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns?
CSV 中的列与 MySQL 表的列的顺序不同。如何自动分配Mysql表列对应的列？
When I execute
当我执行
LOAD DATA INFILE'abc.csv' INTO TABLE abc
this query adds all data to the first column.
此查询将所有数据添加到第一列。
Please suggest auto syntax for importing data to Mysql.
请建议用于将数据导入 Mysql 的自动语法。
回答by Saharsh Shah
You can use LOAD DATA INFILEcommand to import csvfile into table.
您可以使用LOAD DATA INFILE命令将csv文件导入表中。
Check this link MySQL - LOAD DATA INFILE.
检查此链接MySQL-LOAD DATA INFILE。
LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);
For MySQL 8.0 users:
对于 MySQL 8.0 用户：
Using the LOCALkeyword hold security risks and as of MySQL 8.0 the LOCALcapability is set to Falseby default. You might see the error:
使用LOCAL关键字持有安全风险，从 MySQL 8.0 开始，该LOCAL功能False默认设置为。您可能会看到以下错误：
ERROR 1148: The used command is not allowed with this MySQL version
错误 1148：此 MySQL 版本不允许使用的命令
You can overwrite it by following the instructions in the docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.
回答by ckim
You probably need to set the FIELDS TERMINATED BY ','or whatever the delimiter happens to be.
您可能需要设置FIELDS TERMINATED BY ','分隔符或任何恰好是的分隔符。
For a CSV file, your statement should look like this:
对于 CSV 文件，您的语句应如下所示：
LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
回答by Sunny S.M
Before importing the file, you must need to prepare the following:
在导入文件之前，您必须准备以下内容：
- A database table to which the data from the file will be imported.
- A CSV file with data that matches with the number of columns of the table and the type of data in each column.
- The account, which connects to the MySQL database server, has FILE and INSERT privileges.
- 文件中的数据将导入到的数据库表。
- 包含与表的列数和每列中的数据类型匹配的数据的 CSV 文件。
- 连接到 MySQL 数据库服务器的帐户具有 FILE 和 INSERT 权限。
Suppose we have following table :
假设我们有下表：
CREATE TABLE USING FOLLOWING QUERY :
使用以下查询创建表：
CREATE TABLE IF NOT EXISTS `survey` (
`projectId` bigint(20) NOT NULL,
`surveyId` bigint(20) NOT NULL,
`views` bigint(20) NOT NULL,
`dateTime` datetime NOT NULL
);
YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING ATTACHED IMAGE :
您的 CSV 文件必须正确格式化，例如请参见以下附加图像：
If every thing is fine.. Please execute following query to LOAD DATA FROM CSV FILE :
如果一切正常..请执行以下查询以从 CSV FILE 加载数据：
NOTE : Please add absolute path of your CSV file
注意：请添加 CSV 文件的绝对路径
LOAD DATA INFILE '/var/www/csv/data.csv'
INTO TABLE survey
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
If everything has done. you have exported data from CSV to table successfully
如果一切都完成了。您已成功将数据从 CSV 导出到表
回答by Md. Nashir Uddin
Syntax:
句法：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name' INTO TABLE `tbl_name`
CHARACTER SET [CHARACTER SET charset_name]
FIELDS [{FIELDS | COLUMNS}[TERMINATED BY 'string']]
[LINES[TERMINATED BY 'string']]
[IGNORE number {LINES | ROWS}]
See this Example:
请参阅此示例：
LOAD DATA LOCAL INFILE
'E:\wamp\tmp\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
回答by krunal panchal
Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)
1分钟内批量插入超过7000000条记录到数据库中（带计算的超快速查询）
mysqli_query($cons, '
LOAD DATA LOCAL INFILE "'.$file.'"
INTO TABLE tablename
FIELDS TERMINATED by \',\'
LINES TERMINATED BY \'\n\'
IGNORE 1 LINES
(isbn10,isbn13,price,discount,free_stock,report,report_date)
SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
RRP_nl = RRP * 1.44 + 8,
RRP_bl = RRP * 1.44 + 8,
ID = NULL
')or die(mysqli_error());
$affected = (int) (mysqli_affected_rows($cons))-1;
$log->lwrite('Inventory.CSV to database:'. $affected.' record inserted successfully.');
RRP and RRP_nl and RRP_bl is not in csv but we are calculated that and after insert that.
RRP 和 RRP_nl 和 RRP_bl 不在 csv 中，但我们计算出来并在插入之后。
回答by Mohammad Arshi
let suppose you are using xampp and phpmyadmin
假设您正在使用 xampp 和 phpmyadmin
you have file name 'ratings.txt' table name 'ratings' and database name 'movies'
你有文件名 'ratings.txt' 表名 'ratings' 和数据库名 'movies'
if your xampp is installed in "C:\xampp\"
如果您的 xampp 安装在“C:\xampp\”
copy your "ratings.txt" file in "C:\xampp\mysql\data\movies" folder
将“ratings.txt”文件复制到“C:\xampp\mysql\data\movies”文件夹中
LOAD DATA INFILE 'ratings.txt' INTO TABLE ratings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Hope this can help you to omit your error if you are doing this on localhost
如果您在本地主机上执行此操作，希望这可以帮助您忽略错误
回答by Kikoz
If you are running LOAD DATA LOCAL INFILEfrom the windows shell, and you need to use OPTIONALLY ENCLOSED BY '"', you will have to do something like this in order to escape characters properly:
如果您LOAD DATA LOCAL INFILE从 windows shell运行，并且需要使用OPTIONALLY ENCLOSED BY '"'，则必须执行以下操作才能正确转义字符：
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql" -u root --password=%password% -e "LOAD DATA LOCAL INFILE '!file!' INTO TABLE !table! FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"^""' LINES TERMINATED BY '\n' IGNORE 1 LINES" --verbose --show-warnings > mysql_!fname!.out
回答by Hozayfa
You can try to insert like this :
您可以尝试像这样插入：
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
回答by Jonathan Perrotta
By these days (ending 2019) I prefer to use a tool like http://www.convertcsv.com/csv-to-sql.htmI you got a lot of rows you can run partitioned blocks saving user mistakes when csv come from a final user spreadsheet.
到了这些天（到 2019 年结束）我更喜欢使用像http://www.convertcsv.com/csv-to-sql.htm这样的工具我有很多行你可以运行分区块，当 csv 来自最终的用户电子表格。
回答by Carlos Garcia
I was getting Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
我收到错误代码：1290。MySQL 服务器正在使用 --secure-file-priv 选项运行，因此它无法执行此语句
This worked for me on windows 8.1 64 bit using wampserver 3.0.6 64bit.
这在使用 wampserver 3.0.6 64 位的 windows 8.1 64 位上对我有用。
Edited my.ini file from C:\wamp64\bin\mysql\mysql5.7.14
从 C:\wamp64\bin\mysql\mysql5.7.14 编辑 my.ini 文件
Delete entry secure_file_priv c:\wamp64\tmp\ (or whatever dir you have here)
删除条目 secure_file_priv c:\wamp64\tmp\（或您在此处拥有的任何目录）
Stopped everything -exit wamp etc.- and restarted everything; then punt my cvs file on C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur (the last dir being my database name)
停止一切 - 退出 wamp 等 - 并重新启动一切；然后将我的 cvs 文件放在 C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur （最后一个目录是我的数据库名称）
executed LOAD DATA INFILE 'myfile.csv'
执行 LOAD DATA INFILE 'myfile.csv'
INTO TABLE alumnos
INTO TABLE校友
FIELDS TERMINATED BY ','
以 ',' 结尾的字段
ENCLOSED BY '"'
由 '"' 括起来
LINES TERMINATED BY '\r\n'
以 '\r\n' 结尾的行
IGNORE 1 LINES
忽略 1 行
... and VOILA!!!
......和瞧！
