Today, I saw a blog post on the Internet. The title is: insert 10 million pieces of data into mysql database in 4 minutes. I think it's very interesting, so I record it for myself to learn.
MySQL version: mysql-5.7.22-winx64

1. Set the capacity of MySQL database tables

The default capacity of the database table is 4M. If the stored data exceeds the limit, an error will be reported.
Enter MySql-uroot-p in the windows console, enter the MySql database, and enter set global max_allowed_packet = 100*1024*1024; pay attention to the semicolon at the back.

2. Main code

    public static void main(String[] args) {
        final String driver = "com.mysql.jdbc.Driver";
        final String url = "jdbc:mysql://localhost:3306/project";
        final String user = "root";
        final String password = "253432";
        Connection conn = null;
        PreparedStatement pst =  null;
        long beginTime = 0;
        long endTime = 0;
        try {
            Class.forName(driver);//Specify connection type
            conn = DriverManager.getConnection(url, user, password);
            if(conn != null) {
                System.out.println("Get connection successful");
                beginTime = new Date().getTime();//Start timing
                String sqlPrefix = "insert into test (id,num) values ";
                // Save sql suffix
                StringBuffer suffix = new StringBuffer();
                // Set transaction to non auto commit
                conn.setAutoCommit(false);
                // pst is better than st
                pst = (PreparedStatement) conn.prepareStatement("");//Prepare to execute statement
                // Outer loop, total number of transactions committed
                for (int i = 1; i <= 100; i++) {
                    suffix = new StringBuffer();
                    // j submission step
                    for (int j = 1; j <= 100000; j++) {
                        // Build SQL suffix
                        suffix.append("('"+ UUID.randomUUID().toString()+"','"+i*j+"'"+"),");
                    }
                    // Build full SQL
                    String sql = sqlPrefix + suffix.substring(0, suffix.length() - 1);
                    // Add execution SQL
                    pst.addBatch(sql);
                    // Execution operation
                    pst.executeBatch();
                    // Submission of affairs
                    conn.commit();
                    // Clear last added data
                    suffix = new StringBuffer();
                }
                endTime = new Date().getTime();//Start timing
            }else {
                System.out.println("Database connection failed");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("com.mysql.jdbc.Driver Driver class not found");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Database address error");
        }finally {//Release resources
            System.out.println("Insert successful, all times:"+ (endTime-beginTime));
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
    }

3. Operation results

You can see it took two minutes