mysql批量插入数据理解_mysql批量insert

最近发现作业中有个批量插入数据库的功能，这里总结一下原生的几种批量插入方式。

insert1 Statement循环插入方式

最慢的一种方式，一条语句对应了一个包，没有批次的效果,每次都要如果数据量不是很大，可以使用，但是每次都要消耗数据库的资源。但若数据多起来这个方法就很难受了，还好我连的本地数据库，如果是连连线上数据库，每次都单独insert，网络传输次数太多了，会更加慢

 public void insertUser1(List<Ex_user> users) throws SQLException {
        db = MysqlDbUtil.getInstance();
        String sql = "insert into Ex_user(name,password)values(?,?)";
        PreparedStatement stmt = (PreparedStatement) db.connection.prepareStatement(sql);
        Iterator<Ex_user> iterator=users.iterator();
        Ex_user user;
        long starttime=System.currentTimeMillis();
        while (iterator.hasNext()) {
            user= iterator.next();
            try {
                stmt.setString(1,user.getName());
                stmt.setString(2, user.getPassword());
                stmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        long endtime=System.currentTimeMillis();
        System.out.println("111times="+(endtime-starttime));
        db.close();//关闭连接
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

insert2 Statement拼接插入方式

因为将要插入数据全部拼接到sql语句后面，一条insert语句即搞定，减少了多次操作数据库。并且连远程数据库，只通过一次网络传输即可完成操作。
缺点是字符串拼接到sql的时候，在遇到稍微复杂一点的表结构，会非常麻烦。

public void insertUser2(List<Ex_user> users) throws SQLException {
        db = MysqlDbUtil.getInstance();
        String sql_rear="";
        Iterator<Ex_user> iterator=users.iterator();
        Ex_user user;
        long starttime=System.currentTimeMillis();
        while (iterator.hasNext()) {
            user= iterator.next();
            if (iterator.hasNext()){
                sql_rear+="(\""+user.getName()+"\",\""+user.getPassword()+"\"),";
            }
            else {
                sql_rear+="(\""+user.getName()+"\",\""+user.getPassword()+"\")";
            }
        }
        String sql = "insert into Ex_user(name,password)values"+sql_rear;
        Statement stmt =  db.connection.createStatement();
        stmt.execute(sql);
        long endtime=System.currentTimeMillis();
        System.out.println("222times="+(endtime-starttime));
        db.close();//关闭连接
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

insert3 PreparedStatement循环插入方式

PreparedStatement比起Statement有很多优势，其中一条就是PreparedStatement比Statement更快，SQL语句会预编译在数据库系统中，执行计划同样会被缓存起来，它允许数据库做参数化查询。
但执行起来速度只比statement循环插入快一点点，原因用的也是循环插入的方式,一条语句对应了一个包，没有批次的效果

 public void insertUser3(List<Ex_user> users) throws SQLException {
        db = MysqlDbUtil.getInstance();
        String sql = "insert into Ex_user(name,password)values";
        Statement stmt = db.connection.createStatement();
        Iterator<Ex_user> iterator=users.iterator();
        Ex_user user;
        long starttime=System.currentTimeMillis();
        while (iterator.hasNext()) {
            user= iterator.next();
            try {
                String tempsql="";
                tempsql=sql+"(\""+user.getName()+"\",\""+user.getPassword()+"\")";
//                System.out.println(tempsql);
                stmt.execute(tempsql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        long endtime=System.currentTimeMillis();
        System.out.println("333times="+(endtime-starttime));
        db.close();//关闭连接
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

insert4 PreparedStatement executeBatch插入方式

PreparedStatement的executeBatch方法原理：每次提交一批命令到数据库中执行，如果所有的命令都成功执行了，可以设置成每1000条，再传送给数据库执行.这样效率最高
这里参考了博客：https://blog.csdn.net/jim_LoveQ/article/details/88823466

 public void insertUser4(List<Ex_user> users) throws SQLException {
        db = MysqlDbUtil.getInstance();
        String sql = "insert into Ex_user(name,password)values(?,?)";
        PreparedStatement stmt = (PreparedStatement) db.connection.prepareStatement(sql);
        Iterator<Ex_user> iterator=users.iterator();
        Ex_user user;
        long starttime=System.currentTimeMillis();
        while (iterator.hasNext()) {
            user= iterator.next();
            try {
                stmt.setString(1,user.getName());
                stmt.setString(2, user.getPassword());
                stmt.addBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        stmt.executeBatch();
        long endtime=System.currentTimeMillis();
        System.out.println("4444times="+(endtime-starttime));
        db.close();//关闭连接
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

最后贴上main函数还有测试数据

总结：

mysql批量插入数据最快的是PreparedStatement executeBatch插入方式的方法，对比四个不同的插入方式，可以得知PreparedStatement比起Statement有很多优势，其中一条就是PreparedStatement比Statement更快，SQL语句会预编译在数据库系统中，执行计划同样会被缓存起来，它允许数据库做参数化查询。executeBatch是将命令按照批次一批批传给数据库执行。
还有一个比较重要的减少耗时睇法还是要减少与数据库的通讯次数从而减少网络连接耗时。