java - 在单个事务中将 50000 条记录存储在 mysql 中的最佳做法是什么

Question

输入集:数千(>10000)个 csv 文件，每个文件包含 >50000 个条目。输出:将这些数据存储在 mysql 数据库中。

采取的方法:读取每个文件并将数据存储到数据库中。下面是相同的代码片段。请建议这种方法是否可行。

    PreparedStatement pstmt2 = null;
try 
{
pstmt1 = con.prepareStatement(sqlQuery);
result = pstmt1.executeUpdate();
con.setAutoCommit(false);
sqlQuery = "insert   into "
        + tableName
        + " (x,y,z,a,b,c) values(?,?,?,?,?,?)";
pstmt2 = con.prepareStatement(sqlQuery);
Path file = Paths.get(filename);

lines = Files.lines(file, StandardCharsets.UTF_8);
final int batchsz = 5000;
for (String line : (Iterable<String>) lines::iterator) {

    pstmt2.setString(1, "somevalue");
    pstmt2.setString(2, "somevalue");
    pstmt2.setString(3, "somevalue");
    pstmt2.setString(4, "somevalue");
    pstmt2.setString(5, "somevalue");
    pstmt2.setString(6, "somevalue");
    pstmt2.addBatch();
    if (++linecnt % batchsz == 0) {
        pstmt2.executeBatch();
    }
}
int batchResult[] = pstmt2.executeBatch();
pstmt2.close();
con.commit();

} catch (BatchUpdateException e) {
    log.error(Utility.dumpExceptionMessage(e));

} catch (IOException ioe) {
    log.error(Utility.dumpExceptionMessage(ioe));
} catch (SQLException e) {
    log.error(Utility.dumpExceptionMessage(e));
} finally {
    lines.close();
    try {
        pstmt1.close();
        pstmt2.close();
    } catch (SQLException e) {
        Utility.dumpExceptionMessage(e);
    }
}

Answer 1

我过去曾在这种情况下使用过 LOAD DATA INFILE。

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 14.2.9.1, “SELECT ... INTO Syntax”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both statements. Both clauses are optional, but FIELDS must precede LINES if both are specified.

IGNORE number LINES 选项可用于忽略文件开头的行。例如，您可以使用 IGNORE 1 LINES 跳过包含列名的初始标题行:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

http://dev.mysql.com/doc/refman/5.7/en/load-data.html