在MySQL开发中，insert、insert ignore、insert select 是最常用的三种数据插入方式。它们虽能满足不同业务需求（如普通插入、忽略重复数据、批量迁移数据），但都存在一个共性隐患——可能导致自增ID主键空洞。自增ID空洞会造成主键资源浪费，若业务依赖ID连续性（如流水号生成、时序数据统计），还可能引发业务异常。

前置知识：自增ID的核心分配规则

无论哪种插入场景，自增ID（AUTO_INCREMENT）的分配都遵循一个核心规则：自增ID一旦分配给插入操作，无论操作最终成功与否，都不会回收复用。后续插入操作会从当前最大自增ID继续递增，这是所有场景下空洞产生的底层逻辑。

为方便后续演示，先创建基础测试表（以InnoDB引擎为例）：

-- 创建测试表，id为自增主键，name设为唯一索引（用于复现重复数据场景）
CREATE TABLE test_user (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
  name VARCHAR(20) NOT NULL COMMENT '用户名',
  UNIQUE INDEX idx_name (name)
);

一、普通insert：事务回滚/插入失败引发空洞

普通insert是最基础的插入方式，看似简单，却容易因事务回滚、插入数据校验失败导致自增ID空洞。这两种情况的核心共性是：自增ID已分配，但插入操作未成功提交。

1. 场景1：事务回滚导致空洞

当insert操作处于事务中时，MySQL会在执行insert语句时立即分配自增ID，而非等到事务提交。若后续事务因异常（如业务逻辑错误、手动回滚）回滚，已分配的ID会被直接“浪费”，后续插入会跳过该ID，形成空洞。

复现步骤：

-- 1. 开启事务
START TRANSACTION;
-- 2. 插入数据，此时自增ID已分配为1
INSERT INTO test_user (name) VALUES ('张三');
-- 3. 模拟业务异常，回滚事务
ROLLBACK;

-- 4. 再次插入数据，自增ID从2开始（ID=1成为空洞）
INSERT INTO test_user (name) VALUES ('李四');
-- 执行结果：id=2，name=李四，空洞产生（缺失ID=1）

2. 场景2：插入失败（违反约束）导致空洞

若insert操作因违反数据库约束（如唯一索引冲突、字段非空约束）直接失败，且失败前自增ID已分配，该ID会被浪费。最典型的场景是插入重复的唯一索引数据。

复现步骤：

-- 1. 先插入一条正常数据，id=3
INSERT INTO test_user (name) VALUES ('王五');

-- 2. 插入重复name，违反唯一索引，插入失败
INSERT INTO test_user (name) VALUES ('李四'); -- 报错：Duplicate entry '李四' for key 'idx_name'

-- 3. 继续插入一条正常数据，id=5
INSERT INTO test_user (name) VALUES ('王五');

二、insert ignore：忽略重复导致的“隐蔽空洞”

insert ignore 的核心作用是：当插入数据存在重复冲突时，不报错，直接忽略该条数据的插入。但正因为“忽略错误”的特性，其导致的自增ID空洞更隐蔽——开发者可能误以为数据未插入就不会分配ID，实则不然。

核心原因：insert ignore 执行时，MySQL仍会先为待插入数据分配自增ID，再检查是否存在冲突；若存在冲突，仅忽略插入操作，已分配的ID不会回收，直接造成空洞。

场景复现：忽略重复数据导致空洞

TRUNCATE TABLE test_user;
-- 1. 插入第一条数据，id=1（成功）
INSERT IGNORE INTO test_user (name) VALUES ('张三');

-- 2. 插入重复数据，触发ignore，忽略插入
INSERT IGNORE INTO test_user (name) VALUES ('张三');
-- 关键：此时MySQL已为这条重复数据分配了ID=2，只是未插入数据

-- 3. 插入新数据，自增ID直接变为3（ID=2成为空洞）
INSERT IGNORE INTO test_user (name) VALUES ('李四');

-- 执行结果：表中数据id=1、3，ID=2空洞产生，且无任何错误提示，隐蔽性极强

注意：insert ignore 仅忽略“重复键冲突”“数据转换错误”等特定错误，若因其他原因（如字段长度超限）插入失败，仍会报错，且同样会导致ID空洞。

三、insert select：批量插入的“预分配空洞”

