MySql LOAD DATA INFILE

LOAD DATA INFILE 语句以非常高的速度从文本文件中读取行到表中。

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

eg：LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

 

因为数据是从第三方获取到的，需要做一些简单的数据转换到我们的数据库中。所以写了一个java接口来读取文件、数据转换、load data

    
/**
*  接受文件绝对地址 和 数据库表名（因为所有数据都是同一库，所以没有新增库名参数）
*/
public String addDataLoadMysql(String filePath, String tableName) {
        StopWatch stopWatch = new StopWatch();//计时
        stopWatch.start();
        StringBuilder sb = new StringBuilder();
        List<String> data = FileUtil.readFile(filePath);
        sb = tableFieldToLoadUtil.LoadData(sb,tableName,data);//数据转换、拼接处理
        if(null == sb){
            LOG.error("null text: {}", data);
            return null;
        }
        String sql = loadDataInFileUtils.assembleSql("chpp_v2", tableName, TableFieldToLoadUtil.TABLEFIELDS.get(tableName));
        int insertRow = loadDataInFileUtils.fastInsertData(sql, sb);
        System.out.println("insert应收报表数量insertRow："+insertRow);
        stopWatch.stop();
        System.out.println("花费时间" + stopWatch.getNanoTime());
        System.out.println("---------方法执行结束--------------");
        return "success";
    }

    private  StringBuilder LoadData(StringBuilder sb, List<String> data){
        for (String rowDate : data) {
            String[] sArray = rowDate.split("\\|");
            for(int i = 0; i < sArray.length - 1; i++) {
                loadDataInFileUtils.builderAppend(sb, sArray[i]); //这里可以对字段进行处理
            }
            loadDataInFileUtils.builderEnd(sb, sArray[sArray.length-1]);
        }
        return sb;
    }

    public String assembleSql(String dataBaseName, String tableName, String columnName[]) {
        String insertColumnName = StringUtils.join(columnName, ",");
        String sql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE " + dataBaseName + "." + tableName + "(" + insertColumnName + ")";
        return sql;

    }

  /**
     * 通过 LOAD DATA LOCAL INFILE 大批量导入数据到 MySQL。
     *
     * @param sql     SQL语句。
     * @param builder 组装好的数据。
     */

    public int fastInsertData(String sql, StringBuilder builder) {
        int rows = 0;
        InputStream is = null;
        try {
            byte[] bytes = builder.toString().getBytes();
            if (bytes.length > 0) {
                is = new ByteArrayInputStream(bytes);
                //批量插入数据。
                long beginTime = System.currentTimeMillis();
                rows = bulkLoadFromInputStream(sql, is, null);
                long endTime = System.currentTimeMillis();
                logger.info("LOAD DATA LOCAL INFILE :【插入" + rows + "行数据至MySql中，耗时" + (endTime - beginTime) + "ms。】");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != is) {
                    is.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (IOException | SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }