mybatis批量百万数据mysql_mybatis 百万数据插入-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_31746993/article/details/114325953

本文介绍了如何使用MyBatis进行批量插入MySQL数据的优化，通过在数据库连接URL中设置rewriteBatchedStatements=true开启批量插入，并提供了一个示例代码，展示了如何生成并插入100批次，每批次10000条记录的数据，以及记录和展示执行总耗时的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开启批量插入数据库连接url中增加一个rewriteBatchedStatements=true(开启批量插入)

2.用foreach拼接sql插入语句

环境配置环境说明：WIN10+16G内存+i7-4770U3.40GHz+JDK8+MySQL8.0+

上代码

@Testpublic void monitor(){

//循环次数

int count = 100;

//每次插入条数

int size = 10000;

Calendar calendar = Calendar.getInstance();

//calendar的月从0开始算

calendar.set(2011,05,01,0,0,0);

long dayTime = calendar.getTimeInMillis();

long sumTime = 0L;

for (int i = 0; i < count; i++) {

List monitors = new ArrayList();

for (int j = 0; j < size; j++) {

Monitor monitor = new Monitor();

monitor.setId(IdWorker.getId());

monitor.setBdjm("铸造二期3#进线");

monitor.setBdz("00000000023");

monitor.setRqsj(new Date(dayTime));

dayTime += 300_000L; // 5*60*1000为5分钟

monitor.setYgdl(RandomUtil.getRandomNumber(1,10));

monitor.setWgdl(RandomUtil.getRandomNumber(1,10));

monitor.setFwgdl(RandomUtil.getRandomNumber(1,10));

monitor.setFygdl(RandomUtil.getRandomNumber(1,10));

monitor.setOnexxwg(RandomUtil.getRandomNumber(1,10));

monitor.setThreexxwg(RandomUtil.getRandomNumber(1,10));

monitor.setTwoxxwg(RandomUtil.getRandomNumber(1,10));

monitor.setFourxxwg(RandomUtil.getRandomNumber(1,10));

monitor.setAa(RandomUtil.getRandomNumber(1,10));

monitor.setAv(RandomUtil.getRandomNumber(1,10));

monitor.setBa(RandomUtil.getRandomNumber(1,10));

monitor.setBv(RandomUtil.getRandomNumber(1,10));

monitor.setCa(RandomUtil.getRandomNumber(1,10));

monitor.setCv(RandomUtil.getRandomNumber(1,10));

monitor.setGlcs(RandomUtil.getRandomNumber(1,10));

monitor.setSzgl(RandomUtil.getRandomNumber(1,10));

monitor.setWggl(RandomUtil.getRandomNumber(1,10));

monitor.setAxgl(RandomUtil.getRandomNumber(1,10));

monitor.setAxglcs(RandomUtil.getRandomNumber(1,10));

monitor.setAxwgl(RandomUtil.getRandomNumber(1,10));

monitor.setBxgl(RandomUtil.getRandomNumber(1,10));

monitor.setBxglcs(RandomUtil.getRandomNumber(1,10));

monitor.setBxwgl(RandomUtil.getRandomNumber(1,10));

monitor.setCxgl(RandomUtil.getRandomNumber(1,10));

monitor.setCxglcs(RandomUtil.getRandomNumber(1,10));

monitor.setCxwgl(RandomUtil.getRandomNumber(1,10));

monitor.setYggl(RandomUtil.getRandomNumber(1,10));

monitor.setDwpl(RandomUtil.getRandomNumber(1,10));

monitor.setUabUaAngle(RandomUtil.getRandomNumber(1,10));

monitor.setUbAngle(RandomUtil.getRandomNumber(1,10));

monitor.setUcbUcAngle(RandomUtil.getRandomNumber(1,10));

monitor.setZglcs(RandomUtil.getRandomNumber(1,10));

monitor.setDlbph(RandomUtil.getRandomNumber(1,10));

monitor.setIaAngle(RandomUtil.getRandomNumber(1,10));

monitor.setIbAngle(RandomUtil.getRandomNumber(1,10));

monitor.setIcAngle(RandomUtil.getRandomNumber(1,10));

monitors.add(monitor);

}

Instant inst1 = Instant.now();

monitorService.batchInsert(monitors);

Instant inst2 = Instant.now();

sumTime += ChronoUnit.MILLIS.between(inst1, inst2);

}

System.out.println( formatTime(sumTime) );

}

public static String formatTime(Long ms) {

Integer ss = 1000;

Integer mi = ss * 60;

Integer hh = mi * 60;

Integer dd = hh * 24;

Long day = ms / dd;

Long hour = (ms - day * dd) / hh;

Long minute = (ms - day * dd - hour * hh) / mi;

Long second = (ms - day * dd - hour * hh - minute * mi) / ss;

Long milliSecond = ms - day * dd - hour * hh - minute * mi - second * ss;

StringBuffer sb = new StringBuffer();

if(day > 0) {

sb.append(day+"天");

}

if(hour > 0) {

sb.append(hour+"小时");

}

if(minute > 0) {

sb.append(minute+"分");

}

if(second > 0) {

sb.append(second+"秒");

}

if(milliSecond > 0) {

sb.append(milliSecond+"毫秒");

}

return sb.toString();

}