Skip to main content

理解SQLite自动增量

SQLite ROWID表简介

无论何时创建表而不指定“不使用ROWID”选项,都会得到一个名为ROWID的隐式自动增量列。rowid列存储唯一标识表中一行的64位有符号整数。

让我们看看下面的例子。

首先,创建一个名为people的新表,该表有两列:First_name和last_name:

CREATE TABLE people (
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
);

试试看

其次,使用以下insert语句将一行插入people表:

INSERT INTO people (first_name, last_name)
VALUES('John', 'Doe');

试试看

第三,使用以下SELECT语句从people表中查询数据:

SELECT
rowid,
first_name,
last_name
FROM
people;

试试看

SQLite AUTOINCREMENT

从输出中可以清楚地看到,SQLite隐式地创建一个名为rowid的列,并在向表中插入新行时自动指定一个整数值。

请注意,您也可以使用rowid列的别名来引用它:_rowid_uu和oid。

创建包含整数主键列的表时,此列是rowid列的别名。

下面的语句删除table people并重新创建它。不过,这次我们添加了另一个名为person_id的列,其数据类型为INTEGER,列约束为主键:

DROP TABLE people;

CREATE TABLE people (
person_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
);

试试看

在本例中,person_id列实际上是rowid列。

SQLite如何为rowid列分配整数值?

如果插入新行时未指定rowid值或使用空值,SQLite会自动分配下一个连续整数,该整数比表中最大的rowid大一个。rowid值从1开始。

therowid列的最大值为9223372036854775807,非常大。如果数据达到这个最大值,并且试图插入新行,SQLite将找到一个未使用的整数并使用它。如果SQLite找不到任何未使用的整数,它将发出SQLite_FULL错误。最重要的是,如果删除一些行并插入新行,SQLite将尝试重用已删除行中的rowid值。

我们来测试一下。

首先,在people表中插入一个最大值的行。

INSERT INTO people (person_id,first_name,last_name)
VALUES( 9223372036854775807,'Johnathan','Smith');

试试看

SQLite maximum rowid value

其次,插入另一行,但不指定person_id列的值:

INSERT INTO people (first_name,last_name)
VALUES('William','Gate');

试试看

SQLite INSERT row without rowid

正如输出中清楚显示的,新行接收到一个未使用的整数。

再举一个例子。

首先,创建一个名为t1的新表,该表有一列:

CREATE TABLE t1(c text);

其次,在t1表中插入一些行:

INSERT INTO t1(c) VALUES('A');
INSERT INTO t1(c) values('B');
INSERT INTO t1(c) values('C');
INSERT INTO t1(c) values('D');

第三,从t1表中查询数据:

SELECT rowid, c FROM t1;

第四,删除t1表的所有行:

DELETE FROM t1;

第五,在t1表中插入一些行:

INSERT INTO t1(c) values('E');
INSERT INTO t1(c) values('F');
INSERT INTO t1(c) values('G');

最后,从t1表中查询数据:

SELECT rowid, c FROM t1;

如您所见,rowid1、2和3已被重新用于新行。

SQLite自动增量列属性

SQLite建议您不要使用AUTOINCREMENT属性,因为:

此外,SQLite为AUTOINCREMENT列指定值的方式与为rowid列指定值的方式略有不同。

考虑下面的例子。

首先,删除并重新创建people表。这一次,我们使用自动增量属性列:

DROP TABLE people;

CREATE TABLE people (
person_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name text NOT NULL,
last_name text NOT NULL
);

试试看

第二,在people表中插入一个rowid值最大的行。

INSERT INTO people (person_id,first_name,last_name)
VALUES(9223372036854775807,'Johnathan','Smith');

试试看

第三,在people表中插入另一行。

INSERT INTO people (first_name,last_name)
VALUES('John','Smith');

试试看

这一次,SQLite发出了一条错误消息,因为person_id列没有像rowid列那样重用该数字。

[Err] 13 - database or disk is full

什么时候应该使用“自动增量列”属性?

使用属性AUTOINCREMENT的主要目的是防止SQLite重用尚未使用的值或以前删除的行中的值。

如果没有这样的要求,就不应该在主键中使用AUTOINCREMENT属性。

在本教程中,您已经了解了SQLite AUTOINCREMENT属性如何工作,以及它如何影响SQLite将值分配给主键列的方式。