mybatis批量插入方式对比

逃离我推掉我的手 2022-05-24 00:54 173阅读 0赞

第一种：普通for循环插入  
①junit类

@Test
    public void testInsertBatch2() throws Exception {
        long start = System.currentTimeMillis();
        User user;
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(false);
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for (int i = 0; i < 500; i++) { 
            user = new User();
            user.setId("test" + i);
            user.setName("name" + i);
            user.setDelFlag("0");
            mapper.insert(user);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("---------------" + (start - end) + "---------------");

②xml配置

<insert id="insert">
        INSERT INTO t_user (id, name, del_flag)
              VALUES(#{
       id}, #{
       name}, #{
       delFlag})
    </insert>

第二种：mybatis BATCH模式插入  
①junit类

@Test
    public void testInsertBatch2() throws Exception {
        long start = System.currentTimeMillis();
        User user;
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql区别
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        for (int i = 0; i < 500; i++) { 
            user = new User();
            user.setId("test" + i);
            user.setName("name" + i);
            user.setDelFlag("0");
            mapper.insert(user);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("---------------" + (start - end) + "---------------");
    }

②xml配置

<insert id="insert">
        INSERT INTO t_user (id, name, del_flag)
              VALUES(#{
       id}, #{
       name}, #{
       delFlag})
    </insert>

第三种：foreach方式插入  
①junit类

@Test
    public void testInsertBatch() throws Exception {
        long start = System.currentTimeMillis();
        List<User> list = new ArrayList<>();
        User user;
        for (int i = 0; i < 10000; i++) {
            user = new User();
            user.setId("test" + i);
            user.setName("name" + i);
            user.setDelFlag("0");
            list.add(user);
        }
        userService.insertBatch(list);
        long end = System.currentTimeMillis();
        System.out.println("---------------" + (start - end) + "---------------");
    }

②xml配置  
MySQL

<insert id="insertBatch">
        INSERT INTO t_user
                (id, name, del_flag)
        VALUES
        <foreach collection ="list" item="user" separator =",">
             (#{user.id}, #{user.name}, #{user.delFlag})
        </foreach >
    </insert>

Oracle

<insert id="insertBatch">
        INSERT ALL
        <foreach collection ="list" item="user" index="index">
        INTO t_user(id, name, del_flag)  VALUES
             (#{user.id}, #{user.name}, #{user.delFlag})
        </foreach >
        SELECT 1 FROM DUAL
    </insert>

第四种：foreach存储过程方式插入  
①junit类

②xml配置

<insert id="insertBatch">
        begin
        <foreach collection ="list" item="user" >
            INSERT INTO t_user
                (id, name, del_flag)
        VALUES (#{user.id}, #{user.name}, #{user.delFlag});
        </foreach >
        end;
    </insert>

特别注意：mysql默认接受sql的大小是1048576(1M)，即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的my.ini文件中\[mysqld\]段的＂max\_allowed\_packet = 1M＂）

nested  
exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).  
You  
can change this value on the server by setting the max\_allowed\_packet’ variable.

结果对比：  
![这里写图片描述][70]  
时间有限测试数据较少，有兴趣可以自己测试以下。（不清楚为什么BATCH有时候比单条循环插入还耗时间，请知道的大神不吝赐教，感谢！）

[70]: /images/20220524/1c503d55445647d9bd582ee565cbad91.png