http://blog.csdn.net/home_zhang/article/details/50836253
- <span style="font-size:14px;"><span style="font-size:14px;">package com.ddx.zhang;
-
- import java.sql.SQLException;
- import java.util.UUID;
-
- public class JDBCTest {
- public static void main(String[] args) throws SQLException {
- try {
- Class.forName("com.mysql.jdbc.Driver");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
-
- java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "123456");
-
- java.sql.Statement stmt = conn.createStatement();
-
- int total = 10000;
- System.out.println("====start=====");
-
- long start = System.currentTimeMillis();
-
- for (int n = 0; n < 1000; n++) {
- StringBuffer sBuffer = new StringBuffer(" insert into student(user_name) values ");
- for (int i = 0; i < total; i++) {
- String userName = UUID.randomUUID().toString();
- if (i == total - 1) {
- sBuffer.append("('" + userName + "');");
- } else {
- sBuffer.append("('" + userName + "'),");
- }
- }
- System.out.println("第" + n + "次插入1万条数据!");
- stmt.executeUpdate(sBuffer.toString());
- }
-
- long end = System.currentTimeMillis();
-
- System.out.println("run time:" + (end - start));
- stmt.close();
- conn.close();
- }
- }
- </span></span>
运行结束所需要的时间:247828ms,为247.828s=4.13m
将每次插入条数total修改为25000,n<400 ,运行结束所需要的时间为:209958ms,为209.958s=3.4993m
比较之后,选择用每次插入25000条,循环400次的方式插入,运行10次,既可插入1亿条数据。总花费时间也就10几分钟
表的sql脚本如下:
CREATE TABLE `student` (
`id` bigint(20) NOT NULL auto_increment COMMENT '主键',
`user_name` varchar(255) default NULL COMMENT '姓名',
`add_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '添加时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;