MySQL MySQL工作台表数据导入向导速度极慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33296569/
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 workbench table data import wizard extremely slow
提问by BlueStarry
I need to import a csv file with 20 million rows and 2 columns into a database, but when I try to do this with MySQL Workbench's data import wizard it is extremely slow, probably is going to take 1 month to finish, looking at the progress bar.
我需要将一个包含 2000 万行和 2 列的 csv 文件导入数据库，但是当我尝试使用 MySQL Workbench 的数据导入向导执行此操作时，速度非常慢，可能需要 1 个月才能完成，查看进度酒吧。
There has to be some faster way to do this, I hope.
我希望必须有一些更快的方法来做到这一点。
采纳答案by Drew
Always use Load Data Infileas a first attempt for huge sets of data.
始终Load Data Infile用作大量数据集的第一次尝试。
Mysql Manual page on Load Data Infile.
加载数据 Infile上的 Mysql 手册页。
Wrote up several answers for this question, but for a peer comparison, see this guy's question and my Answerand his time comparisons of Workbench vs Load Data Infile.
为这个问题写了几个答案，但要进行同行比较，请参阅此人的问题和我的答案以及他对 Workbench 与 Load Data Infile 的时间比较。
回答by Gaurav Lad
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 SOURCEcommand
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 values(1,2),(1,3),....;
现在使用 MySQL shell 脚本并使用SOURCE命令
mysql> source C:/Users/Desktop/sql scripts/script.sql
与直接导入 CSV 相比，您的数据导入速度更快数以百万计的记录。
回答by malnosna
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/文本文件。
回答by Dilan JIvanji
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 管理员连接到远程 MySQL 服务器需要我 40 分钟。在 MySQL 服务器本身上，上传需要几分钟。使用蟾蜍，我能够连接到远程服务器并在几分钟内上传。我确实尝试过 HeidiSQL，但发现它导入起来并不友好。
回答by adnls
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.
总是更喜欢 load data 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。这是一种丑陋的“黑客”，但对我有用。
