SQLite AUTOINCREMENT你需要懂得知识

1.总结

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.背景

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

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

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

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

CREATE TABLE test1（INT，b TEXT）;

INSERT INTO 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的尝试都会导致错误。