MYSQL three insert contrast: a single insertion the Load DATA, storage process

Such as reprint, please indicate:
Today, MYSQL for bulk data insert a test focusing on learning. Ordinary each insert, the LOAD DATA instructions, call a stored procedure in three ways contrast

The use of the time, to tell you inserted in different ways, the efficiency is greatly different.Now based on 100,000 data, for example.

Database SQL file:
/ * Source F :/ mysqlTest / mysqlInsertTest / src / db.sql * /

drop database if exists testInsert;

The create database testInsert;

use testInsert;

drop table if exists insertTB;

create table insertTB (id int primary key, username varchar (20));

Database properties:
The package com.ys.db.init;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public abstract class DBProperty {

private static String FILE_NAME = “db.properties”;

static {
InputStream in = DBProperty.class.getResourceAsStream (FILE_NAME);
Properties p = new Properties ();
try {
p.load (in);
driverClass = p.getProperty (“driverClass”);
to url = p.getProperty (“url”);
the username = p.getProperty (“username”);
the password = p.getProperty (“password”);
} Catch (IOException e) {
e.printStackTrace ();
}
}

public static String driverClass = “com.mysql.jdbc.Driver”;
public static String url = “jdbc: mysql :/ / localhost: 3306/testInsert”;
public static String username = “root”;
public static String password = “wq3892961”;
}

driverClass = com.mysql.jdbc.Driver
url = jdbc: mysql :/ / localhost: 3306/testInsert
username = root
password = wq3892961

Data source:
The package com.ys.db.init;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.ys.db.init.DBProperty;

public class DataSource {

Protected DataSource () {
try {
System.out.println (“driverClass:” + DBProperty.driverClass);
Class.forName (DBProperty.driverClass);
} Catch (ClassNotFoundException e) {
e.printStackTrace ();
}
}

public Connection getConnection () {
try {
System.out.println (: + DBProperty.url);
System.out.println (username: DBProperty.username);
The System.out.println (password: DBProperty.password);
return DriverManager.getConnection (DBProperty.url,
DBProperty.username, DBProperty.password);
} Catch (SQLException e) {
e.printStackTrace ();
return null;
}
}
}

The ordinary each insert:
The package com.ys.db.init;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public the class TestInsert1 {

public static void main (String [] args) {
DataSource d = new DataSource ();
The Connection Connection = d.getConnection ();
try {
Statement createStatement = connection.createStatement ();
long start = System.currentTimeMillis ();
for (long i = 0; i <100000; i + +) {
createStatement.execute (“insert into insertTB values ??(” + i
+ “, ‘Username’)”);
}
long end = System.currentTimeMillis ();
System.out.println ((end – start) / 1000);
createStatement.close ();
connection.close ();
/ / 123 sec.
} Catch (SQLException e) {
e.printStackTrace ();
}
}
}
Consumption of 123 seconds

LOAD DATA command:
First need to generate data files, to 100,000, as a programmer, how can we copy it? Write your own pictures!
The package com.ys.db.init;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class GInert {

public static void main (String args []) {
try {
File f = new File (“. / Src / i.sql”);
if (! f.exists ()) {
f.createNewFile ();
}
FileOutputStream out = new FileOutputStream (f, true);
for (long i = 0; i <100000; i + +) {
out
. Write ((i + “/ tusername / r / n”)
GetBytes ());
}
out.close ();
} Catch (FileNotFoundException e) {
e.printStackTrace ();
} Catch (IOException e) {
e.printStackTrace ();
}
}
}
Test categories:
The package com.ys.db.init;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert2 {

public static void main (String [] args) {
DataSource d = new DataSource ();
The Connection Connection = d.getConnection ();
Statement createStatement = null;
try {
createStatement = connection.createStatement ();
String sql = “Load Data InFile ‘F :/ mysqlTest / mysqlInsertTest / src / i.sql’ Into Table

`InsertTB`;
long start = System.currentTimeMillis ();
createStatement.execute (sql);
long end = System.currentTimeMillis ();
System.out.println ((end – start) / 1000);
createStatement.close ();
connection.close ();
/ / 0 seconds
} Catch (SQLException e) {
e.printStackTrace ();
}
}
}

Only 0 seconds!

Stored procedure:
New Stored Procedure
delimiter / /
CREATE PROCEDURE idata ()
BEGIN
DECLARE a INT;
Set a = 1;
WHILE a <100000 DO
INSERT INTO insertTB VALUES (‘username’);
Set a = a + 1;
END WHILE;
END ;/ /

Test categories:
The package com.ys.db.init;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert3 {

public static void main (String [] args) {
DataSource d = new DataSource ();
The Connection Connection = d.getConnection ();
Statement createStatement = null;
try {
createStatement = connection.createStatement ();
String sql = “call idata () ;/ /”;
long start = System.currentTimeMillis ();
createStatement.execute (sql);
long end = System.currentTimeMillis ();
System.out.println ((end – start) / 1000);
createStatement.close ();
connection.close ();
/ /
} Catch (SQLException e) {
e.printStackTrace ();
}
}
}

Only 0 seconds

Know from the last stored procedures and LOAD DATA will be faster than the insertion of a single statement!