栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

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

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

利用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 {

    private String tableName;

    private String[] columns;

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

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

    private ModeEnum mode;

    public LoadUtil(String tableName, String[] columns, List 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 data, ModeEnum mode) {
        this(tableName, columns, data, "$$", "n", mode);
    }

    public LoadUtil(String tableName, String[] columns, List 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 

依赖的方法如下:

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 getBean(Class 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 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 loadUtil = new LoadUtil<>("wework_receive_messages", fieldNames, list);
    loadUtil.loadData();    
}

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

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/958830.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号