insert select 用于从其他表批量迁移数据，是高效的批量插入方式。但它导致空洞的原因与前两种不同——核心是 MySQL 对批量插入的“自增ID预分配机制”：为提升插入效率，MySQL会提前预估需要的自增ID数量，一次性分配足够的ID；若实际插入的记录数小于预估数量，多余的预分配ID会被浪费，形成空洞。

补充说明：预分配的ID数量并非固定，与插入数据的来源、MySQL版本相关，通常会比实际需要的数量多，导致空洞范围可能较大。

场景复现：批量插入预分配导致空洞

TRUNCATE TABLE test_user; 
-- 1. 创建临时表，用于存储待迁移数据
CREATE TABLE temp_user (
  name VARCHAR(20) NOT NULL
);
-- 2. 向临时表插入2条数据
INSERT INTO temp_user (name) VALUES ('张三'), ('李四');

-- 3. 用insert select批量迁移数据
INSERT INTO test_user (name) SELECT name FROM temp_user;
-- 此时实际插入2条数据，id=1、2，但MySQL预分配了4个ID（预估数量>实际数量）

-- 4. 插入新数据，自增ID直接变为5（ID=3、4成为空洞）
INSERT INTO test_user (name) VALUES ('王五');

-- 执行结果：表中数据id=1、2、5，ID=3、4形成空洞

延伸：若 insert select 插入过程中出现部分数据失败（如部分数据违反约束），已分配的预分配ID仍会全部占用，失败数据对应的ID也会成为空洞，进一步扩大空洞范围。

四、三种场景的共性与针对性规避方案

1. 共性总结

• 核心根源：自增ID“分配后不回收”的机制，所有空洞都是“ID已分配但未有效使用”导致；
• 风险场景：事务回滚、数据冲突、批量插入预分配是三大核心风险点；
• 隐蔽性排序：insert ignore > insert select > 普通insert（insert ignore无报错提示，最易被忽略）。

2. 针对性规避方案

（1）针对普通insert：避免事务回滚+提前校验数据

• 减少事务回滚：插入操作尽量放在独立的小事务中，缩短事务执行时间；插入前先校验业务逻辑，避免因业务异常导致回滚；
• 提前校验数据约束：插入前通过查询判断是否存在重复数据（如SELECT COUNT(*) FROM test_user WHERE name='李四'），避免因约束冲突导致插入失败；
• 慎用大事务：避免在包含复杂逻辑的大事务中执行insert，降低回滚概率。

（2）针对insert ignore：尽量替代，而非依赖

• 优先用“查询+插入”替代：先查询数据是否存在，不存在再执行insert，避免使用insert ignore；

（3）针对insert select：优化批量插入逻辑

• 避免预分配浪费：优先用“INSERT ... VALUES”批量插入（明确数据量，无预分配），替代insert select（仅当数据量极大时使用insert select）；
• 调整 innodb_autoinc_lock_mode 参数,默认 innodb_autoinc_lock_mode=1：INSERT SELECT 会提前预估 ID 数量，可能过量；设置 innodb_autoinc_lock_mode=0：INSERT SELECT 会先执行 SELECT 获取实际记录数，再精准分配对应数量的 ID，无多余预分配，但并发性能极差，适合低并发场景。
• 分批次小批量执行 INSERT SELECT：将大数据量的 INSERT SELECT 拆分为多个小批次（如每次插入 100 条），即使每个批次预分配少量多余 ID，整体浪费也会大幅减少。

（4）通用兜底方案：不依赖自增ID连续性

• 核心业务改用分布式ID：若业务强依赖ID连续性（如流水号），放弃自增ID，改用雪花算法（Snowflake）、UUID等分布式ID方案；

五、总结

insert、insert ignore、insert select 三种插入场景的自增ID空洞，本质都是“自增ID分配机制”与“操作失败/忽略”共同作用的结果。其中，insert ignore的隐蔽性最强，insert select的空洞范围可能最大，普通insert的问题最易定位。

在实际开发中，我们无需过度追求“零空洞”（MySQL自增ID设计初衷不保证连续性），但需针对核心业务场景做好规避：通过“提前校验数据、优化事务逻辑、替代高风险插入方式”减少空洞产生；若业务强依赖连续性，直接改用分布式ID方案。