SQLite Autoincrement

1. Summary

• AUTOINCREMENT关键字会增加额外的CPU，内存，磁盘空间和磁盘I / O开销，如果不是严格需要，应该避免这种情况。通常不需要。

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

• 在INSERT上，如果ROWID或INTEGER PRIMARY KEY列没有明确给定值，则会自动填充未使用的整数，通常比当前使用的最大ROWID多一个。无论是否使用AUTOINCREMENT关键字，情况都是如此。

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

2. Background

在SQLite中，表行通常有一个64位有符号整数ROWID，它在同一个表中的所有行中都是唯一的。（WITHOUT ROWID表是例外。）

您可以使用特殊列名称ROWID，_ROWID_或OID之一访问SQLite表的ROWID。除非您声明普通表列使用其中一个特殊名称，那么使用该名称将声明的列引用到内部ROWID。

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

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

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

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

上面描述的正常ROWID选择算法将生成单调递增的唯一ROWID，只要您从不使用最大ROWID值并且永不删除具有最大ROWID的表中的条目。如果您删除了行或者如果您曾经创建过最大可能的ROWID行，那么在创建新行时可能会重复使用以前删除的行中的ROWID，并且新创建的ROWID可能不是严格按升序排列。

3. The AUTOINCREMENT Keyword

如果列的类型为INTEGER PRIMARY KEY AUTOINCREMENT，则使用稍微不同的ROWID选择算法。为新行选择的ROWID至少比之前在同一个表中存在的最大ROWID大一个。如果表中以前从未包含任何数据，则使用1的ROWID。如果先前插入了最大可能的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总是增加1。一个是通常的增量。但是，如果插入由于（例如）唯一性约束而失败，则失败的插入尝试的ROWID可能无法在后续插入中重用，从而导致ROWID序列中存在间隙。AUTOINCREMENT保证自动选择的ROWID将会增加，但不会保证它们是连续的。

由于AUTOINCREMENT关键字会更改ROWID选择算法的行为，因此WITHOUT ROWID表或除INTEGER PRIMARY KEY之外的任何表列上不允许使用AUTOINCREMENT。任何尝试在WITHOUT ROWID表或除INTEGER PRIMARY KEY列之外的列上使用AUTOINCREMENT都会导致错误。

 SQLite is in the Public Domain.