扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
MySQL load data的使用
创新互联建站是一家专注网站建设、网络营销策划、小程序设计、电子商务建设、网络推广、移动互联开发、研究、服务为一体的技术型公司。公司成立十多年以来，已经为数千家湿喷机各业的企业公司提供互联网服务。现在，服务的数千家客户与我们一路同行，见证我们的成长；未来，我们一起分享成功的喜悦。
数据库中，最常见的写入数据方式是通过SQL INSERT来写入，另外就是通过备份文件恢复数据库，这种备份文件在MySQL中是SQL脚本，实际上执行的还是在批量INSERT语句。
在实际中，常常会遇到两类问题：一类是数据导入，比如从word、excel表格或者txt文档导入数据（这些数据一般来自于非技术人员通过OFFICE工具录入的文档）；一类数据交换，比如从MySQL、Oracle、DB2数据库之间的数据交换。
这其中就面临一个问题：数据库SQL脚本有差异，SQL交换比较麻烦。但是几乎所有的数据库都支持文本数据导入(LOAD)导出(EXPORT)功能。利用这一点，就可以解决上面所提到的数据交换和导入问题。
MySQL的LOAD DATAINFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。下面以MySQL5为例说明，说明如何使用MySQL的LOADDATA命令实现文本数据的导入。
注意：这里所说的文本是有一定格式的文本，比如说，文本分行，每行中用相同的符号隔开文本等等。等等，获取这样的文本方法也非常的多，比如可以把word、excel表格保存成文本，或者是一个csv文件。
在项目中，使用的环境是快速上传一个csv文件，原系统中是使用的db2数据库，然后调用了与mysql的loaddata相似的一个函数sysproc.db2load。但是loaddata在mysql的存储过程是不能使用的。采取的方法时在java代码中调用此方法。
实现的例子：
准备测试表
SQL如下：
USE test; CREATE TABLE `test` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` bigint(20) UNSIGNED NOT NULL, `c` bigint(20) UNSIGNED NOT NULL, `d` int(10) UNSIGNED NOT NULL, `e` int(10) UNSIGNED NOT NULL, `f` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`), KEY `a_b` (`a`, `b`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8
Java代码如下：
package com.seven.dbTools.DBTools;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
/**
*
@author seven
*
@since 07.03.2013
*/
public class BulkLoadData2MySQL {
private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);
private JdbcTemplate jdbcTemplate;
private Connection conn = null;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public static InputStream getTestDataInputStream() {
StringBuilder builder = new StringBuilder();
for (int i = 1; i <= 10; i++) {
for (int j = 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();
InputStream is = new ByteArrayInputStream(bytes);
return is;
}
/**
*
* load bulk data from InputStream to MySQL
*/
public int bulkLoadFromInputStream(String loadDataSql,
InputStream dataStream) throws SQLException {
if (dataStream == null) {
logger.info("InputStream is null ,No data is imported");
return 0;
}
conn = jdbcTemplate.getDataSource().getConnection();
PreparedStatement statement = conn.prepareStatement(loadDataSql);
int result = 0;
if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class);
mysqlStatement.setLocalInfileInputStream(dataStream);
result = mysqlStatement.executeUpdate();
}
return result;
}
public static void main(String[] args) {
String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";
InputStream dataStream = getTestDataInputStream();
BulkLoadData2MySQL dao = new BulkLoadData2MySQL();
try {
long beginTime = System.currentTimeMillis();
int rows = dao.bulkLoadFromInputStream(testSql, dataStream);
long endTime = System.currentTimeMillis();
logger.info("importing " + rows +
" rows data into mysql and cost " + (endTime - beginTime) +
" ms!");
} catch (SQLException e) {
e.printStackTrace();
}
System.exit(1);
}
}
提示：
例子中的代码使用setLocalInfileInputStream方法，会直接忽略掉文件名称，而直接将IO流导入到数据库中。在实际的实现中也可以把文件上传到服务器，然后读文件再导入文件，此时load data的local参数应该去掉，并且文件名应该是完整的绝对路径的名字。
最后附上LOAD DATA INFILE语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...]]
总结
LOADDATA是一个很有用的命令，从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。但是命令的选项很多，然而大多都用不到，如果真的需要，用的时候看看官方文档即可。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网