load data infile mysql_JDBC使用MYSQL的LOAD DATA LOACAL INFILE和LOAD DATA INFILE

标签：

MYSQL的LOAD方法都必须建立在mysql服务允许使用该命令的情况下：

开启该命令的方法：

1、在实例对应的my.cnf(windows为my.ini)中添加一行local-infile=1(默认好像是开启的),改为0即关闭,LOAD方式禁用。

2、加参数local-infile=1(默认是开启的,除非my.cnf中配置了禁止使用load才这样启动)启动mysql,"/usr/local/mysql/bin/mysqld_safe --user=mysql --local-infile=1 &"

注：当数据库实例(my.cnf)指定了secure-file-priv="D:/MySQL/Uploads",则只能在该目录下操作文件

一、LOAD DATA LOCAL INFILE......

测试文件tmp01.txt内容：

6,KEY01,Value01,2012-06-08 15:50:30,fssd;

7,KEY02,Value02,2012-06-08 15:50:30,2012-06-08 16:50:30;

8,KEY03,Value03,2012-06-08 15:50:30,232;

9,KEY04,Value04,2012-06-08 15:50:30,ve4;

10,KEY05,Value05,2012-06-08 15:50:30,444f;

数据库表

CREATE TABLE`t_emp` (

`id`int(10) NOT NULLAUTO_INCREMENT,

`name`varchar(100) DEFAULT NULL,

`sex`varchar(10) DEFAULT NULL,

`salary`double DEFAULT NULL,

`dep_id`int(10) DEFAULT NULL,

`dep_date`datetime DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;

测试java代码

packagecom.wms.test;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;public classMysqlLoadFile {private static String username = "root";private static String ip = "192.168.1.106";private static String password = "mysql";private static String port = "3306";public static voidconnection() {try{

Class.forName("com.mysql.jdbc.Driver");

String url= "jdbc:mysql://" + ip +":" + port + "/mydb";

Connection conn=DriverManager.getConnection(url, username, password);

String sql= "LOAD DATA LOCAL INFILE ‘d:/test/tmp01.txt‘ INTO TABLE mydb.t_emp FIELDS TERMINATED BY ‘,‘ LINES TERMINATED BY ‘;‘";

PreparedStatement pstmt=conn.prepareStatement(sql);

pstmt.execute();

pstmt.close();

conn.close();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}

}public static voidmain(String[] args) {

connection();

}

}

使用LOCAL会自动进行容错操作，即使数据存在很大问题，如上，数据库也能导入数据。

2、使用LOAD DATA INFILE......

此种情况比较复杂：

tmp.txt文件内容：

‘6‘,‘KEY01‘,‘Value01‘,‘2012-06-08 15:50:30‘,‘2012-06-08 16:50:30‘

‘7‘,‘KEY02‘,‘Value02‘,‘2012-06-08 15:50:30‘,‘2012-06-08 16:50:30‘

‘8‘,‘KEY03‘,‘Value03‘,‘2012-06-08 15:50:30‘,‘2012-06-08 16:50:30‘

‘9‘,‘KEY04‘,‘Value04‘,‘2012-06-08 15:50:30‘,‘2012-06-08 16:50:30‘

‘10‘,‘KEY05‘,‘Value05‘,‘2012-06-08 15:50:30‘,‘2012-06-08 16:50:30‘

此种是标准数据。

数据库表：

CREATE TABLE`test_key_value` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`key` varchar(32) CHARACTER SET latin1 DEFAULT NULL,

`value`varchar(128) CHARACTER SET latin1 DEFAULT NULL,

`gmt_create`timestamp NULL DEFAULT NULL,

`gmt_modify`timestamp NULL DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk;

java测试代码：

packagecom.wms.test;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;public classMysqlLoadFile {private static String username = "root";private static String ip = "192.168.1.106";private static String password = "mysql";private static String port = "3306";public static voidconnection() {try{

Class.forName("com.mysql.jdbc.Driver");

String url= "jdbc:mysql://" + ip +":" + port + "/mydb";

Connection conn=DriverManager.getConnection(url, username, password);

String sql= "load data infile ‘d:/test/tmp.txt‘ into table test_key_value fields terminated by ‘,‘ enclosed by ‘\\‘‘ lines terminated by ‘\\r\\n‘";

System.out.println(sql);

PreparedStatement pstmt=conn.prepareStatement(sql);

pstmt.execute();

pstmt.close();

conn.close();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}

}public static voidmain(String[] args) {

connection();

}

}

标签：