向MySQL插入100w条数据，该怎么做？

使用MySQL存储过程插入

那么从sql层面上我们该怎么插入100万条数据呢，无脑直接写个循环？那就使用存储过程试试看吧

DROP PROCEDURE IF EXISTS proc_insert()
	DELIMITER $
	CREATE PROCEDURE pro_insert2()
 	BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=1000000 DO
    INSERT INTO student VALUES(i,马老师,1970-02-02,广东深圳,60,60,60);
    SET i = i+1;
    END WHILE;
    END $

调用该存储过程后执行时间如下

JDBC往数据库中普通插入方式

同样先来个粗暴的100万次for循环，代码如下

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

/**
 * @author admin
 * @version 1.0.0
 * @ClassName InsertForDemo.java
 * @createTime 2020年12月06日 15:17:58
 */
public class InsertForDemo {
          
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
          
   

        long start = System.currentTimeMillis();
        Connection con = SqlTools.getConnection();
        System.out.println(con);
        String sql = "insert into emp (uid,name,age,hiredate,salary,deptno) values(?,?,?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        for (int i = 0; i < 1000000; i++) {
          
   

            ps.setString(1, SqlTools.getTrimUUID());
            ps.setString(2, SqlTools.getChineseName());
            ps.setInt(3, SqlTools.getNum(18, 60));
            ps.setString(4, SqlTools.getCurrTime());
            ps.setInt(5, SqlTools.getNum(5000, 500000));
            ps.setInt(6, SqlTools.getNum(1, 50));

            ps.execute();

        }
        con.close();
        long end = System.currentTimeMillis();
        System.out.println("插入100w条数据，for循环耗时：" + (end - start) + "ms");

    }
}

执行用时653550ms 在讨论批量处理之前，先说说遇到的坑。MySQL驱动jar包，使用过低版本（5.1.13以下）会导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来…

使用事务批处理提交方式

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

public class InsertDataByTxDemo {
          
   
    //  insert into emp values (uid,name,age,hiredate,salary,deptno)
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
          
   
        Connection con = SqlTools.getConnection();
        String sql = "insert into emp values(?,?,?,?,?,?)";
        try {
          
   
            con.setAutoCommit(false);
            PreparedStatement pst = con.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 10; i++) {
          
   
                long start2 = System.currentTimeMillis();
                // 每一轮插入10万条
                for (int j = 0; j < 100000; j++) {
          
   
                    pst.setString(1, SqlTools.getTrimUUID());
                    pst.setString(2, SqlTools.getChineseName());
                    pst.setInt(3, SqlTools.getNum(18, 60));
                    pst.setString(4, SqlTools.getCurrTime());
                    pst.setInt(5, SqlTools.getNum(5000, 50000));
                    pst.setInt(6, SqlTools.getNum(1, 50));
                    pst.addBatch();
                }
                pst.executeBatch();
                pst.clearBatch();
                con.commit();
                long end2 = System.currentTimeMillis();
                System.out.println("第" + (i + 1) + "轮执行时间：" + (end2 - start2) + "ms");
            }

            long end = System.currentTimeMillis();
            System.out.println("总耗时：" + (end - start) + "ms");//168662ms

        } catch (SQLException e) {
          
   
            e.printStackTrace();
        } finally {
          
   
            con.close();
        }
    }
}

显然批处理事务提交的效果还不错。