Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL
Mysqlloaddata的使用
数据库中，最常见的写入数据方式是通过SQLINSERT来写入，另外就是通过备份文件恢复数据库，这种备份文件在MySQL中是SQL脚本，实际上执行的还是在批量INSERT语句。
在实际中，常常会遇到两类问题：一类是数据导入，比如从word、excel表格或者txt文档导入数据（这些数据一般来自于非技术人员通过OFFICE工具录入的文档）；一类数据交换，比如从MySQL、Oracle、DB2数据库之间的数据交换。
这其中就面临一个问题：数据库SQL脚本有差异，SQL交换比较麻烦。但是几乎所有的数据库都支持文本数据导入(LOAD)导出(EXPORT)功能。利用这一点，就可以解决上面所提到的数据交换和导入问题。
MySQL的LOADDATAINFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。下面以MySQL5为例说明，说明如何使用MySQL的LOADDATA命令实现文本数据的导入。
注意：这里所说的文本是有一定格式的文本，比如说，文本分行，每行中用相同的符号隔开文本等等。等等，获取这样的文本方法也非常的多，比如可以把word、excel表格保存成文本，或者是一个csv文件。
在项目中，使用的环境是快速上传一个csv文件，原系统中是使用的db2数据库，然后调用了与mysql的loaddata相似的一个函数sysproc.db2load。但是loaddata在mysql的存储过程是不能使用的。采取的方法时在java代码中调用此方法。
实现的例子：
准备测试表
SQL如下：
USEtest; CREATETABLE`test`( `id`int(10)UNSIGNEDNOTNULLAUTO_INCREMENT, `a`int(11)NOTNULL, `b`bigint(20)UNSIGNEDNOTNULL, `c`bigint(20)UNSIGNEDNOTNULL, `d`int(10)UNSIGNEDNOTNULL, `e`int(10)UNSIGNEDNOTNULL, `f`int(10)UNSIGNEDNOTNULL, PRIMARYKEY(`id`), KEY`a_b`(`a`,`b`) )ENGINE=InnoDBAUTO_INCREMENT=1CHARSET=utf8
Java代码如下：
packagecom.seven.dbTools.DBTools;
importorg.apache.log4j.Logger;
importorg.springframework.jdbc.core.JdbcTemplate;
importjava.io.ByteArrayInputStream;
importjava.io.InputStream;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjavax.sql.DataSource;
/**
*
@authorseven
*
@since07.03.2013
*/
publicclassBulkLoadData2MySQL{
privatestaticfinalLoggerlogger=Logger.getLogger(BulkLoadData2MySQL.class);
privateJdbcTemplatejdbcTemplate;
privateConnectionconn=null;
publicvoidsetDataSource(DataSourcedataSource){
this.jdbcTemplate=newJdbcTemplate(dataSource);
}
publicstaticInputStreamgetTestDataInputStream(){
StringBuilderbuilder=newStringBuilder();
for(inti=1;i<=10;i++){
for(intj=0;j<=10000;j++){
builder.append(4);
builder.append("\t");
builder.append(4+1);
builder.append("\t");
builder.append(4+2);
builder.append("\t");
builder.append(4+3);
builder.append("\t");
builder.append(4+4);
builder.append("\t");
builder.append(4+5);
builder.append("\n");
}
}
byte[]bytes=builder.toString().getBytes();
InputStreamis=newByteArrayInputStream(bytes);
returnis;
}
/**
*
*loadbulkdatafromInputStreamtoMySQL
*/
publicintbulkLoadFromInputStream(StringloadDataSql,
InputStreamdataStream)throwsSQLException{
if(dataStream==null){
logger.info("InputStreamisnull,Nodataisimported");
return0;
}
conn=jdbcTemplate.getDataSource().getConnection();
PreparedStatementstatement=conn.prepareStatement(loadDataSql);
intresult=0;
if(statement.isWrapperFor(com.mysql.jdbc.Statement.class)){
com.mysql.jdbc.PreparedStatementmysqlStatement=statement.unwrap(com.mysql.jdbc.PreparedStatement.class);
mysqlStatement.setLocalInfileInputStream(dataStream);
result=mysqlStatement.executeUpdate();
}
returnresult;
}
publicstaticvoidmain(String[]args){
StringtestSql="LOADDATALOCALINFILE'sql.csv'IGNOREINTOTABLEtest.test(a,b,c,d,e,f)";
InputStreamdataStream=getTestDataInputStream();
BulkLoadData2MySQLdao=newBulkLoadData2MySQL();
try{
longbeginTime=System.currentTimeMillis();
introws=dao.bulkLoadFromInputStream(testSql,dataStream);
longendTime=System.currentTimeMillis();
logger.info("importing"+rows+
"rowsdataintomysqlandcost"+(endTime-beginTime)+
"ms!");
}catch(SQLExceptione){
e.printStackTrace();
}
System.exit(1);
}
}
提示：
例子中的代码使用setLocalInfileInputStream方法，会直接忽略掉文件名称，而直接将IO流导入到数据库中。在实际的实现中也可以把文件上传到服务器，然后读文件再导入文件，此时loaddata的local参数应该去掉，并且文件名应该是完整的绝对路径的名字。
最后附上LOADDATAINFILE语法
LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'file_name.txt' [REPLACE|IGNORE] INTOTABLEtbl_name [FIELDS [TERMINATEDBY'string'] [[OPTIONALLY]ENCLOSEDBY'char'] [ESCAPEDBY'char'] ] [LINES [STARTINGBY'string'] [TERMINATEDBY'string'] ] [IGNOREnumberLINES] [(col_name_or_user_var,...)] [SETcol_name=expr,...]]
总结
LOADDATA是一个很有用的命令，从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。但是命令的选项很多，然而大多都用不到，如果真的需要，用的时候看看官方文档即可。