i need to import into a database a csv file with 20 million rows and 2 columns, but when i try to do this with the data import wizard of mysql workbench is extremely slow, probably is going to take 1 month to finish, looking at the progress bar. There has to be some faster way to do this, i hope. Thank you very much
我需要在数据库中导入一个包含2000万行和2列的csv文件，但是当我尝试使用mysql workbench的数据导入向导进行此操作非常慢时，可能需要1个月才能完成，看着进度条。我希望必须有一些更快的方法来做到这一点。非常感谢你
5 个解决方案
#1
9
Always use Load Data Infile as a first attempt for huge sets of data.
始终使用Load Data Infile作为大量数据集的首次尝试。
Mysql Manual page on Load Data Infile.
Load Data Infile上的Mysql手册页。
Wrote up several answers for this question, but for a peer comparison, see this guy's question and my Answer and his time comparisons of Workbench vs Load Data Infile.
为这个问题写了几个答案，但是对于同行比较，看看这个人的问题和我的答案以及他与Workbench和Load Data Infile的时间比较。
#2
3
This is an alternative. Dump your CSV data into sql script, you need to write down some code for this. Basically, your csv data will get converted into similar to below commandsINSERT INTO TABLE_NAME values(1,2),(1,3),....;
now use MySQL shell script and use SOURCE command
mysql> source C:/Users/Desktop/sql scripts/script.sql
your data will get imported faster as compared to direct importing a CSV for millions of record.
这是另一种选择。将您的CSV数据转储到sql脚本中，您需要为此写下一些代码。基本上，您的csv数据将转换为类似于以下命令INSERT INTO TABLE_NAME值（1,2），（1,3），....;现在使用MySQL shell脚本并使用SOURCE命令mysql> source C：/ Users / Desktop / sql scripts / script.sql，与直接导入数百万条记录的CSV相比，您的数据将被更快地导入。
#3
3
If you don't want to write code, I suggest trying another free GUI client like HeidiSQL. It imports CSV/text files much quicker than MySQL Workbench.
如果您不想编写代码，我建议您尝试另一个免费的GUI客户端，如HeidiSQL。它比MySQL Workbench更快地导入CSV /文本文件。
#4
1
I had a similar issue with MySQL workbench. The alternative I found is Toad for MySQL (https://www.toadworld.com/m/freeware/1469)
MySQL工作台遇到了类似的问题。我找到的替代方案是Toad for MySQL（https://www.toadworld.com/m/freeware/1469）
It would take me 40min via MySQL admin, connecting to a remote MySQL sever. On the MySQL server itself the upload takes a few minutes. With toad I am able to connect to the remote server and upload in a few minutes. I did try HeidiSQL, but did not find it as friendly to import.
通过MySQL管理员需要40分钟，连接到远程MySQL服务器。在MySQL服务器上，上传需要几分钟。使用toad，我可以连接到远程服务器并在几分钟内上传。我确实尝试过HeidiSQL，但没有发现导入友好。
#5
1
Always prefer load data infile for importing datasets, the inconvenient is => you have to create a table structure before importing. Import wizard allows you to create on the fly a new table directly from csv or json.
总是更喜欢加载数据infile来导入数据集，不方便的是=>你必须在导入之前创建表结构。导入向导允许您直接从csv或json创建新表。
I think the reason of this slowlyness is : workbench uses python for the import wizard. You can see that in the log of import wizard when an error occurs, it's python's console log.
我认为这种缓慢的原因是：workbench使用python作为导入向导。您可以在导入向导日志中看到错误发生时，它是python的控制台日志。
If you don't wan't to create the structure for any reasons, you can start the process, it will create the table from the csv, then kill the process. Then, delete everything from your table and load data infile. It's a kind of ugly "hack" but it worked for me.
如果由于任何原因不想创建结构，可以启动该过程，它将从csv创建表，然后终止该过程。然后，从表中删除所有内容并加载数据infile。这是一种丑陋的“黑客”，但它对我有用。
#1
9
Always use Load Data Infile as a first attempt for huge sets of data.
始终使用Load Data Infile作为大量数据集的首次尝试。
Mysql Manual page on Load Data Infile.
Load Data Infile上的Mysql手册页。
Wrote up several answers for this question, but for a peer comparison, see this guy's question and my Answer and his time comparisons of Workbench vs Load Data Infile.
为这个问题写了几个答案，但是对于同行比较，看看这个人的问题和我的答案以及他与Workbench和Load Data Infile的时间比较。
#2
3
This is an alternative. Dump your CSV data into sql script, you need to write down some code for this. Basically, your csv data will get converted into similar to below commandsINSERT INTO TABLE_NAME values(1,2),(1,3),....;
now use MySQL shell script and use SOURCE command
mysql> source C:/Users/Desktop/sql scripts/script.sql
your data will get imported faster as compared to direct importing a CSV for millions of record.
这是另一种选择。将您的CSV数据转储到sql脚本中，您需要为此写下一些代码。基本上，您的csv数据将转换为类似于以下命令INSERT INTO TABLE_NAME值（1,2），（1,3），....;现在使用MySQL shell脚本并使用SOURCE命令mysql> source C：/ Users / Desktop / sql scripts / script.sql，与直接导入数百万条记录的CSV相比，您的数据将被更快地导入。
#3
3
If you don't want to write code, I suggest trying another free GUI client like HeidiSQL. It imports CSV/text files much quicker than MySQL Workbench.
如果您不想编写代码，我建议您尝试另一个免费的GUI客户端，如HeidiSQL。它比MySQL Workbench更快地导入CSV /文本文件。
#4
1
I had a similar issue with MySQL workbench. The alternative I found is Toad for MySQL (https://www.toadworld.com/m/freeware/1469)
MySQL工作台遇到了类似的问题。我找到的替代方案是Toad for MySQL（https://www.toadworld.com/m/freeware/1469）
It would take me 40min via MySQL admin, connecting to a remote MySQL sever. On the MySQL server itself the upload takes a few minutes. With toad I am able to connect to the remote server and upload in a few minutes. I did try HeidiSQL, but did not find it as friendly to import.
通过MySQL管理员需要40分钟，连接到远程MySQL服务器。在MySQL服务器上，上传需要几分钟。使用toad，我可以连接到远程服务器并在几分钟内上传。我确实尝试过HeidiSQL，但没有发现导入友好。
#5
1
Always prefer load data infile for importing datasets, the inconvenient is => you have to create a table structure before importing. Import wizard allows you to create on the fly a new table directly from csv or json.
总是更喜欢加载数据infile来导入数据集，不方便的是=>你必须在导入之前创建表结构。导入向导允许您直接从csv或json创建新表。
I think the reason of this slowlyness is : workbench uses python for the import wizard. You can see that in the log of import wizard when an error occurs, it's python's console log.
我认为这种缓慢的原因是：workbench使用python作为导入向导。您可以在导入向导日志中看到错误发生时，它是python的控制台日志。
If you don't wan't to create the structure for any reasons, you can start the process, it will create the table from the csv, then kill the process. Then, delete everything from your table and load data infile. It's a kind of ugly "hack" but it worked for me.
如果由于任何原因不想创建结构，可以启动该过程，它将从csv创建表，然后终止该过程。然后，从表中删除所有内容并加载数据infile。这是一种丑陋的“黑客”，但它对我有用。