MySQL使用Java从文件插入大型数据集 [英] MySQL Inserting large data sets from file with Java
问题描述
我需要从CSV文件中将大约180万行插入MySQL数据库。 （只有一个表）
I need to insert about 1.8 million rows from a CSV file into a MySQL database. (only one table)
目前使用Java来解析文件并插入每一行。
Currently using Java to parse through the file and insert each line.
您可以想象这需要花费几个小时才能运行。 （10）
As you can imagine this takes quite a few hours to run. (10 roughtly)
我之所以没有将它直接从文件传输到数据库中，是因为在将数据添加到数据库之前必须对其进行操作。
The reason I'm not piping it straight in from the file into the db, is the data has to be manipulated before it adds it to the database.
此过程需要由IT经理在那里运行。所以我把它设置为一个很好的批处理文件，让它们在将新的csv文件放到正确的位置后运行。所以，我需要通过将文件放到某个位置并运行批处理文件来很好地完成这项工作。 （Windows环境）
This process needs to be run by an IT manager in there. So I've set it up as a nice batch file for them to run after they drop the new csv file into the right location. So, I need to make this work nicely by droping the file into a certain location and running a batch file. (Windows enviroment)
我的问题是，插入这么多数据的最快方法是什么？大型插入，来自临时解析文件或一次插入一次？可能还有其他想法吗？
My question is, what way would be the fastest way to insert this much data; large inserts, from a temp parsed file or one insert at a time? some other idea possibly?
第二个问题是，如何优化我的MySQL安装以允许非常快速的插入。 （还会有一个点需要大量选择所有数据）
The second question is, how can I optimize my MySQL installation to allow very quick inserts. (there will be a point where a large select of all the data is required as well)
注意：表最终会被删除，整个过程会再次运行稍后日期。
Note: the table will be eventually droped and the whole process run again at a later date.
一些澄清：
目前正在使用... opencsv.CSVReader来解析文件然后在每一行上插入。我正在总结一些专栏并忽略其他专栏。
Some clarification: currently using ...opencsv.CSVReader to parse the file then doing an insert on each line. I'm concating some columns though and ignoring others.
更多说明：
本地数据库
MyISAM表
More clarification: Local DB MyISAM table
推荐答案
快速插入提示：
- 使用 LOAD DATA INFILE 语法让MySQL解析并插入它，即使你必须修改它并在操作后提供它。
-
使用此插入语法：
- Use the LOAD DATA INFILE syntax to let MySQL parse it and insert it, even if you have to mangle it and feed it after the manipulation.
Use this insert syntax:
插入表格（col1 ，col2）值（val1，val2），（val3，val4），...
insert into table (col1, col2) values (val1, val2), (val3, val4), ...
在插入之前删除所有键/索引。
Remove all keys/indexes prior to insertion.
这篇关于MySQL使用Java从文件插入大型数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
