通过命令行将 csv 导入 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6605765/
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
Importing a csv into mysql via command line
提问by de1337ed
I'm trying to import a very large .csv file (~4gb) into mysql. I was considering using phpmyadmin, but then you have a max upload size of 2mb. Someone told me that I have to use the command line.
我正在尝试将一个非常大的 .csv 文件（~4gb）导入 mysql。我正在考虑使用 phpmyadmin，但是您的最大上传大小为 2mb。有人告诉我，我必须使用命令行。
I was going to use these directions to import it: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html#c5680
我打算使用这些说明来导入它：http: //dev.mysql.com/doc/refman/5.0/en/mysqlimport.html#c5680
What would be the command to set the first row in the .csv table as the column names in the mysql table? This option is available through phpmyadmin, so their must be a mysql command line version too, right?. Please help me. Thank you.
将 .csv 表中的第一行设置为 mysql 表中的列名的命令是什么？此选项可通过 phpmyadmin 获得，因此它们也必须是 mysql 命令行版本，对吗？请帮我。谢谢你。
-Raj
-拉吉
回答by Balanivash
Try this command
试试这个命令
load data local infile 'file.csv' into table table
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(column1, column2, column3,...)
The fields here are the actual table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.
此处的字段是数据需要位于的实际表字段。 由和终止的行是可选的，如果您有用双引号括起来的列（例如 Excel 导出等），则可以提供帮助。
For further details check the manual.
有关详细信息，请查看手册。
For setting the first row as the table column names, just ignore the row from being read and add the values in the command.
要将第一行设置为表列名称，只需忽略读取该行并在命令中添加值。
回答by Niels van Adrichem
You could do a
你可以做一个
mysqlimport --columns='head -n 1 $yourfile' --ignore-lines=1 dbname $yourfile`
That is, if your file is comma separated and is not semi-colon separated. Else you might need to sedthrough it too.
也就是说，如果您的文件是逗号分隔的，而不是分号分隔的。否则，您可能也需要sed通过它。
回答by Rahul Arora
try this:
尝试这个：
mysql -uusername -ppassword --local-infile scrapping -e "LOAD DATA LOCAL INFILE 'CSVname.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"
回答by Bjoern
For importing csv with a header row using mysqlimport, just add
要使用 mysqlimport 导入带有标题行的 csv，只需添加
--ignore-lines=N
(ignores the first N lines of the data file)
（忽略数据文件的前N行）
This option is described in the page you've linked.
此选项在您链接的页面中进行了描述。
回答by Aravinthan K
You can simply import by
您可以简单地导入
mysqlimport --ignore-lines=1 --lines-terminated-by='\n' --fields-terminated-by=',' --fields-enclosed-by='"' --verbose --local -uroot -proot db_name csv_import.csv
Note: Csv File name and Table name should be same
注意：Csv 文件名和表名要一致
回答by Daud Mabena
You can put it in the following way:
您可以按以下方式放置它：
LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
回答by user1725872
Another option is to use the csvsql command from the csvkit library.
另一种选择是使用 csvkit 库中的 csvsql 命令。
Example usage directly on command line:
直接在命令行上的示例用法：
csvsql --db mysql:///test --tables yourtable --insert yourfile.csv
This can be executed directly on the command line, or built into a python or shell script for automation if you need to do this for a number of files.
这可以直接在命令行上执行，或者如果您需要对多个文件执行此操作，则可以将其内置到 python 或 shell 脚本中以实现自动化。
csvsql allows you to create database tables on the fly based on the structure of your csv, so it is a lite-code way of getting the first row of your csv to automagically be cast as the MySQL table header.
csvsql 允许您根据 csv 的结构动态创建数据库表，因此它是一种精简代码方式，可以让您的 csv 的第一行自动转换为 MySQL 表头。
Full documentation and further examples here: https://csvkit.readthedocs.io/en/1.0.3/scripts/csvsql.html
完整文档和更多示例：https: //csvkit.readthedocs.io/en/1.0.3/scripts/csvsql.html
回答by Luke Madhanga
I know this says command line, but just a tidbit of something quick to try that might work, if you've got MySQL workbench and the csv isn't too large, you can simply
我知道这说的是命令行，但只是一些快速尝试可能会奏效的花絮，如果你有 MySQL 工作台并且 csv 不是太大，你可以简单地
- SELECT * FROM table
- Copy entire CSV
- Paste csv into the query results section of Workbench
- Hope for the best
- SELECT * FROM 表
- 复制整个 CSV
- 将 csv 粘贴到 Workbench 的查询结果部分
- 希望最好的
I say hope for the best because this is MySQL Workbench. You never know when it's going to explode
我说希望最好，因为这是 MySQL Workbench。你永远不知道什么时候会爆炸
If you want to do this on a remote server, you would do
如果您想在远程服务器上执行此操作，您可以这样做
mysql -h<server|ip> -u<username> -p --local-infile bark -e "LOAD DATA LOCAL INFILE '<filename.csv>' INTO TABLE <table> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"
Note, I didn't put a password after -pas putting one on the command line is considered bad practice
请注意，我没有输入密码，-p因为在命令行上输入密码被认为是不好的做法
