Java 实现 Excel 批量导入 MySQL 的工程实践与性能优化

简介：这份资源面向Java后端初学者与需要处理数据迁移的开发者，提供了一套完整的Excel与MySQL双向数据同步示例。项目基于Apache POI解析xls/xlsx文件，通过JDBC连接MySQL，实现Excel数据导入数据库，并在检测到重复数据时执行更新，同时支持将库中数据反向导出为Excel表格，覆盖文件操作、数据解析、SQL编写与事务处理等核心技能。压缩包共20个文件，约1.31MB，包含6个java源码与6个class编译文件、2个依赖jar（mysql驱动与jxl）、1个建表sql脚本，以及Eclipse工程配置与说明文档，结构清晰便于直接导入运行。目前已有1737人学习下载，适合作为课程设计、毕业项目或日常数据导入导出工具的参考实现，帮助读者快速理解批量插入、条件更新与结果集写回Excel的完整链路。

1. Java 把 Excel 灌进 MySQL：一条被低估的脏活链路

电商后台的运营同事丢过来一个 12 万行的 Excel，说「导一下库存」，你打开一看：合并单元格、日期列混着文本、手机号被存成科学计数法、还有三行是空行夹在中间。这时候你才意识到，Excel 导入 MySQL 这件事，从来不是read → insert两行代码能收场的。它是一条完整的脏活链路：文件解析、类型推断、批量写入、事务边界、失败重试，每一环都能让你加班到凌晨。

这个方向适合谁？做后台管理系统的 Java 工程师、需要给业务方做数据迁移的开发者、以及正在准备java 面试题里「大文件导入怎么优化」这类问题的同学。它不需要你上大数据栈，spring boot + mybatis或者纯 JDBC 就能跑通，但要把 10 万行级别的导入做到「不 OOM、不超时、可回滚、能定位坏行」，里面全是血泪经验。下面我按自己实际落地的顺序，把选型、代码、参数和翻车点一次讲透。

2. 选型先立住：POI、EasyExcel 还是流式 SAX

2.1 三种解析路线的内存账

很多人一上来就new XSSFWorkbook(inputStream)，本地跑 5000 行没问题，生产上 8 万行直接OutOfMemoryError: Java heap space。原因很简单：XSSF 会把整个 xlsx 解压后的 XML 树全部加载进内存，一个 10 万行、20 列的文件，堆里轻松吃掉 1.5G 以上。所以选型的第一原则是——先看内存模型，再看 API 好不好用。

我一般会：文件超过 2 万行，直接上 EasyExcel 或 POI 的 SAX 事件模型。EasyExcel 的优势是把 SAX 那套ContentHandler的样板代码封成了ReadListener，你只关心「读到一行怎么处理」，不用手写startElement / endElement的状态机。代价是它对复杂公式、图表、宏的支持有限——但导入场景本来也不需要这些。

2.2 依赖怎么引，版本别乱跳

<!-- pom.xml 片段 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.3.2</version> <!-- 3.x 与 2.x 的监听器 API 不兼容，别混用 --> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.33</version> </dependency>

这里有个容易翻车的点：EasyExcel 3.x 把AnalysisEventListener的泛型回调签名做了调整，网上大量 2.x 的示例代码直接抄过来会编译不过。如果你项目里已经有别的模块在用 2.x，要么统一升到 3.x，要么给导入模块单独做依赖隔离，别让两个版本在同一个 classpath 里打架。

2.3 数据库侧的前置准备

导入性能有一半不在 Java 代码里，而在 MySQL 的配置和表结构上。建表时几个必须确认的点：

