Mybatis批量插入与存储过程批量插入

前言

在数据库操作中，批量插入是一个常见的性能优化手段，特别是在需要插入大量数据时。Mybatis作为一款优秀的持久层框架，提供了多种批量插入数据的方式。除了传统的Mybatis映射文件中的批量插入外，还可以利用存储过程来实现批量插入。本文就来讲解一下Mybatis常用的几种批量插入方式，以及重点介绍存储过程插入。

1. Mybatis传统批量插入

Mybatis中可以通过在Mapper XML文件中编写批量插入的SQL语句，并在Java代码中通过List或数组的形式传递参数来实现批量插入。这种方式需要确保SQL语句支持批量插入，并且需要在Mybatis的配置文件中启用批量操作。

如下代码，可以在Mapper XML文件中编写如下SQL语句：

<insert id="insertBatch" parameterType="list">
    INSERT INTO t2 (id, a, b) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id}, #{item.a}, #{item.b})
    </foreach>
</insert>

在Java代码中，可以调用这个Mapper方法并传入一个包含多个待插入对象的List。这种方式比较适合数据量小的情况。

2. 利用存储过程批量插入

存储过程

接下来重点介绍利用存储过程批量插入。存储过程是存储在数据库中的一组为了完成特定功能的SQL语句集，用户通过指定存储过程的名字和参数（如果有）来调用并执行它。使用存储过程进行批量插入可以减少与数据库的交互次数，提高性能。

在上面的示例中，你已经展示了如何使用存储过程来批量插入数据。存储过程idata和proc_initData分别用于向表t2和oms_order中插入数据。

在存储过程中，你可以使用循环结构（如WHILE循环）来迭代插入数据。这种方法适用于需要动态生成数据或者需要基于某种条件插入数据的情况。但是，需要注意的是，这种方法的性能可能不如直接使用SQL的批量插入语句，因为每次循环都会执行一次INSERT操作，增加了与数据库的交互次数。

案例代码

接下来就用实例代码，演示如何进行使用存储过程进行批量插入，首先准备表结构

-- 创建表
CREATE TABLE `t2` (
    `id` INT ( 11 ) NOT NULL,
    `a` INT ( 11 ) DEFAULT NULL,
    `b` INT ( 11 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ),
    KEY `a` ( `a` )
) ENGINE = INNODB;
DROP PROCEDURE idata;

编写存储过程进行批量插入

-- 创建存储过程，批量插入数据
delimiter;;
CREATE PROCEDURE idata () BEGIN
    DECLARE
        i INT;
    
    SET i = 1;
    WHILE
            ( i <= 1000 ) DO
            INSERT INTO t2
        VALUES
            ( i, i, i );
        SET i = i + 1;
    END WHILE;
END;;


delimiter;
CALL idata ();
CREATE TABLE t1 LIKE t2;
INSERT INTO t1 ( SELECT * FROM t2 WHERE id <= 100 ) SELECT
*
FROM
    t1 STRAIGHT_JOIN t2 ON (
    t1.a = t2.b)

批量插入1w数据

DROP PROCEDURE IF EXISTS proc_initData;

DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        insert into oms_order (order_sn,member_id,total_amount) VALUES (CONCAT('guli商城',i),i,i);
        SET i = i+1;
    END WHILE;
END $
DELIMITER;
CALL proc_initData();

总结

总的来说，批量插入的方式有很多，需要根据业务进行选择，同时需要注意一下事项：

• 性能考虑：批量插入可以显著提高性能，但是过大的批量可能会导致内存问题或者其他资源瓶颈。因此，需要根据实际情况选择合适的批量大小。
• 事务管理：批量插入通常应该在一个事务中执行，以确保数据的完整性和一致性。如果其中一条插入语句失败，整个事务都应该回滚。
• 参数化查询：为了防止SQL注入攻击，应该使用参数化查询来传递插入数据。在Mybatis中，可以通过#{}语法来引用参数。
• 索引和约束：在插入大量数据时，可能需要暂时禁用表上的索引和外键约束，以提高插入速度。但是，在插入完成后，应该重新启用这些索引和约束，以确保数据的完整性和查询性能。
• 错误处理：在编写批量插入的代码时，应该考虑可能出现的错误情况，并编写相应的错误处理逻辑。例如，如果数据库连接失败或者插入语句本身存在语法错误，应该能够捕获这些异常并进行处理。

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！