快速插入大量数据到MySQL

快速插入大量数据到MySQL解决办法：load data local infile

/**
 * 1. MySQL server 单表最大2000W行数据
 * 2. 打开批量导入功能
 * SHOW GLOBAL VARIABLES LIKE 'local_infile';
 * set global local_infile=1;
 * 3. jdbc:mysql://127.0.0.1:3306/springboot_test?useUnicode=true&characterEncoding=UTF-8&allowLoadLocalInfile=true
 */
@Component
public class BulkLoadStreamToMySQLUtilTest {
    @Autowired
    private BulkLoadStreamToMySQLUtil bulkLoadStreamToMySQLUtil;

    public void testBulkLoadStreamToMySQL() {
        // 1.准备要插入MySQL的数据信息
        String tableName = "test";                                  // 要导入的表名
        String[] columns = new String[]{"id", "name", "age"};       // 导入数据的列名，与数据库列名保持一致
        int number = 10000 * 100;                                   // 一共100w行
        InputStream inputStream = getImportDataStream(number);      // 具体要导入的数据

        // 2.批量导入数据到MySQL
        long startTime = System.currentTimeMillis();
        boolean isSuccess = bulkLoadStreamToMySQLUtil.bulkLoadStreamToMySQL(bulkLoadStreamToMySQLUtil.getDataBaseName(), tableName, columns, inputStream, number);
        long endTime = System.currentTimeMillis();
        if (isSuccess) {
            System.out.println("插入数据成功，所用时间：" + (endTime - startTime) + "毫秒");
        } else {
            System.out.println("插入数据失败");
        }
    }

    // 准备number行测试数据流
    private InputStream getImportDataStream(int number) {
        StringBuilder dataBuilder = new StringBuilder();
        for (int i = 1; i < number + 1; i++) {
            dataBuilder.append(i);              // id
            dataBuilder.append("\t");
            dataBuilder.append("name_" + i);   // name
            dataBuilder.append("\t");
            dataBuilder.append(i);             // age
            dataBuilder.append("\n");
        }
        byte[] bytes = dataBuilder.toString().getBytes();
        InputStream is = new ByteArrayInputStream(bytes);
        return is;
    }
}


/**
 * @Author zhu
 * @Date 2022/9/28 10:23
 * 快速插入大量数据到MySQL解决办法：load data local infile
 * https://blog.csdn.net/black_dawn/article/details/118581810
 */
@Component
@Slf4j
@Data
public class BulkLoadStreamToMySQLUtil {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverName;
    @Value("${database-name}")
    private String dataBaseName;
    private Connection conn = null;

    /**
     * 批量插入到MySQL
     *
     * @param dataBaseName 数据库名称
     * @param tableName    表名
     * @param columns      列名
     * @param is           导入的数据流
     * @param number       导入的数据行数
     * @return boolean     是否全部成功插入
     */
    public boolean bulkLoadStreamToMySQL(String dataBaseName, String tableName, String[] columns, InputStream is, int number) {
        // 1. 构建插入语句
        String insertColumnName = StringUtils.join(columns, ",");
        String sql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE " + dataBaseName + "." + tableName + " (" + insertColumnName + ")";
        System.out.println("sql = " + sql);

        // 2. 插入行数，为了判断是否全部插入
        int effectRows = fastInsertData(sql, is);
        System.out.println("effectRows = " + effectRows);
        return (effectRows == number) ? true : false;
    }

    /**
     * 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL
     * 将数据从输入流加载到MySQL
     * 原理是使用 setLocalInfileInputStream 会忽略 sql.csv 文件名，不从文件读取，直接从输入流读取数据
     *
     * @param sql SQL语句
     * @param is  组装好的数据流
     * @return int  成功插入的行数
     */
    public int fastInsertData(String sql, InputStream is) {
        int result = 0;
        if (is == null) {
            log.info("输入流为NULL，没有数据导入...");
            return result;
        }
        try {
            conn = getConnection();
            PreparedStatement statement = null;
            try {
                statement = conn.prepareStatement(sql);
                // mysql8 使用下面的方式
                if (statement.isWrapperFor(com.mysql.cj.jdbc.JdbcStatement.class)) {
                    com.mysql.cj.jdbc.ClientPreparedStatement mysqlStatement = statement.unwrap(com.mysql.cj.jdbc.ClientPreparedStatement.class);
                    mysqlStatement.setLocalInfileInputStream(is);
                    result = mysqlStatement.executeUpdate();
                }
                // mysql5 使用下面的方式
//            if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
//                com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class);
//                mysqlStatement.setLocalInfileInputStream(dataStream);
//                result = mysqlStatement.executeUpdate();
//            }
            } finally {
                if (statement != null) {
                    statement.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != is) {
                    is.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 获取原生jdbc连接.
     *
     * @return
     */
    private Connection getConnection() {
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return conn;
    }
}