MySQL workbench table data import wizard extermely

问题:

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

回答1:

Always use Load Data Infile as a first attempt for huge sets of data.

Mysql Manual page on Load Data Infile.

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.

回答2:

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 commands
INSERT 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.

回答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.

回答4:

I had a similar issue with MySQL workbench. The alternative I found is 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.

回答5:

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.

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.

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.