MySQL LOAD DATA INFILE 高效批量導入：效能優勢與 Java 整合實戰

MySQL LOAD DATA INFILE 高效批量導入實戰

效能優勢

速度比 INSERT 快 20 倍以上，適用於百萬級以上資料匯入場景。

核心語法

LOAD DATA INFILE 'E://baidu//cp9.csv'
REPLACE INTO TABLE baidu_34000_0_competition
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

參數詳解

Java JDBC 整合範例

package com.mysql.csv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Competition {
    static Connection conn = null;
    static String driver = "com.mysql.jdbc.Driver";
    static String url = "jdbc:mysql://127.0.0.1:3306/nerd_1";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        conn = DriverManager.getConnection(url, "root", "1234");
        
        String sql = "LOAD DATA INFILE 'E://baidu//cp9.csv' " +
                     "REPLACE INTO TABLE baidu_34000_0_competition " +
                     "CHARACTER SET utf8 " +
                     "FIELDS TERMINATED BY ',' " +
                     "ENCLOSED BY '\"' " +
                     "LINES TERMINATED BY '\r\n' " +
                     "IGNORE 1 LINES";
        
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.execute();
        conn.close();
    }
}

關鍵前置條件與坑點

1. secure_file_priv：MySQL 5.6+ 限制匯入目錄

   SHOW VARIABLES LIKE 'secure_file_priv';
   -- 結果為 NULL 表示禁用，需配置 my.cnf: secure_file_priv = '' 並重啟

2. 檔案權限：Server 進程需有檔案讀取權限 (非 LOCAL 模式)
3. LOCAL 模式：加上 LOCAL 關鍵字由 Client 讀取檔案，需 JDBC 連線參數 allowLoadLocalInfile=true
4. max_allowed_packet：大檔案需調大此參數

官方文件參考

MySQL 5.1 LOAD DATA INFILE 官方語法文件