CREATE TABLE `product_import` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `sku` VARCHAR(64) NOT NULL COMMENT '商品编码', `name` VARCHAR(255) DEFAULT NULL, `price` DECIMAL(10,2) DEFAULT NULL, `stock` INT DEFAULT 0, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_sku` (`sku`) -- 唯一索引是幂等导入的关键 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

uk_sku这个唯一索引不是可选项。没有它，你重复导入同一个文件就会产生重复数据，而业务方几乎一定会重复点「导入」按钮。有了它，配合INSERT ... ON DUPLICATE KEY UPDATE或者INSERT IGNORE，导入天然具备幂等性。另外utf8mb4别省，Excel 里一个 emoji 就能让utf8的列插入报Incorrect string value。

3. 从 Excel 到 MySQL 的最小可跑通实现

3.1 定义实体和监听器

先定义一个和表结构对应的实体，字段上用注解标好列头名称。EasyExcel 默认按列顺序读，但生产上列顺序经常被业务方调整，所以强烈建议按列头名称匹配，而不是按 index。

@Data public class ProductRow { @ExcelProperty("商品编码") private String sku; @ExcelProperty("商品名称") private String name; @ExcelProperty("价格") private BigDecimal price; @ExcelProperty("库存") private Integer stock; }

监听器是核心，它决定「每读一行做什么」。关键设计是攒批：不要读一行插一次，那样 10 万行就是 10 万次网络往返，慢到怀疑人生。攒够 1000 行批量写一次，性能能差出两个数量级。

public class ProductImportListener extends AnalysisEventListener<ProductRow> { private static final int BATCH_SIZE = 1000; private final List<ProductRow> buffer = new ArrayList<>(BATCH_SIZE); private final ProductMapper mapper; private int successCount = 0; public ProductImportListener(ProductMapper mapper) { this.mapper = mapper; } @Override public void invoke(ProductRow row, AnalysisContext context) { // 基础校验：编码为空直接跳过，并记录行号 if (row.getSku() == null || row.getSku().trim().isEmpty()) { return; } buffer.add(row); if (buffer.size() >= BATCH_SIZE) { flush(); } } private void flush() { if (buffer.isEmpty()) return; successCount += mapper.batchInsert(buffer); buffer.clear(); // 必须清空，否则内存持续增长 } @Override public void doAfterAllAnalysed(AnalysisContext context) { flush(); // 处理最后不足一批的尾巴数据 } public int getSuccessCount() { return successCount; } }

invoke里做校验、flush里做批量写、doAfterAllAnalysed里收尾——这三个方法的职责要分清。最容易犯的错是忘了在doAfterAllAnalysed里再 flush 一次，结果最后几百行永远进不了库，而且不报错，属于典型的「静默丢数据」。

3.2 Mapper 的批量插入 SQL

MyBatis 的foreach批量插入写法：

<insert id="batchInsert" parameterType="java.util.List"> INSERT INTO product_import (sku, name, price, stock) VALUES <foreach collection="list" item="item" separator=","> (#{item.sku}, #{item.name}, #{item.price}, #{item.stock}) </foreach> ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price), stock = VALUES(stock) </insert>

ON DUPLICATE KEY UPDATE让重复的 sku 走更新而不是报错，这就是幂等。但要注意：单条 SQL 的max_allowed_packet限制。1000 行 × 4 列拼出来的 SQL 可能超过默认的 4M，报Packet for query is too large。解决办法有两个：把BATCH_SIZE降到 500，或者调大 MySQL 的max_allowed_packet（SET GLOBAL max_allowed_packet = 33554432;）。我一般两个都做，双保险。

3.3 入口方法：事务和异常边界

@PostMapping("/import") public Result importExcel(@RequestParam("file") MultipartFile file) throws IOException { ProductImportListener listener = new ProductImportListener(productMapper); EasyExcel.read(file.getInputStream(), ProductRow.class, listener) .sheet() .doRead(); return Result.ok("成功导入 " + listener.getSuccessCount() + " 行"); }

这里有个必须想清楚的问题：要不要把整个导入包在一个事务里？我的经验是——不要。10 万行一个大事务，undo log 撑爆、锁持有时间过长、失败回滚代价巨大。正确做法是按批提交，每 1000 行一个独立事务，失败的那一批单独记录，其余照常入库。这样即使中间有坏数据，也不会让整个文件前功尽弃。如果业务强要求「全成功或全失败」，那就得引入导入任务表 + 状态机，而不是靠数据库大事务硬扛。

4. 参数调优与坏数据排查

4.1 三个必调参数

JDBC 的rewriteBatchedStatements=true。这是 MySQL 驱动里最容易被忽略、收益又最大的参数。不开它，你的foreach批量插入会被驱动拆成一条条单行 insert 发给服务端；开了它，驱动会把它们重写成真正的多值 INSERT。实测 10 万行导入，开启前后能差 3~5 倍。

spring.datasource.url=jdbc:mysql://localhost:3306/demo?\ rewriteBatchedStatements=true&\ useServerPrepStmts=true&\ characterEncoding=utf8mb4

BATCH_SIZE。太小网络往返多，太大 SQL 超包且内存占用高。1000 是甜点区，但如果你单行字段特别多（比如 30 列以上），降到 300~500 更稳。

max_allowed_packet。前面提过，配合批量大小一起调。服务端和客户端都要确认，别只改一边。

4.2 坏数据怎么定位到具体行

EasyExcel 的AnalysisContext里能拿到当前行号，但默认是 0 基的物理行号，和用户在 Excel 里看到的行号差 1（表头占一行）。定位坏行时，把行号、原始单元格值、异常信息一起记下来，返回给用户一个「错误报告」，比一句「导入失败」有用得多。

@Override public void onException(Exception exception, AnalysisContext context) { if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException e = (ExcelDataConvertException) exception; // 物理行号 + 1 才是用户在 Excel 里看到的行号 log.warn("第 {} 行第 {} 列转换失败，原始值：{}", e.getRowIndex() + 1, e.getColumnIndex(), e.getCellData().getStringValue()); } }

ExcelDataConvertException专门处理「单元格是文本但实体字段是数字/日期」这类转换失败。重写onException后，默认行为是抛异常中断整个导入，你可以改成「记录并跳过」，让好数据先入库。

4.3 日期和数字的经典陷阱

Excel 里的日期本质是一个浮点数（从 1900-01-01 起的天数），POI 读出来是double。如果实体字段是String，你会拿到45123这种鬼东西。解决办法是字段用Date类型，或者自定义Converter。数字列同理，手机号、身份证号这种长数字，Excel 默认按科学计数法显示，读出来可能是1.38E+10，必须在导入前让业务方把列格式设成文本，或者在代码里做还原。

5. 避坑与常见问题排查

现象：导入 3 万行后 OOM，堆内存持续上涨。原因：监听器里的buffer在 flush 后没clear()，或者把每一行都塞进了一个成员变量 List 里做「统计」。EasyExcel 的监听器是单例复用的，任何成员集合都必须及时清理。 解决：flush 后立即buffer.clear()；统计只存计数不存对象；用jmap -histo:live确认是不是ProductRow实例堆积。

现象：报Packet for query is too large。原因：批量 SQL 拼接后超过了max_allowed_packet。 解决：调小BATCH_SIZE到 500，同时SET GLOBAL max_allowed_packet = 33554432，重启连接池让新连接生效。

现象：中文列头匹配不上，所有字段都是 null。原因：Excel 文件编码或列头有前后空格、全角空格。EasyExcel 按字符串精确匹配列头。 解决：读之前先 trim，或者用headRowNumber(1)明确表头行，必要时自定义HeadNameMatcher做模糊匹配。

现象：重复导入产生重复数据。原因：表上没有唯一索引，或者用了普通INSERT而非ON DUPLICATE KEY UPDATE。 解决：加唯一索引，SQL 改成 upsert；如果历史数据已经有重复，先清洗再加索引，否则建索引会失败。

现象：导入成功但部分行丢失，且无报错。原因：doAfterAllAnalysed里没 flush 最后一批；或者invoke里的校验return掉了却没记录。 解决：收尾必 flush；所有跳过逻辑都要计数并输出，别让数据静默消失。

6. 进阶：把导入做成可观测、可重试的任务

到这一步，基本功能已经能跑了。但生产环境真正需要的，是可观测和可重试。我现在的习惯是给每次导入建一条任务记录：任务 ID、文件名、总行数、成功数、失败数、状态、错误详情。导入过程中每批更新一次进度，前端就能显示进度条；失败的行单独存到一张import_error表，用户可以下载错误报告，改完只重传失败部分。

验证导入是否真的成功，别只看接口返回的「成功 N 行」。我会做三件事：一是SELECT COUNT(*)对比源文件行数（减去表头和空行）；二是抽查几个 sku 的字段值，确认没有类型错位；三是用CHECKSUM TABLE或者对关键列做SUM聚合，和 Excel 里用公式算出来的结果对一遍。这三步能挡住 90% 的「看起来成功其实数据错了」。

一个具体技巧：用LOAD DATA LOCAL INFILE做兜底。当 Excel 特别大（50 万行以上）且格式规整时，先用脚本把 xlsx 转成 CSV，再用 MySQL 原生的LOAD DATA导入，速度比任何 Java 方案都快一个数量级。代价是它绕过了应用层的校验，所以只适合内部可信数据源。我一般把它作为「应急通道」，平时还是走 Java 链路，因为校验、日志、幂等这些能力都在应用层。

最后说个我踩过的坑：有次为了图快，把BATCH_SIZE设成 5000，本地测试飞快，上线后遇到一个字段特别长的商品描述，直接触发max_allowed_packet，整个导入挂掉。从那以后我养成了一个习惯——批量大小永远留余量，宁可 500 不赌 5000。参数这东西，稳比快重要。希望帮到你。

本文还有配套的精品资源，点击获取