Python导入Excel至MariaDB时自增ID不连续跳号问题求助

问题原因 #

你遇到的ID跳变问题是MariaDB InnoDB引擎的自增ID预分配机制导致的，完全匹配你给出的跳变规律：

• InnoDB引擎默认的innodb_autoinc_lock_mode参数值为1，针对批量插入操作，会一次性预分配一批连续的自增ID，避免多次申请锁带来的性能损耗，未使用完的ID会被直接丢弃，不会留给后续插入使用。
• 你每次从Excel导入的行数固定为8行，正好触发了预分配8个ID的规则，所以每次插入完成后，下一个可用ID直接跳到当前最大值+8，就出现了1-8、16-24、32-40这类规律跳号。
• 自增ID的设计核心目标是保证唯一性，连续性本身不属于默认承诺的特性，所以默认配置下跳号属于正常行为。

解决方法 #

如果业务强制要求ID连续，可以选择以下方案：

1. 修改InnoDB自增锁模式
   在MariaDB配置文件（Windows为my.ini、Linux为my.cnf）中添加以下配置：

   innodb_autoinc_lock_mode = 0
   

   该模式下每次插入都会逐个申请自增ID，不会预分配多余ID，可完全避免跳号问题，仅会小幅降低批量插入的性能，在并发写入量不高的场景完全可以接受。修改后重启数据库即可生效。

2. 调整pandas插入参数
   如果你不想修改数据库配置，可以在df.to_sql调用时添加chunksize参数，强制单次插入的行数和你Excel的行数一致：

   df.to_sql('tssr', con=engine, if_exists='append', index=False, chunksize=len(df))
   

   该方法可以大幅降低预分配多余ID的概率，适合无法修改数据库配置的场景。

3. 单次插入后重置自增指针（仅适用于单进程写入场景）
   如果能保证只有当前程序会向该表写入数据，可以在每次插入完成后手动重置自增ID的起始值：

   from sqlalchemy import text
   with engine.connect() as conn:
       conn.execute(text("ALTER TABLE tssr AUTO_INCREMENT = (SELECT MAX(id) FROM tssr) + 1"))
       conn.commit()
   

   注意该方法在多进程/多线程并发写入的场景下会出现ID冲突，不推荐使用。

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