Java利用 LOCAL INFILE 结合easyexcel 实现大批量导入数据到MySQL

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

由于 使用easyexcel 读取excel 导入到mysql 数据库时，使用mybatis新增到数据库，
五千多条数据将耗时需要五分钟不能忍受、故而想到使用mysql load data local infile 实现
新手勿喷

---

Mysql load data的使用，MySQL的LOAD DATAINFILE语句用于高速地从一个文本文件中读取行，并装入一个表中

一、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代码中调用此方法。

二、使用步骤

1.在easyexcel 的 AnalysisEventListener 类中的invoke方法里面将实体类进行相关的转换

2.创建 bulkLoadFromInputStream 方法

代码如下（示例）：

import javax.sql.DataSource;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


 /**

     *

     * load bulk data from InputStream to MySQL

     */
    public int bulkLoadFromInputStream(String loadDataSql,ByteArrayInputStream dataStream) throws Exception {
        if (dataStream == null) {
            log.info("InputStream is null ,No data is imported");

            return 0;
        }

            Class.forName("com.mysql.cj.jdbc.Driver");

        Connection conn=DriverManager.getConnection("jdbc:mysql://ip:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&allowLoadLocalInfile=true","用户名","密码");

        PreparedStatement statement = conn.prepareStatement(loadDataSql);
        int result = 0;

        if (statement.isWrapperFor(com.mysql.cj.jdbc.ClientPreparedStatement.class)) {


            com.mysql.cj.jdbc.ClientPreparedStatement  mysqlStatement = statement.unwrap(com.mysql.cj.jdbc.ClientPreparedStatement.class);

            mysqlStatement.setLocalInfileInputStream(dataStream);

            result = mysqlStatement.executeUpdate();
        }

        return result;
    }

3.调用

 @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
       // saveData();
        try {
            bulkLoadFromInputStream("LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE  jskj_sf_expres_delivery(date, waybill, mailing, mailing, arrival,arrival,other,other,billable,product,payment,cost, discount, amount, handler,increment,origin,destination,point,point,state)",new ByteArrayInputStream(stringBuilder.toString().getBytes()));

        }
        catch (Exception e)
        {
            log.error(e.toString());
        }
       log.info("所有数据解析完成！");
    }

4. 遇到的问题

在此连接过程中遇到 连接问题

 java.sql.SQLSyntaxErrorException: Loading local data is disabled; this must be enabled on both the client and server sides

问题处理：
一定要注意的问题 客户端一定要加上 allowLoadLocalInfile=true mysq的服务端要开启
服务端开启任务

查看 local_infile 的设置是否为off（分步执行下面命令；然后重启mysql）一定要重新启动 (若是重启会把该状态修改为off 需要持久化到 my.ini 文件中，我目前没有重启服务)
查看 local_infile 的状态

SHOW GLOBAL VARIABLES LIKE 'local_infile';

如果是off,使用命令设置为on

SET GLOBAL local_infile = 'ON'; 

SET GLOBAL local_infile = ‘ON’;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

---

总结

使用local infile 确实提高了很大的数据 结合easysxcel 使用体验较好

参考连接
链接:https://blog.youkuaiyun.com/weixin_45761659/article/details/119102399
连接:http://www.zzvips.com/article/155111.html