3分钟左右的时间如何向MySQL数据库中插入100万条数据

一、编写测试案例向MySQL数据库中插入百万条数据。测试数据表建表脚本如下：

use db_xk;

droptableifexists tb_test2;

createtable tb_test2 (

       id intprimarykey auto_increment,

       subject varchar(50)notnull,

       description varchar(200)notnull,

       teacher_id int(10) zerofillnotnull,

       student_id int(10) zerofilldefaultnull,

       statebooleannotnulldefaultfalse

);

      测试案例源码如下：

package test;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

 

import util.DBUtil;

 

publicclass TestDataBase2 {

 

   publicstaticvoid main(String[] args) {

 

      Connection conn = DBUtil.getConnection();

      String sql = "insert into tb_test2(subject, description, teacher_id, student_id) values (?,?,?,?)";

 

      try {

          PreparedStatement prep = conn.prepareStatement(sql);

          

          //将连接的自动提交关闭，数据在传送到数据库的过程中相当耗时

          conn.setAutoCommit(false);

          long start = System.currentTimeMillis();

          

          for (int i = 0; i < 10; i++) {

             

             long start2 = System.currentTimeMillis();

             

             //一次性执行插入10万条数据

             for (int j = 0; j < 100000; j++) {

                 

                 prep.setString(1, "test2");

                 prep.setString(2, "test3");

                 prep.setInt(3, 1234562);

                 prep.setInt(4, 12354545);

                 

                 //将预处理添加到批中

                 prep.addBatch();

                 

             }

             

             //预处理批量执行

             prep.executeBatch();

             prep.clearBatch();

             conn.commit();

             

             long end2 = System.currentTimeMillis();

             

             //批量执行一次批量打印执行依次的时间

             System.out.print("inner"+i+": ");

             System.out.println(end2 - start2);

 

          }

          

          long end = System.currentTimeMillis();

          System.out.print("total: ");

          System.out.println(end - start);

          

      } catch (SQLException e) {

 

          e.printStackTrace();

      } finally {

 

          DBUtil.close(conn);

      }

      

   }

 

}