Small. Fast. Reliable.
Choose any three.
SQLite自动增量

1.总结

  1. AUTOINCREMENT关键字强加了额外的CPU，内存，磁盘空间和磁盘I / O开销，如果没有严格要求，则应避免使用。通常不需要。

  2. 在SQLite中，类型为INTEGER PRIMARY KEY的列是ROWID的别名 （在WITHOUT ROWID表中除外），该别名始终是64位有符号整数。

  3. 在INSERT上，如果未显式给ROWID或INTEGER PRIMARY KEY列赋值，则将自动用未使用的整数填充该整数，通常比当前使用的最大ROWID多一个整数。无论是否使用AUTOINCREMENT关键字，都是如此。

  4. 如果在INTEGER PRIMARY KEY之后出现AUTOINCREMENT关键字，则会更改自动ROWID分配算法，以防止在数据库的生存期内重用ROWID。换句话说，AUTOINCREMENT的目的是防止重复使用先前删除的行中的ROWID。

2.背景

在SQLite中，表行通常具有64位带符号整数ROWID ，该ID在同一表的所有行中都是唯一的。（没有ROWID表是例外。）

您可以使用特殊列名称ROWID，_ROWID_或OID之一访问SQLite表的ROWID。除非您声明普通表列使用那些特殊名称之一，否则该名称的使用将引用已声明的列而不是内部ROWID。

如果表包含INTEGER PRIMARY KEY类型的列，则该列将成为ROWID的别名。然后，您可以使用以下四个不同的名称中的任何一个来访问ROWID：上述三个原始名称或为INTEGER PRIMARY KEY列指定的名称。所有这些名称都是彼此的别名，并且在任何情况下都可以很好地工作。

当在SQLite表中插入新行时，可以将ROWID指定为INSERT语句的一部分，也可以由数据库引擎自动分配它。要手动指定ROWID，只需将其包括在要插入的值列表中即可。例如： 

创建表test1（a INT，b TEXT）;
插入test1（rowid，a，b）VALUES（123，5，'hello'）;

如果在插入上未指定ROWID，或者指定的ROWID的值为NULL，则将自动创建适当的ROWID。通常的算法是为新创建的行赋予ROWID，该ROWID比插入之前表中的最大ROWID大一。如果表最初是空的，那么将使用ROWID 1。如果最大ROWID等于最大可能整数（9223372036854775807），则数据库引擎开始随机选择正候选ROWID，直到找到以前未使用过的ROWID。如果经过合理的尝试次数后未找到未使用的ROWID，则插入操作将失败，并显示SQLITE_FULL错误。如果没有明确插入负的ROWID值，则自动生成的ROWID值将始终大于零。

只要您从不使用最大ROWID值并且从未删除具有最大ROWID的表中的条目，上述常规ROWID选择算法将生成单调递增的唯一ROWID。如果删除行或创建具有最大可能ROWID的行，则在创建新行时，可能会重复使用先前删除的行中的ROWID，并且新创建的ROWID可能不严格按升序排列。

3. AUTOINCREMENT关键字

如果列的类型为INTEGER PRIMARY KEY AUTOINCREMENT，则使用略有不同的ROWID选择算法。为新行选择的ROWID至少比同一表中以前存在的最大ROWID大一个。如果该表之前从未包含任何数据，则使用ROWID 1。如果以前已插入最大可能的ROWID，则不允许使用新的INSERT，并且任何尝试插入新行的尝试都会失败，并显示SQLITE_FULL错误。仅考虑来自先前提交的事务的ROWID值。回滚的ROWID值将被忽略，并且可以重复使用。

SQLite使用名为“ sqlite_sequence ”的内部表来跟踪最大的ROWID 。每当创建包含AUTOINCREMENT列的普通表时，都会自动创建并初始化sqlite_sequence表。可以使用普通的UPDATE，INSERT和DELETE语句修改sqlite_sequence表的内容。但是对此表进行修改可能会干扰AUTOINCREMENT密钥生成算法。进行此类更改之前，请确保您知道自己在做什么。sqlite_sequence表不跟踪与UPDATE语句关联的ROWID更改，仅跟踪INSERT语句。

由AUTOINCREMENT关键字实现的行为与默认行为略有不同。使用AUTOINCREMENT，可以保证具有自动选择的ROWID的行具有以前从未在同一数据库中的同一表使用过的ROWID。并且保证自动生成的ROWID会单调增加。这些是某些应用程序中的重要属性。但是，如果您的应用程序不需要这些属性，则可能应该保留默认行为，因为使用AUTOINCREMENT时需要在插入每一行时进行额外的工作，从而使INSERT的运行速度稍慢一些。

注意，“单调增加”并不意味着ROWID总是正好增加一。一种是通常的增量。但是，如果插入由于（例如）唯一性约束而失败，则失败的插入尝试的ROWID可能不会在后续插入中重复使用，从而导致ROWID序列中出现间隔。AUTOINCREMENT保证自动选择的ROWID会增加，但不能保证它们是顺序的。

因为AUTOINCREMENT关键字更改了ROWID选择算法的行为，所以在WITHOUT ROWID表或除INTEGER PRIMARY KEY之外的任何其他表列上均不允许使用AUTOINCREMENT 。任何在WITHOUT ROWID表或INTEGER PRIMARY KEY列以外的列上使用AUTOINCREMENT的尝试都将导致错误。