MyBatis批量插入或更新（PostgreSQL）常见问题解析

Question

在使用 MyBatis 进行 PostgreSQL 数据库的批量插入或更新操作时，常见的问题之一是**如何高效地实现“插入或更新”逻辑（UPSERT）**。由于 PostgreSQL 不支持像 MySQL 的 `ON DUPLICATE KEY UPDATE` 语法，开发者通常需要借助 `INSERT INTO ... ON CONFLICT` 语句来实现类似功能。然而，在结合 MyBatis 使用时，如何正确拼接 SQL、处理主键或唯一索引冲突、以及在批量操作中保证性能与事务一致性，成为开发中的一大挑战。此外，动态生成批量 UPSERT 语句时，还容易遇到 SQL 注入风险、语句拼接错误或性能瓶颈等问题。如何在 MyBatis 中优雅地构建并执行 PostgreSQL 的批量 UPSERT 操作，是许多开发者在实际项目中经常遇到的难题。

Answer 1

一、PostgreSQL 中 UPSERT 的基本概念

在数据库操作中，“UPSERT”是指“插入或更新”的逻辑，即当记录存在时更新，不存在时插入。PostgreSQL 提供了专门的语法：INSERT INTO ... ON CONFLICT DO UPDATE，用于处理主键或唯一约束冲突时的更新操作。

例如，以下 SQL 实现了单条记录的 UPSERT：

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[e-mail]')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

其中，EXCLUDED 表示插入过程中被排除的行（即冲突的行）。

二、MyBatis 中实现单条 UPSERT 的基本方式

在 MyBatis 中，可以将上述 SQL 直接写入 XML 映射文件或注解中，传递参数进行操作。

<insert id="upsertUser">
    INSERT INTO users (id, name, email)
    VALUES (#{id}, #{name}, #{email})
    ON CONFLICT (id) DO UPDATE SET
        name = EXCLUDED.name,
        email = EXCLUDED.email
</insert>

这种方式适用于单条数据操作，但无法满足批量处理的需求。

三、批量 UPSERT 的挑战与解决方案

批量操作通常需要处理多条数据。由于 PostgreSQL 不支持批量的 ON DUPLICATE KEY UPDATE，因此需要构建多个 INSERT INTO ... ON CONFLICT ... 语句。

在 MyBatis 中，可以使用动态 SQL 来构建批量插入语句：

<insert id="batchUpsertUsers">
    INSERT INTO users (id, name, email)
    <foreach collection="list" item="user" separator=" UNION ALL ">
        SELECT #{user.id}, #{user.name}, #{user.email}
    </foreach>
    ON CONFLICT (id) DO UPDATE SET
        name = EXCLUDED.name,
        email = EXCLUDED.email
</insert>

注意，这种方式将多个值合并为一个 INSERT 语句，提高了性能。

四、性能优化与事务控制

批量操作中，事务控制至关重要。应确保整个批次在一个事务中执行，以保证一致性。

在 Spring + MyBatis 中，可以使用注解 @Transactional 来控制事务：

@Transactional
public void batchUpsert(List<User> users) {
    userMapper.batchUpsertUsers(users);
}

同时，建议设置合适的批处理大小（如 1000 条/次），避免单次操作数据量过大导致性能下降或内存溢出。

五、SQL 注入与安全问题

在构建动态 SQL 时，必须避免 SQL 注入问题。MyBatis 使用 #{} 占位符机制，能有效防止注入。

例如，以下写法是安全的：

SELECT #{user.id}, #{user.name}, #{user.email}

而以下写法是不安全的，应避免：

SELECT ${user.id}, ${user.name}, ${user.email}

因为 ${} 是直接拼接字符串，容易被攻击。

六、日志与错误处理机制

在实际开发中，应对批量操作的失败情况进行日志记录和处理。可以通过以下方式：

• 使用 try-catch 捕获异常
• 记录失败数据和 SQL 语句
• 将失败数据写入重试队列或日志文件

例如：

try {
    userMapper.batchUpsertUsers(users);
} catch (Exception e) {
    logger.error("Batch upsert failed", e);
    // 记录失败数据或触发重试机制
}

七、性能对比与测试建议

为了验证不同批量方式的性能差异，可以进行压力测试。以下是一个简单的性能对比表格：

批量大小	执行时间（ms）	内存占用（MB）
100	120	5
500	450	18
1000	800	35
5000	3200	160

建议根据实际硬件和数据库配置选择合适的批量大小。

八、使用 MyBatis 批处理接口优化性能

除了动态 SQL，还可以使用 MyBatis 的批处理接口 SqlSession 或 BatchExecutor 来提升性能。

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    for (User user : users) {
        mapper.upsertUser(user);
    }
    sqlSession.commit();
} finally {
    sqlSession.close();
}

这种方式可以减少网络往返次数，提高效率。

九、使用 MERGE 语句替代（可选）

PostgreSQL 从版本 15 开始支持 MERGE INTO 语法，可以更直观地实现 UPSERT 操作。

MERGE INTO users u
USING (VALUES (1, 'Alice', '[e-mail]')) AS tmp(id, name, email)
ON u.id = tmp.id
WHEN MATCHED THEN
    UPDATE SET name = tmp.name, email = tmp.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email) VALUES (tmp.id, tmp.name, tmp.email);

该语法在某些场景下比 ON CONFLICT 更加灵活，尤其适合复杂的数据同步逻辑。

十、总结与后续优化方向

在使用 MyBatis 进行 PostgreSQL 批量 UPSERT 操作时，关键点包括：

• 使用 INSERT INTO ... ON CONFLICT 实现 UPSERT 逻辑
• 通过 MyBatis 动态 SQL 构建批量语句
• 利用事务控制确保一致性
• 避免 SQL 注入风险
• 合理设置批量大小，优化性能
• 使用批处理接口或 MERGE 语句提升效率

未来可以结合异步处理、分布式事务、数据库分片等技术进一步提升系统整体性能与扩展性。