批量插入失败后关联表自增值失衡引发的数据完整性问题咨询

• 事务回滚后InnoDB出现AUTO_INCREMENT跳变是数据库的固有设计特性，并非事务逻辑失效。InnoDB为了避免自增锁竞争、提升并发插入性能，不会回退已经预分配的自增值，该行为属于预期设计。
• 触发外键完整性报错、自增值不匹配的核心原因并非自增跳变本身，而是现有代码存在两处硬伤：
  1. 插入流程未正确捕获自增主键值：插入product_img、sold_price表后，没有获取刚生成的img_id、sold_id，插入主表Product时甚至硬编码外键值为4509960，完全脱离实际插入的子表主键。
  2. 异常校验完全后置：将非空校验逻辑完全交给数据库约束触发，直到执行insert语句报错才中断流程，未在插入前对爬取数据做前置清洗。

1. 修正主键获取逻辑 #

不要依赖跨步骤的3405098全局值，每完成一张子表的插入，立刻通过当前游标读取刚生成的自增ID，存入变量供主表插入使用。
Python（pymysql）场景下的正确实现：

for row in real_data:
    # 插入产品图表，同步获取生成的img_id
    curr.execute(
        "INSERT INTO product_img (prod_img) VALUE (%s)",
        (row["prod_img"][0] if row.get("prod_img") else "default_placeholder",)
    )
    current_img_id = curr.lastrowid

    # 插入价格表，同步获取生成的sold_id
    curr.execute(
        "INSERT INTO sold_pricei (sold_price) VALUE (%s)",
        (row["sold_price"][0] if row.get("sold_price") else 0,)
    )
    current_sold_id = curr.lastrowid

    # 插入主表，直接使用已获取的两个外键值
    curr.execute(
        "INSERT INTO Product (item_title, sold_id, img_id) VALUES (%s, %s, %s)",
        (row["item_title"][0], current_sold_id, current_img_id)
    )
config.commit()

• curr.lastrowid是当前会话内针对上一条insert语句返回的实际主键值，不受自增计数器跳变、其他会话插入操作的影响，完全可以保证外键引用的正确性，不同表的自增计数器不需要强行保持数值一致。

2. 前置数据校验，减少无效插入 #

在循环插入前对每一行爬取数据做统一清洗：

• 针对数组类型的爬取字段：判断是否为空数组、是否存在空值元素，对prod_img、sold_price、item_title等必填字段做兜底处理：
  • 无有效值的sold_price直接赋值为0，匹配表结构非空要求
  • 无有效值的prod_img、item_title根据业务规则选择跳过该行、或赋值为默认占位值
• 提前过滤异常数据，避免无意义的insert操作触发数据库约束报错，减少回滚带来的自增值消耗。

3. 自增跳变的适配说明 #

• 生产环境无需处理自增跳变：跳变不影响已提交数据的正确性，也不会导致外键引用错误，只要正确获取每一步插入的主键值即可。
• 若为测试环境需要连续自增值，可临时调整MySQL参数innodb_autoinc_lock_mode=0，该模式下事务回滚会同步回退自增计数器，但会显著降低并发插入性能，禁止在生产环境使用。

4. 代码细节修正 #

现有建表语句中，sold_price表名错写为sold_pricei，插入语句的表名需要和建表语句保持一致，否则会触发表不存在的报错。

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