MYSQL 创建表

create table T_ZST(
  id int(4) NOT NULL AUTO_INCREMENT UNIQUE,
  product_name varchar(50) NOT NULL,
  product_type varchar(50) NOT NULL,
  product_country varchar(50) NOT NULL,
  product_minimum_price  varchar(50),
  product_maximum_price varchar(50),
  product_staple_price varchar(50),
  product_info_from varchar(50) NOT NULL,
  product_business_time varchar(50)
);

 

MYSQL三种插入方式对比:单条插入、LOAD DATA、存储过程

以下为引用的内容：
数据库SQL文件:
/*source F:/mysqlTest/mysqlInsertTest/src/db.sql*/

drop database if exists testInsert;

create database testInsert;

use testInsert;

drop table if exists insertTB;

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


数据库属性:
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");
url = p.getProperty("url");
username = p.getProperty("username");
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

数据源:
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("url:" + DBProperty.url);
System.out.println("username:" + DBProperty.username);
System.out.println("password:" + DBProperty.password);
return DriverManager.getConnection(DBProperty.url,
DBProperty.username, DBProperty.password);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
} 

 

   普通的每条插入:

  

package com.ys.db.init;

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

public class TestInsert1 {

public static void main(String[] args) {
DataSource d = new DataSource();
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秒
} catch (SQLException e) {
e.printStackTrace();
}
}
}
消耗了123秒

 LOAD DATA指令:
首先需要生成数据文件，要100000条，作为程序员的我们怎么可以是复制呢？自己编写呗！

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();
}
}
}
测试类:
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();
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秒
} catch (SQLException e) {
e.printStackTrace();
}
}
}

仅仅需要0秒！

存储过程:
新建存储过程
delimiter //
CREATE PROCEDURE idata()
BEGIN
DECLARE a INT;
SET a = 1;
WHILE a < 100000 DO
INSERT INTO insertTB VALUES(a, 'username');
SET a = a + 1;
END WHILE;
END;//

测试类：
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();
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();
}
}
} 

 也只是使用了0秒

从上可知道，存储过程和LOAD DATA都会比单条语句的插入快的多！

和 Load Data InFile 相反的是
Select * From `TableTest` Into OutFile 'C:/Data_OutFile.txt';

 

注意一点，要生成文本文件的时候，字段之间有一个制表符\t,例如,

"\t6\t5\t1\t1.8\t1.8\t1.8\t7\t9\r\n"