利用mysql load data秒级别大批量写入数据，建议单次10w+以上~速度远超批量insert，附上完整封装工具

平时项目中，有时候可能会遇到需要大批量写入数据到mysql数据库中的场景，这时候我们可以利用mysql自带的load data语法，先将数据写入文本文件，再将文件读入数据库，具体用法看下面！

1、表结构如下：

CREATE TABLE `wework_receive_messages` (
  `msg_id` varchar(64) NOT NULL COMMENT 'ID',
  `action` varchar(12) DEFAULT NULL COMMENT '动作',
  `from_user` varchar(64) DEFAULT NULL COMMENT '发出用户',
  `to_list` varchar(2056) DEFAULT NULL,
  `room_id` varchar(32) DEFAULT NULL COMMENT '房间id',
  `msg_time` bigint(20) DEFAULT NULL COMMENT '消息时间',
  `msg_type` varchar(12) DEFAULT NULL COMMENT '消息类型',
  `content` varchar(1024) DEFAULT NULL COMMENT '消息内容',
  `seq` bigint(20) DEFAULT NULL COMMENT '消息计数',
  `msg_flag` tinyint(1) DEFAULT NULL COMMENT '消息状态(0未拉取 1已拉取)',
  PRIMARY KEY (`msg_id`),
  KEY `ix_wework_receive_messages_from_user` (`from_user`),
  KEY `ix_wework_receive_messages_msg_time` (`msg_time`),
  KEY `ix_wework_receive_messages_room_id` (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、原生sql用法如下：

load data concurrent local infile '路径' replace into table '表名' character set utf8 fields terminated by '字段分隔符' lines terminated by '行分隔符'  (字段1, 字段2, 字段3); 

3、封装工具代码如下：

LoadUtil.java  load data主要工具

import lombok.extern.log4j.Log4j2;
import org.springframework.jdbc.core.JdbcTemplate;
import java.lang.reflect.AnnotatedType;
import java.lang.reflect.Field;
import java.util.List;


@Log4j2
public class LoadUtil<T> {

    private String tableName;

    private String[] columns;

    private List<T> data;
    
    // 字段分隔符
    private String terminated;

    // 行数据分隔符
    private String linesTerminated;

    private ModeEnum mode;

    public LoadUtil(String tableName, String[] columns, List<T> data, String terminated, String linesTerminated,
                    ModeEnum mode) {
        this.tableName = tableName;
        this.columns = columns;
        this.data = data;
        this.terminated = terminated;
        this.linesTerminated = linesTerminated;
        this.mode = mode;
    }

    public LoadUtil(String tableName, String[] columns, List<T> data, ModeEnum mode) {
        this(tableName, columns, data, "$$", "\n", mode);
    }

    public LoadUtil(String tableName, String[] columns, List<T> data) {
        this(tableName, columns, data, ModeEnum.replace);
    }

    public void loadData() {
        String filePath = "";
        try {
            long startAt = System.currentTimeMillis();
            log.info("数据大小: {}", this.data.size());
            String str = concatData();
            filePath = writeFile(str);

            String sql = sqlGenerator(filePath);
            log.info("load data sql: {}", sql);
            long endAt = System.currentTimeMillis();
            log.info("耗时: {}s", (endAt - startAt) / 1000);
            log.info("load data写入完成");
            sqlExecutor(sql);
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            deleteFile(filePath);
        }
    }

    private void deleteFile(String filePath) {
        if(filePath != null && filePath != "") {
            IOUtil.delete(filePath);
        }
    }

    private String sqlGenerator(String filePath) {
        String sql = String.format("load data concurrent local infile '%s' %s into table %s character set utf8 fields terminated by '%s' lines terminated by '%s' (%s)", filePath, mode.name(),
                tableName, terminated, linesTerminated, StringUtils.join(columnsConvert(), ","));
        return sql;
    }

    private void sqlExecutor(String sql) {
        JdbcTemplate jdbcTemplate  = (JdbcTemplate) ApplicationContextUtils.getBean("jdbcTemplate");
        jdbcTemplate.execute(sql);
    }

    private String[] columnsConvert() {
        String[] _columns = new String[columns.length];
        for(int i=0,len=columns.length; i<len; i++) {
            _columns[i] = StringUtils.camelhumpToUnderline(columns[i]);
        }
        return _columns;
    }

    private String concatData() throws NoSuchFieldException, IllegalAccessException {
        StringBuilder sb = new StringBuilder();
        for(T t : data) {
            for(String column : columns) {
                Field field = t.getClass().getDeclaredField(column);
                field.setAccessible(true);
                Object o = field.get(t);
                sb.append(o != null ? removeSpecialChar(o.toString()) :
                        getDefaultValue(column, t)).append(terminated);
            }
            sb.append(linesTerminated);
        }
        return sb.toString();
    }

    private String writeFile(String str) {
        String filePath = "/tmp".concat("/").concat(StringUtils.shortUuid()).concat(".txt");
        IOUtil.write(filePath, false, str);
        return filePath;
    }

    private Object getDefaultValue(String key, Object object) throws NoSuchFieldException {
        Field field = object.getClass().getDeclaredField(key);
        field.setAccessible(true);
        AnnotatedType annotatedType = field.getAnnotatedType();
        String typeName = annotatedType.getType().getTypeName();
        if(typeName == "java.lang.String") {
            return "";
        } else if(typeName == "java.lang.Integer") {
            return 0;
        } else if(typeName == "java.lang.Long") {
            return 0;
        } else if(typeName == "java.lang.Boolean") {
            return true;
        }
        return "";
    }

    private String removeSpecialChar(String str) {
        str = str.trim().replaceAll(terminated, "").replaceAll(linesTerminated, "");
        return str;
    }

    public enum ModeEnum {
        ignore,
        replace,
    }

}

依赖的方法如下：

ApplicationContextUtils.java  spring bean对象获取工具，此处主要用来获取jdbcTemplate对象

import lombok.extern.log4j.Log4j2;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;


@Component
@Log4j2
public class ApplicationContextUtils implements ApplicationContextAware {

	private static ApplicationContext context;

	@Override
	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		if (context == null) {
			context = applicationContext;
		}
	}

	public static ApplicationContext getApplicationContext() {
		return context;
	}

	public static Object getBean(String name) {
		log.info("application_context: {}", context);
		return context.getBean(name);
	}

	public static <T> T getBean(Class<T> classz) {
		return context.getBean(classz);
	}
}

 IOUtil.java  用来写入临时文本文件

package com.mw.web.common.utils;

import java.io.*;

public class IOUtil {
	
	// 读取指定路径文本文件  
    public static String read(String filePath) {  
        StringBuilder str = new StringBuilder();  
        BufferedReader in = null;  
        try {  
            in = new BufferedReader(new FileReader(filePath));  
            String s;  
            try {  
                while ((s = in.readLine()) != null)  
                    str.append(s + '\n');  
            } finally {  
                in.close();  
            }  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return str.toString();  
    }
    
    // 读取指定路径文本文件  
    public static String readWeb(String filePath) {  
        StringBuilder str = new StringBuilder();  
        BufferedReader in = null;  
        try {  
        	InputStream is = IOUtil.class.getClassLoader().getResourceAsStream(filePath);
        	InputStreamReader isr = new InputStreamReader(is);
            in = new BufferedReader(isr);  
            String s;  
            try {  
                while ((s = in.readLine()) != null)  
                    str.append(s + '\n');  
            } finally {  
                in.close();  
            }  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return str.toString();  
    }  
  
    // 写入指定的文本文件，append为true表示追加，false表示重头开始写，  
    //text是要写入的文本字符串，text为null时直接返回  
    public static void write(String filePath, boolean append, String text) {  
        if (text == null)  
            return;  
        try {  
            BufferedWriter out = new BufferedWriter(new FileWriter(filePath,  
                    append));  
            try {  
                out.write(text);  
            } finally {  
                out.close();  
            }  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
    
    public static String getAbsolutePath(String path) {
    	if(null!=path && ""!=path){
    		String curPath = System.getProperty("user.dir").toString();
    		if(path.substring(0, 1)=="/"){
    			return curPath + path;
    		}else{
    			return curPath + "/" + path;
    		}
    	}
    	return null;
    }

    public static void delete(String filePath) {
        File file = new File(filePath);
        if(file.exists()) {
            file.delete();
        }
    }

}

StringUtils.java  字符串处理工具，例如将字段名驼峰与蛇形命名风格相互转换！

package com.mw.web.common.utils;

import java.io.IOException;
import java.security.InvalidParameterException;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class StringUtils {
	

	public static String uuid() {
		return UUID.randomUUID().toString();
	}

	public static String shortUuid() {
		return uuid().replaceAll("-", "");
	}

	public static String join(String[] arrays, String terminated) {
		StringBuilder sb = new StringBuilder();
		for(String str : arrays) {
			sb.append(str).append(terminated);
		}
		String result = sb.toString();
		result = result.substring(0, result.length() - terminated.length());
		return result;
	}

	public static String underlineToCamelhump(String inputString) {
		Pattern linePattern = Pattern.compile("_(\\w)");
		inputString = inputString.toLowerCase();
		Matcher matcher = linePattern.matcher(inputString);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	public static String camelhumpToUnderline(String inputString) {
		Pattern humpPattern = Pattern.compile("[A-Z]");
		Matcher matcher = humpPattern.matcher(inputString);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
		}
		matcher.appendTail(sb);
		return sb.toString();
	}
	

}

 3、测试方法如下：

MessageReceiveParam.java 对象实体类

package com.mw.web.param;

import lombok.Builder;
import lombok.Data;
import java.io.Serializable;


@Data
@Builder
public class MessageReceiveParam implements Serializable {

    private String msgId;

    private String action;

    private String fromUser;

    private String toList;

    private String roomId;

    private String msgType;

    private long msgTime;

    private String content;

    private Long seq;

    private Integer msgFlag;

}

test.java 测试方法

public void test() {
    List<MessageReceiveParam> list = new ArrayList<>();
    for(int i=0, len=100000;i < len; i++) {
        list.add(MessageReceiveParam.builder().msgId("test" + i).content("测试" + i).build());
    }

    // 获取实体类的所有字段
    String[] fieldNames = EntityUtil.getFieldNames(MessageReceiveParam.class);
    // wework_receive_messages 为数据库表名
    LoadUtil<MessageReceiveParam> loadUtil = new LoadUtil<>("wework_receive_messages", fieldNames, list);
    loadUtil.loadData();    
}

总结：以上就是整个load data的使用方法和封装工具，注意使用前请在数据库连接里面先配置开启允许load data命令 allowLoadLocalInfile=true