快速将数据导入Java中的mysql [英] Fast import data to mysql in Java

问题描述

通过Java代码从CSV文件向MySQL数据库插入大约500.000条记录需要多长时间?数据库托管在localhost上.

How long will take insert about 500.000 records from CSV file to MySQL database by Java code? Database hosted on localhost.

表结构:AI id, | varchar(8) | datetime | int | varchar(2).我的代码需要在40分钟内插入70.000条记录.有什么办法可以更快地做到这一点? 这是我的代码的主要部分:

Table structure: AI id, | varchar(8) | datetime | int | varchar(2). My code need to insert 70.000 records over 40 minutes. Is there any way to do it faster? Here is the main part of my code:

CsvReader pro

ducts = new CsvReader(path);
products.readHeaders();
stmt = con.createStatement();
String updateString = "INSERT INTO table (T_V1, date, T_V2, T_V3) VALUES (?,?,?,?)";
PreparedStatement preparedStatement = con.prepareStatement(updateString);

            while (products.readRecord()) {
                v1= products.get("V1");
                date = format.parse(products.get("Date") + " " + products.get("Hour"));
                java.sql.Date dateDB = new java.sql.Date(data.getTime());
                v2 = products.get("V2");
                v3 = products.get("V3");



                preparedStatement.setString(1, v1);
                preparedStatement.setDate(2,dateDB);
                preparedStatement.setInt(3, Integer.parseInt(v2));
                preparedStatement.setString(4, v3);   
                preparedStatement.executeUpdate();
            }

根据您的建议，我将语句的创建移出了循环.在我有29 rps之后，现在我每秒有33条记录.

According to your advice I moved creation of the statement out of the loop. Now I have 33 records per second, after I had 29 rps.

推荐答案

我可能会选择使用MySQL的LOAD DATA语句而不是Java:

I might opt for using the LOAD DATA statement from MySQL instead of using Java:

LOAD DATA LOCAL INFILE '/path/to/your/file.csv' INTO TABLE table;

假设您在将每一行插入MySQL之前都在进行处理，这将避免当前的大量开销.

This would avoid a lot of the overhead you currently have, assuming you are processing each line before inserting it into MySQL.

您可以使用原始JDBC从Java执行LOAD DATA语句.

You can execute a LOAD DATA statement from Java using raw JDBC.

这篇关于快速将数据导入Java中的mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！