SQLite Autoincrement特性-CSDN博客

摘要

1. AUTOINCREMENT会使用额外的CPU、内存和磁盘空间如不是特别需要尽量不要使用。
2. 类型为INTEGER PRIMARY KEY的列会被作为rowidWITHOUT ROWID表除外并且是一个8字节有符号整数。
3. 执行INSERT操作时如果未提供rowid会自动分配一个未使用的整数通常是大于当前使用的最大rowid不论该列是否赋予了AUTOINCREMENT特性。
4. AUTOINCREMENT关键字出现在INTEGER PRIMARY KEY之后该特性会改变表的rowid分配算法不会重复使用之前删掉的rowid值。

背景

rowid会被分配一个表内唯一有符号整数WITHOUT ROWID表除外。
通过 “rowid”,“rowid”,"oid"可以访问rowid列或者使用被作为rowid列的INTEGER列名称。
插入新记录时即可以明确指定一个rowid值也可以不提供系统分自动分配。

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

如果在插入记录时未指定rowid或指定了一个NULL值系统会自动分配一个rowid。默认是分配一个比最后一次插入时分配的rowid大的值从1开始。如果达到最大值9223372036854775807则会扫描表并使用一个未使用的rowid。如果未找到可用值则报错SQLITE_FULL。如果未显式插入一个负的rowid则自动分配的rowid永远是正值。

常规算法是只要未达到最大值则单调递增rowid。如果显式指定了最大值下次分配时将使用之前删除的值在这种情况下分配的rowid不会是严格递增的。

AUTOINCREMENT关键字

带有AUTOINCREMENT特性的INTEGER PRIMARY KEY列其自增算法会与rowid的分配算法稍有不同。仍然从1开始分配但如果达到孙最大值下次插入时会报错SQLITE_FULL而不会重复使用之前删除的值除非上一个分配id的事务操作被回滚这个值会被使用到下一次插入操作。

SQLite内部使用一个名称为sqlite_sequence的内部表存储自增字段的值该表会在创建一个带有AUTOINCREMENT特性的表的时候自动创建。该表可以通过UPDATE、INSERT和DELETE语句直接修改但不建议去操作该表。

单调递增并不一定意味着下次分配的值一定是增加1。例如在一次插入失败时后续插入操作可能不会再使用那个插入失败时分配的id。简单概括就是不能假定该值一定比上一个分配的值大1。

在WITHOUT ROWID表中或非INTEGER PRIMARY KEY列上面不允许使用AUTOINCREMENT特性。

原文链接https://www.sqlite.org/autoinc.html