如何通过MyBatis批量删除MySQL数据？避免单次传大量ID影响性能

Hey there! I totally get your frustration—shoving a huge list of IDs directly into an IN clause can cripple MySQL performance, plus you might hit query length limits before you even get to the performance hit. Let’s break down the best ways to handle this, including using List/JSON structures that MySQL can natively parse.

1. Use MyBatis <foreach> for List Input (Most Common & Efficient Approach) #

MyBatis has built-in support for iterating over List collections, which lets you generate a clean IN clause without manually stitching IDs together. Here’s how to adjust your mapper XML:

<delete id="deleteByIds">
  DELETE FROM your_table a
  WHERE
  <if test="idList != null and idList.size() > 0">
    a.xxx IN
    <foreach collection="idList" item="id" open="(" separator="," close=")">
      #{id}
    </foreach>
  </if>
</delete>

Key Tips: #

• Batch Your Lists: MySQL defaults to a maximum of 1000 elements in an IN clause (you can tweak max_allowed_packet, but it’s not ideal). Split your large ID list into chunks of 500-1000 IDs each and process them in batches. This keeps queries fast and avoids hitting limits.
• Check Your Index: Make sure the a.xxx column has an index—this will make the IN query run orders of magnitude faster, even with large batches.

2. Batch Deletes with MyBatis’s BATCH Executor #

For extremely large datasets (10k+ IDs), use MyBatis’s batch execution mode to cut down on round-trips between your app and MySQL:

try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  YourMapper mapper = session.getMapper(YourMapper.class);
  int batchSize = 1000;
  for (int i = 0; i < idList.size(); i++) {
    mapper.deleteSingleId(idList.get(i));
    if (i % batchSize == 0) {
      session.flushStatements(); // Execute pending batch operations
    }
  }
  session.flushStatements();
  session.commit();
}

对应的单ID删除Mapper方法：

<delete id="deleteSingleId">
  DELETE FROM your_table a WHERE a.xxx = #{id}
</delete>

3. Parse JSON Lists Directly in MySQL #

If you need to pass a list as a single parameter (like a JSON string) and have MySQL parse it, MySQL 5.7+ supports JSON functions that can handle this. Here are two ways to do it:

Option 1: Use JSON_CONTAINS #

<delete id="deleteByJsonIds">
  DELETE FROM your_table a
  WHERE JSON_CONTAINS(#{idJson}, CAST(a.xxx AS JSON))
</delete>

In your Java code, convert your ID list to a JSON string (e.g., [1,2,3,4,...]):

String idJson = new ObjectMapper().writeValueAsString(idList);
mapper.deleteByJsonIds(idJson);

Option 2: Use JSON_TABLE for Relational Joins #

For more complex scenarios, you can convert the JSON array into a temporary table and join it:

<delete id="deleteByJsonIds">
  DELETE a
  FROM your_table a
  JOIN JSON_TABLE(
    #{idJson},
    '$[*]' COLUMNS(id INT PATH '$')
  ) jt ON a.xxx = jt.id
</delete>

Caveat: #

JSON functions are convenient, but they’re generally slower than a properly indexed IN clause. Stick to this only if you can’t split the list into batches for some reason.

Final Recommendations #

• For most cases: Go with <foreach> + batch splits (500-1000 IDs per batch) + an indexed column—it’s the fastest and most reliable approach.
• For massive datasets: Use MyBatis’s BATCH Executor to minimize network overhead.
• For edge cases where you need a single parameter: Use MySQL’s JSON parsing functions, but keep an eye on performance.

内容的提问来源于stack exchange，提问作者kevintao