Autoincrement In SQLite

• 链接
• 概要
• 背景
• AUTOINCREMENT关键字

---

概要

1. AUTOINCREMENT关键字消耗了额外的CPU，内存，磁盘空间以及磁盘输入/输出开销，如果不是严格需要的话，那么应该避免使用它。通常来说也不需要使用该关键字。
2. 在SQLite中，类型为INTEGER PRIMARY KEY的列就是ROWID（除了使用WITHOUT ROWID的表）的别名，ROWID总是64位的有符号整数。
3. 在INSERT操作时，如果ROWID或者INTEGER PRIMARY KEY的列没有显示给出值，那么该列将会用未使用过的整数自动填充，通常来说，该整数比当前正在使用的最大ROWID大。
4. 如果AUTOINCREMENT关键字出现在INTEGER PRIMARY KEY之后，这样会在整个数据库生命周期内改变自动ROWID的赋值算法以防止ROWIDs的重复使用。换句话说，AUTOINCREMENT的目的就是防止之前被删除过的行的ROWIDs被重复使用。

背景

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

你可以使用一个特别的列名ROWID，_ROWID_，或者OID来访问一张SQLite表的ROWID。除非如果你声明了一个普通的列去使用这些特别的名称之一，那么那个名称的使用将会指向声明的列而非内部的ROWID。

如果一张表包含了一个类型为INTEGER PRIMARY KEY的列，那么那个列就成为了ROWID的别名。你可以使用四个名称中的任意一个，以及最初上面描述过的最初的三个名称，或者是指定了INTEGER PRIMARY KEY列的名称来访问ROWID。

当一个新行插入到一张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，64位），那么数据库引擎就会开始随机挑选候选的ROWID，直到找到一个之前未使用过的ROWID。如果在查找合理的数字尝试之后依然没有找到未使用的ROWID，那么会报出一个插入操作SQLITE FULL的错误。

以上描述的正常ROWID选择算法会单调地自动增加唯一的ROWIDs，只要你从没有使用改过最大的ROWID的值，并且你从没有使用最大的ROWID删除表里的条目。如果你曾经使用最大ROWID删除了多行或者如果你曾经使用最大ROWID创建了一行，那么当新建的行和新建的ROWIDs可能没有以严格递增的顺序时，之前删除过的行的ROWIDs可能被再次使用。

AUTOINCREMENT关键字

如果一个列拥有类型INTEGER PRIMARY KEY AUTOINCREMENT，那么略微不同的ROWID选择算法就被使用。被选择的新行ROWID至少会比最大的之前在同一张表中存在过的ROWID大1。如果表在包含数据之前从未被使用过，那么值为1的ROWID就会被使用。如果表之前持有过使用了最大ROWID，那么新的插入操作不会被允许，并且任何试图插入新行将会报SQLITE FULL的错误。只有之前提交过的事务操作ROWIDs的值才会被考虑。被回滚的ROWID的值会被忽略并且被重新使用。

SQLite会记录一张表曾经持有使用名称为“SQLite序列”的”内部表“的最大ROWID。每当一张正常的包含有AUTOINCREMENT的列的表被创建时，SQLite序列表被创建并且被自动初始化。SQLite序列表的内容可以使用普通的UPDATE，INSERT和DELETE语句修改。但是对这张表做修改很可能会扰乱AUTOINCREMENT key的生成算法。你要确定在你做这样的改变之前，知道你在做什么。

由AUTOINCREMENT关键字执行的行为会和默认的行为微妙的区别。使用AUTOINCREMENT，自动被选择的ROWIDs的行能保证在同一个数据的同一个表中有从未被使用过的ROWID。并且自动增加的ROWIDs能保证单调增加。这些行为在在某些应用中是很重要的属性。但是如果你的应用不需要这些属性，那么你应该保留默认行为，因为当每行插入时，AUTOINCREMENT会需要额外的工作并且会造成插入操作运行得更慢一点。

记住，“单调递增”并不意味着ROWID总是能准确地增加1。1表示通常的增加。可是，如果一个插入操作由于某个独特的约束失败了，那么失败的插入企图的ROWID可能不会在后来的插入操作中使用，于是就导致了ROWID序列的间隔。AUTOINCREMENT保证自动选择的ROWIDs会增加但是不是连续地增加。

因为AUTOINCREMENT关键字改变了ROWID选择算法的行为，所以AUTOINCREMENT不会允许在WITHOUT ROWID的表或者在未使用INTEGER PRIMARY KEY的表。任何试图在WITHOUT ROWID表或者未使用INTEGER PRIMARY KEY的列的表使用AUTOINCREMENT会导致错误。