欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL

程序员文章站 2022-07-28 15:26:21
mysql load data的使用 数据库中,最常见的写入数据方式是通过sql insert来写入,另外就是通过备份文件恢复数据库,这种备份文件在mysql中是sql脚...

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倍左右。但是命令的选项很多,然而大多都用不到,如果真的需要,用的时候看看官方文档即可。