Java不写文件，LOAD DATA LOCAL INFILE大批量导入数据到MySQL的实现

Mysql loaddata的使用

 

        数据库中，最常见的写入数据方式是通过SQLINSERT来写入，另外就是通过备份文件恢复数据库，这种备份文件在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 DEFAULT CHARSET=utf8

Java代码如下：

package com.seven.dbTools.DBTools;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

/**
 * @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,...)]
 
SELECT语法
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

总结

 

LOADDATA是一个很有用的命令，从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。但是命令的选项很多，然而大多都用不到，如果真的需要，用的时候看看官方文档即可。