近日，一项关于数据库自增字段（AUTOINCREMENT）在多行INSERT语句中行为顺序的技术细节，在开发者社区引发热议。许多资深程序员在实际项目中遭遇“预期之外”的ID分配问题，甚至怀疑数据库存在Bug。这一现象背后，隐藏着关于SQL标准实现、数据库引擎差异以及开发者认知偏差的深层话题。

问题重现：明明按顺序插入，ID却“跳号”？

某互联网公司后端团队在重构订单系统时，使用如下SQL语句同时插入多条记录：

INSERT INTO orders (user_id, amount) VALUES (1001, 29.9), (1002, 49.9), (1003, 19.9);

该表orders包含自增主键id。开发者原本预期插入的三条记录会获得连续ID，例如1001, 1002, 1003。然而实际返回的ID却是1001, 1003, 1005，中间跳过了偶数号。团队花了数小时排查并发问题、事务隔离级别，甚至怀疑数据库服务器时钟出现了抖动——最终才发现，这一“异常”竟是SQLite数据库的内置行为。

技术解析：AUTOINCREMENT的“伪连续”本质

大多数数据库的自增ID设计初衷是保证唯一性而非连续性。在单行插入场景中，自增计数器每次递增1，看起来是连续的。但在多行插入（multi-row INSERT）中，不同数据库的实现策略截然不同：

  • MySQL（InnoDB）：预先为整个语句分配一段连续的ID范围，插入后记录按顺序获得连续ID。这是大多数开发者的“直觉”。
  • SQLite：对多行插入中的每一行分别递增计数器，且递增操作发生在解析阶段而非执行阶段。更关键的是，SQLite不会预先锁定连续的ID区间，而是每插入一行就申请一个ID，且如果遇到冲突或回滚，已经分配出去的ID不会再被回收。这导致多行插入获得的ID可能是步进为1的连续值，也可能出现间隔。
  • PostgreSQL：其SERIAL或IDENTITY列本质上是序列（sequence），多行插入时每一行从序列中获取下一个值，相邻ID通常连续，但若存在并发插入，也可能出现“空洞”。

最让人困惑的是SQLite的一个隐藏规则：当使用AUTOINCREMENT关键字（而非普通的rowid别名）时，多行INSERT是按照表中行存储的顺序来分配ID的——但SQLite内部对行的存储顺序并不保证与INSERT的书写顺序完全一致。在某些版本中，如果表包含WITHOUT ROWID选项或存在复合索引，ID分配顺序甚至可能反转。

实战影响：哪些场景会踩坑？

  1. 多行插入后依赖ID顺序：有些开发者通过插入后返回的last_insert_rowid()配合插入行数来推算后续ID，这种做法在多行插入中完全不可靠。
  2. 主从复制与数据迁移：基于ID顺序做增量同步的工具，可能因ID不连续而误判为丢失数据。
  3. 同时插入关联数据：比如先插入订单头，后插入订单明细，若使用SQLite且采用了多行插入，明细表的外键ID可能出现错位。

权威说法：SQLite官方文档的明确警告

SQLite官方文档在AUTOINCREMENT章节中特别注明：“You should not rely on the values of the rowid being sequential in any way.” 它强调自增ID仅保证单调递增和唯一性，不保证连续，也不保证与插入顺序相关的任何序列点。社区中曾有人提交Bug报告，认为多行INSERT的ID分配应该“先到先得”，但SQLite核心开发者回应称，这是为了减少锁竞争并提高并发性能的有意设计。

最佳实践：如何避免被“顺序幻觉”误导？

  • 永远不要假设自增ID连续：无论是MySQL、SQLite还是PostgreSQL，都应把自增ID仅视为唯一标识符，而非业务顺序的载体。如果需要连续编号，应使用ROW_NUMBER()窗口函数或应用层生成逻辑。
  • 多行插入时避免依赖隐式顺序：如需在插入后获取每个新行的ID，推荐使用RETURNING子句（PostgreSQL、SQLite 3.35+支持）或逐行插入并收集last_insert_id。
  • 考虑使用UUID作为业务主键：在分布式或需要明确顺序的场景，UUID虽然更长，但能彻底消除对自增ID连续性的依赖。

结语

这条看似简单的技术规则，折射出数据库设计哲学的根本差异。SQLite将性能与简洁性置于“人性化”之上，而MySQL则更倾向于迎合开发者的直觉。对于开发者而言，深入理解底层机制，破除对自增ID“连续”的刻板印象，才是避免未来“跳坑”的正确姿势。毕竟，数据库从不承诺它没有承诺的东西——而我们的代码，也应该建立在明确的约定之上。