DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年齡',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手機(jī)號(hào)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶表' ROW_FORMAT = DYNAMIC;

truncate table t_user;

/**
     * 測(cè)試大批量數(shù)據(jù)插入數(shù)據(jù)庫(kù)
     * 方式1：用一個(gè) for 循環(huán)，把數(shù)據(jù)一條一條的插入
     * @return
     */
@GetMapping("/insertUser1/{count}")
public JSONResult insertUser1(@PathVariable("count") Integer count){
    return userService.insertUser1(count);
}

public JSONResult insertUser1(Integer count) {
    //如果自動(dòng)提交設(shè)置為true，將無(wú)法控制提交的條數(shù)。所以我這里設(shè)置為false，，改為統(tǒng)一提交
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    UserMapper uMapper = session.getMapper(UserMapper.class);
    //獲取測(cè)試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    int commitCount = 5000;//每次提交的數(shù)量條數(shù)
    long startTime = System.currentTimeMillis();
    for(int i=0; i<userList.size(); i++){
        uMapper.addUserOne(userList.get(i));
        if (i != 0 && i % commitCount == 0) {
            session.commit();
        }
    }
    session.commit();
    long endTime = System.currentTimeMillis();
    log.info("方式1耗時(shí)：{}", (endTime - startTime));
    return JSONResult.success();
}

Integer addUserOne(User user);

<insert id="addUserOne">
    insert into t_user (name, age, phone) values (#{name}, #{age}, #{phone})
</insert>

/**
     * 獲取指定數(shù)量的用戶測(cè)試對(duì)象
     * @param count 數(shù)量
     * @return
     */
public static List<User> getUserList(int count){
    List<User> userList = new ArrayList<>();
    User user = null;
    for(int i=1; i<=count; i++){
        user = new User();
        user.setName("王天霸" + i + "號(hào)");
        user.setAge(i);
        user.setAge(i);
        user.setPhone("18022222222");
        userList.add(user);
    }
    return userList;
}

show VARIABLES like '%max_allowed_packet%';

/**
     * 測(cè)試大批量數(shù)據(jù)插入數(shù)據(jù)庫(kù)
     * 方式2：foreach標(biāo)簽批量插入
     * @return
     */
@GetMapping("/insertUser2/{count}")
public JSONResult insertUser2(@PathVariable("count") Integer count){
    return userService.insertUser2(count);
}

public JSONResult insertUser2(Integer count) {
    //獲取測(cè)試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    int countItem = 5000;//每次提交的記錄條數(shù)
    int userSize = userList.size();
    List<User> userListTemp = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    for (int i = 0, n=userSize; i < n; i++) {
        User user= userList.get(i);
        userListTemp.add(user);
        if ((i>0 && i % countItem == 0) || i == userSize - 1) {
            //每5000條記錄提交一次
            userMapper.insertUserBatch(userListTemp);
            userListTemp.clear();
        }
    }
    long endTime = System.currentTimeMillis();
    log.info("方式2耗時(shí)：{}", (endTime - startTime));
    return JSONResult.success();
}

void insertUserBatch(@Param("userList") List<User> userList);

<insert id="insertUserBatch">
    insert into t_user (name, age, phone) values
    <foreach collection="userList" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.phone})
    </foreach>
</insert>

@Autowired
public UserService userService;
/**
     * 測(cè)試大批量數(shù)據(jù)插入數(shù)據(jù)庫(kù)
     * 方式3：mybatisplus自帶的saveBatch批量新增方法
     * @return
     */
@GetMapping("/insertUser3/{count}")
public JSONResult insertUser3(@PathVariable("count") Integer count){
    //獲取測(cè)試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    long startTime = System.currentTimeMillis();
    userService.saveBatch(userList, 5000);//每次往數(shù)據(jù)庫(kù)提交5000條數(shù)據(jù)
    long endTime = System.currentTimeMillis();
    log.info("方式3耗時(shí)：{}", (endTime - startTime));
    return JSONResult.success();
}