Comprehensive Guide to MyBatis Batch Insert: Three Methods, Performance Comparison, and Analysis

Batch insertion is a frequent requirement in everyday development, yet many developers misunderstand MyBatis‑Plus (MP) batch insert, assuming it merely loops single inserts without performance gain; this article consolidates three batch‑insert approaches, presents their implementations, and conducts performance tests.

Preparation : A test MySQL database testdb and a user table are created, and five sample rows are inserted. The SQL script used is:

-- ----------------------------
-- 创建数据库
-- ----------------------------
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP DATABASE IF EXISTS `testdb`;
CREATE DATABASE `testdb`;
USE `testdb`;

-- ----------------------------
-- 创建 user 表
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `createtime` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 CHARACTER SET utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=Dynamic;

-- ----------------------------
-- 添加测试数据
-- ----------------------------
INSERT INTO `user` VALUES (1,'赵云','123456','2021-09-10 18:11:16');
INSERT INTO `user` VALUES (2,'张飞','123456','2021-09-10 18:11:28');
INSERT INTO `user` VALUES (3,'关羽','123456','2021-09-10 18:11:34');
INSERT INTO `user` VALUES (4,'刘备','123456','2021-09-10 18:11:41');
INSERT INTO `user` VALUES (5,'曹操','123456','2021-09-10 18:12:02');
SET FOREIGN_KEY_CHECKS = 1;

1. Loop Single Insert : Using a Spring Boot test, 100,000 rows are inserted one by one. The core test code is:

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserControllerTest {
    private static final int MAXCOUNT = 100000;
    @Autowired
    private UserServiceImpl userService;

    @Test
    void save() {
        long stime = System.currentTimeMillis();
        for (int i = 0; i < MAXCOUNT; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            userService.save(user);
        }
        long etime = System.currentTimeMillis();
        System.out.println("执行时间：" + (etime - stime));
    }
}

The execution time recorded was 88,574 ms.

2. MyBatis‑Plus (MP) Batch Insert : After adding the MP starter dependency, three classes are created – UserController , UserServiceImpl , and UserMapper . Sample snippets:

// Controller
@RestController
@RequestMapping("/u")
public class UserController {
    @Autowired
    private UserServiceImpl userService;

    @RequestMapping("/mysavebatch")
    public boolean mySaveBatch() {
        List
list = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            list.add(user);
        }
        return userService.saveBatchCustom(list);
    }
}

The service and mapper interfaces simply delegate to MP’s saveBatch method. The performance test (inserting 100,000 rows) took 6,088 ms, about 14.5× faster than the loop method.

MP’s saveBatch internally splits the data into batches of 1,000 rows, which explains the speed improvement.

3. Native Batch Insert : Using MyBatis’s <foreach> tag, a custom batch method is added. Relevant code pieces include:

// Service extension
public boolean saveBatchByNative(List
list) {
    return userMapper.saveBatchByNative(list);
}

// Mapper interface
boolean saveBatchByNative(List
list);

INSERT INTO `USER`(`NAME`,`PASSWORD`) VALUES
(#{item.name},#{item.password})

When this method attempts to insert 100,000 rows in a single SQL statement, MySQL rejects the statement because the generated SQL exceeds the default max_allowed_packet (4 MB), resulting in an error.

Drawbacks and Solutions : The native approach can hit the packet‑size limit; increasing max_allowed_packet (e.g., to 10 MB) mitigates the error but does not address the underlying scalability issue. Therefore, batching—as MP does—is the recommended practice.

Conclusion : Among the three methods, looped single inserts are the slowest and should be avoided; native SQL batch inserts are fastest but risky due to packet‑size constraints; MP’s batch insert offers a balanced solution with significant performance gains and safe batch sizing.