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",则只能在该目录下操作文件。

以下都是在windows，和linux不同的地方就是换行了,windows采用\r\n,linux采用\n

一、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 NULL AUTO_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代码

package com.wms.test;

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

public class MysqlLoadFile {
    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 void connection() {
        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 void main(String[] args) {
        connection();
    }
}

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

二、使用LOAD DATA INFILE......

注："\N"来指定其为null。不能存在空行，存在就报错。后面有总结

此种情况比较复杂：

数据库表：

CREATE TABLE `test_key_value` (
  `id` bigint(20) NOT NULL AUTO_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;

1、数据带"'"单引号,以","分割,以回车结尾：

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'

java测试代码：Windows下换行符为“\r\n”

package com.wms.test;

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

public class MysqlLoadFile {
    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 void connection() {
        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 void main(String[] args) {
        connection();
    }
}

 这种情况下，要求数据库中不能存在和插入数据相同的主键，有冲突不会自动容错，立即抛出异常，可以通过加入ignore或者replace来解决这个问题。

load data infile 'd:/test/tmp.txt' ignore into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\r\\n';
load data infile 'd:/test/tmp.txt' replace into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\r\\n';

 2、修改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

sql语句：

load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\r\\n';

 此处的enclosed by '' 可以省略。正常插入。

3、继续修改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;

 sql语句：

load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by ';'

此种情况下不管如何修改，sql都报错，望高人指点下最后这个lines terminated by '?'应该怎么填写。 

load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\r;'
load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\n;'
load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by '\\r\\n;'
load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by ';\\r'
load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by ';\\n'
load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by ';\\r\\n;'

以上方式都是错误的。。。。。

=>找到了一个解决方法，就是比较麻烦，要能使用如下这条sql，必须去除文件里的换行,不知道为什么上面的方式写的sql都不能用，还是的大神来指点下啊!

=>采用的思路是从文件里先把文件读取出来，去除文件中的换行，再写入文件，可对同一文件，我这里为了方便，使用了两个文件，原始文件内容不想破坏，构造数据好麻烦。。。。。

load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' enclosed by '' lines terminated by ';'

完整java代码如下

package com.wms.test;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class MysqlLoadFile {
    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 void connection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://" + ip +":" + port + "/mydb";
            Connection conn = DriverManager.getConnection(url, username, password);
            BufferedInputStream bf = new BufferedInputStream(new FileInputStream("d:\\test\\tmp.txt"));
            BufferedWriter bw = new BufferedWriter(new FileWriter(new File("d:\\test\\tmp02.txt")));
            StringBuffer sb = new StringBuffer();
            byte[] b = new byte[100];
            int line = 0;
            while((line=bf.read(b)) != -1) {
                if(line == 100)
                    sb.append(new String(b));
                else
                    sb.append(new String(b,0,line));
            }
            bw.write(sb.toString().replaceAll("\r\n", ""));
            bf.close();
            bw.flush();
            bw.close();
            
            String sql = "load data infile 'd:/test/tmp02.txt' into table test_key_value "
　　　　　　　　　+ "fields terminated by ',' enclosed by '\\'' lines terminated by ';'";            
            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();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }     
    }
    
    public static void main(String[] args) {
        connection();
    }
}

如果使用如下这条sql,需要注意的地方:

load data infile 'd:/test/tmp.txt' into table test_key_value fields terminated by ',' lines terminated by '\\r\\n';

总结下,以上能正常执行sql导入的情况：

　　1、数据必须一一对应，即表字段数量和文件中每条记录的数量必须相同。

　　2、最后一行的记录数可多余表字段数量，但不能少。

　　3、文件中能不能存在空行。

　　4、字段类型必须和表数据类型相同,即可转换。

　　5、主键、非空行数据必须填写，不能空着。

表字段:id(int primary key),name(varchar),age(int),birthday(datetime)

正确数据类型：

1,zhangsan,35,2016-06-08 15:50:30
2,,32,\N
3,,,\N,ddd,dddd #最后一行记录数>=表字段

错误的数据类型：

,lisi,32,\N                        #主键为空
1,ww,66,                           #日期类型不填是必须\N
2,we,44,\N,aa,dd                   #不是最后一行数据记录数不能大于表字段
1,,dd,2016-06-08 15:50:30          #int类型的年龄字段使用字母
                                   #存在空行         

 三、LOAD DATA.....详细使用

 语法如下：

load data [low_priority] [local] infile 'file_name txt' [replace | ignore] 

into table tbl_name 

character set coding

[fields [terminated by '\t'] [OPTIONALLY] enclosed by ''] [escaped by '\' ]] 

[lines terminated by '\n']

[ignore number lines]

[(col_name,.. )]

官方版

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]　　

　　load data infile 语句从一个文本文件中以很高的速度读入一个表中。 使用这个命令之前，mysqld进程（服务）必须已经在运行。 为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用 load data infile ，在服务器主机上你必须有 file 的权限。

1、指定 low_priority 关键字， 那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令： 

load data low_priority infile "/home/mark/data sql" into table Orders;

2、指定 local 关键词，则表明从客户主机读文件。如果 local 没指定，文件必须位于服务器上。

3、replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理。

• replace ，新行将代替有相同的唯一键值的现有行。新行覆盖现有行。
• ignore ，跳过有唯一键的现有行的重复行的输入。新行忽略掉不做处理。
• 不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略(mysql控制台)。 JDBC会使用事物进行控制，有一个主键冲突，整个插入都失败。

load data low_priority infile "/home/mark/data sql" into table Orders;
load data low_priority infile "/home/mark/data sql" replace into table Orders;
load data low_priority infile "/home/mark/data sql" ignore into table Orders;

4、character set coding

　　设置编码

5、 fields 关键字指定了文件字段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项： 

　　terminated by '分隔符',意思是以什么字符作为分隔符,默认情况下是tab字符（\t）

　　enclosed by '字段括起字符',(包裹字段的符号,如'sss',包裹字段的符号就是单引号"'",单引号是mysql中关键字,因此要注意转义)　　

　　escaped by '转义字符',指定数据库使用的转义字符,默认的是反斜杠（backslash：\ ）

load data infile "/home/mark/Orders txt" into table Orders fields terminated by',' enclosed by '\'' escaped by '\';

6、lines 关键字指定了每条记录的分隔符,默认为 '\n' 即为换行符

　　如果两个字段都指定了那 fields 必须在 lines 之前。

　　如果不指定一个 fields子句，缺省值与这样写的相同：fields terminated by '\t' enclosed by '' escaped by '\'

　　如果不指定一个 lines 子句，缺省值与这样写的相同：lines terminated by '\n'

7、ignore number lines

　　忽略从文件第一行开始的number行数据，即从第1行开始到number行的数据不会进行导入。如下忽略文件的前两行。

load data infile "/home/mark/Orders txt" into table Orders fields terminated by',' lines terminated by '\r\n' ignore 2 lines;

8、(col_name,.. )

　　我们可以按指定的列把文件导入到数据库中。 比如文件中导入的数据就会对应到这表的这三个字段下

load data infile "/home/mark/Orders txt" into table Orders fields terminated by',' lines terminated by '\r\n' ('name','age','birthday');