标准批量插入语法
INSERT INTO Authors (AuthID, AuthFN, AuthMN, AuthLN) VALUES
(1006, 'H', 'S.', 'T'),
(1007, 'J', 'C', 'O'),
(1008, 'B', NULL, 'E'),
(1009, 'R', 'M', 'R'),
(1010, 'J', 'K', 'T'),
(1011, 'J', 'G.', 'N'),
(1012, 'A', NULL, 'P'),
(1013, 'A', NULL, 'W'),
(1014, 'N', NULL, 'A');
建表示例
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthFN VARCHAR(20),
AuthMN VARCHAR(20),
AuthLN VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
执行结果验证
mysql> SELECT * FROM Authors;
+--------+--------+--------+--------+
| AuthID | AuthFN | AuthMN | AuthLN |
+--------+--------+--------+--------+
| 1006 | H | S. | T |
| 1007 | J | C | O |
| 1008 | B | NULL | E |
| 1009 | R | M | R |
| 1010 | J | K | T |
| 1011 | J | G. | N |
| 1012 | A | NULL | P |
| 1013 | A | NULL | W |
| 1014 | N | NULL | A |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)
性能优势分析
| 方式 | 网络交互 | 解析次数 | 事务开销 | 适用场景 |
|---|
| 循环单条INSERT | N次 | N次 | N次(自动提交) | 极少数据、逻辑复杂 |
| 批量VALUES | 1次 | 1次 | 1次 | 千~万行，推荐 |
| LOAD DATA INFILE | 1次 | 极少 | 可控 | 百万+行，最快 |
| PreparedStatement批处理 | 1次 | 1次预编译 | 1次 | 应用层动态参数 |
最佳实践建议
- 分批提交：每批 500-2000 行，避免单条SQL过大超出
max_allowed_packet（默认4MB/64MB）
- 显式事务：
START TRANSACTION; ...批量INSERT... COMMIT;
- 主键顺序：按主键顺序插入，减少InnoDB页分裂
- 禁用二级索引：大批量导入前
DROP INDEX，导入后 CREATE INDEX
- 调整参数：
innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2（导入期间）
Java/JDBC 批处理示例
String sql = "INSERT INTO Authors (AuthID, AuthFN, AuthMN, AuthLN) VALUES (?, ?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
for (int i = 0; i < dataList.size(); i++) {
Author a = dataList.get(i);
ps.setInt(1, a.getId());
ps.setString(2, a.getFn());
ps.setString(3, a.getMn());
ps.setString(4, a.getLn());
ps.addBatch();
if (i % 1000 == 0) {
ps.executeBatch();
conn.commit();
}
}
ps.executeBatch();
conn.commit();
}
