MySql 如何急速插入百万数据 批量插入 Statement PreparedStatement

方法一：Statement使用如下

import java.sql.*;

public class InsertMoreStatement {

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3308/weqqq?useSSL=false&serverTimezone=UTC&useLocalSessionState=true";


    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "root";

    public static void main(String[] args) {
        Connection con = null;
        try {
            con = DriverManager.getConnection(DB_URL,USER,PASS);

            Statement statement = (Statement)con.createStatement();

            con.setAutoCommit(false);
            long startTime = System.currentTimeMillis();
            System.out.println("开始……");
            for (int i=0;i<1000000;i++)
            {
                statement.execute(String.format("INSERT INTO `pi_liang_insert`(value) VALUES ( '%s');" , "lt_"+ i));
            }
            long endTime  = System.currentTimeMillis();
            con.commit();
            System.out.println("用时：" + (endTime-startTime));
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

    }

}

调用结果

用时接近4分钟

我们可以查看日志。其过程为将插入命令调用100万次

注意 url里的  useLocalSessionState=true，该参数设置为true可提高很多速率，否则每条插入语句前都会有一句状态确认的查询，如下图

因为以上方法效率赶人，故在批量插入时并不推荐

---

方法二PreparedStatement：

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

public class InsertMorePreparedStatement {

    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
//    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//    static final String DB_URL = "jdbc:mysql://localhost:3308/RUNOOB";

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3308/weqqq?useSSL=false&serverTimezone=UTC&useLocalSessionState=true&rewriteBatchedStatements=true";


    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "root";
    static String sql = "INSERT INTO `pi_liang_insert`(value) VALUES ( ?)";

    public static void main(String[] args) {
        Connection con = null;
        try {
            con = DriverManager.getConnection(DB_URL,USER,PASS);

            PreparedStatement preparedStatement = con.prepareStatement(sql);

            con.setAutoCommit(false);
            long startTime = System.currentTimeMillis();
            System.out.println("开始……");
//            for (int i=0;i<100000;i++)
//            {
//                preparedStatement.setString(1,"asfsdfsdf"+i);
//                preparedStatement.addBatch();
//            }
//            preparedStatement.executeBatch();
            for (int j=0;j<100;j++)//防止最大命令字符数溢出
            {
                for (int i=0;i<10000;i++)
                {
                    preparedStatement.setString(1,"lt_"+(i+j*10000));
                    preparedStatement.addBatch();
                }
                preparedStatement.executeBatch();
            }
            long endTime  = System.currentTimeMillis();
            con.commit();
            System.out.println("用时：" + (endTime-startTime) + "毫秒");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        System.exit(0);
    }

}

调用结果如下：

百万数据插入仅需要6.8秒。

我们来看下日志

插入命令变为 批量插入语法：

INSERT INTO `pi_liang_insert`(value) VALUES ( 'xxxxxxx'),('xxxxxxxx')………………

不再逐条插入，故速率提升。

在这个里我分100次，一次提交10000条数据，主要是因为，命令的字节包有长度限制。如果字节包长度过大，可修改数据库里的参数

查看max_allowed_packet：

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 15728640 |
+--------------------+----------+

修改max_allowed_packet：

mysql> set global max_allowed_packet=1024*10;

 

注意rewriteBatchedStatements必须设置为ture,否则依然是逐条插入