SQLite—SQLite 3创建自增字段

最新推荐文章于 2023-09-26 12:29:36 发布

victorai60

最新推荐文章于 2023-09-26 12:29:36 发布

阅读量805

点赞数

文章标签： SQLite

在用SQLite设计表时，每个表都有一个自己的整形id值作为主键，其实可以不指定这么一个id值，SQLite内部本来就会为每个表加上一个rowid，这个rowid可以当成一个隐含的字段使用，但是由SQLite引擎来维护的，在3.0以前rowid是32位的整数，3.0以后是 64位的整数，为什么不直接使用这个内部的rowid作为每个表的id主键呢。

使用自增长字段为主键有不少问题，比如维护或是在大型分布应用中主键冲突的解决等。在一些大型分布应用中主键一般选用guid，这可以有效的避免主键冲突，减少对主键维护的工程。当然，对于中小型的应用，自增长字段的好处更多一些，简单、快速。

SQLite中，一个自增长字段定义为INTEGER PRIMARY KEY AUTOINCREMENT，那么在插入一个新数据时，只需要将这个字段的值指定为NULL，即可由引擎自动设定其值，引擎会设定为最大的rowid+1。当然，也可以设置为非NULL的数字来自己指定这个值，但这样就必须自己小心，不要引起冲突。当这个rowid的值大于所能表达的最大值 9223372036854775807 （3.0及以后版本的rowid最大值）后，rowid的新值会这个最大数之前随机找一个没被使用了的值。所以在rowid达到最大值前，rowid的值是严格单调增加的。

第一：在达到最大值后，rowid会找已被删除的字段对应的rowid作为新值，而自增长字段则会丢出一个SQLITE_FULL的错误。
第二：自增长字段在增加新值时，是找一个从没被使用过的rowid作为新值，而rowid则是找最大已存在的rowid+1。这里对应用的影响会比较大，尤其是一些对id值有依赖的元记录，只适合使用自增长字段而不能用rowid。

比如，我们设计一个元记录表：
drop table test;
create table test (
    [tkid]            integer PRIMARY KEY autoincrement,                -- 设置主键
    [tktype]          int default 0,
    [tableid]         varchar (50),
    [createdate]      datetime default (datetime('now', 'localtime'))    -- 时间
);

第三：使用自增长字段，引擎会自动产生一个sqlite_sequence表，用于记录每个表的自增长字段的已使用的最大值，用户可以看到，并可以用使用 Update、Delete和Insert操作，但不建议这么使用，这会让引擎混乱。如果使用rowid，也会有这么一个内部表，用户可以维护rowid 值，但看不到。
这么看来，如果直接使用rowid来代替自增加字段，根据两者的细微的差别，需要注意是否与自己的应用冲突，如果没有冲突，那么用rowid会更快一点。

注：从SQLite的2.3.4版本开始，如果你将一个表中的一个字段声明为INTEGER PRIMARY KEY，那么无论你何时向该表的该字段插入一个NULL值，这个NULL值将自动被更换为比表中该字段所有行的最大值大1的整数；如果表为空，那么将被更换为1。一个新的API函数 sqlite3_last_insert_rowid() 返回最近的插入操作的整形键。注意这个整型键始终比之前插入表中的最后一个键大1。新键相对于表中的已有键来说是唯一的，但它可能与之前从表中删除的键值重叠。要始终得到在整个表中唯一的键，在INTEGER PRIMARY KEY的声明之前加关键词AUTOINCREMENT.这样被选的键将总是比表中已存在的最大键大1。若可能的最大键已存在于表中，INSERT操作将失败并返回一个SQLITE_FULL错误码.

If you don’t want to read the whole post then just do this: Every time you create a table with sqlite make sure to have an INTEGER PRIMARY KEY AUTOINCREMENT column (the rowid column will be an alias to this one).

If you have some time then read on…

A lot of people don’t realize that a rowid can change. As always a simple example worths more than 1000 words. You can test this example withSQLiteManager.

Create a table without a primary key:
CREATE TABLE test (name TEXT);

Insert some data into the table:
INSERT INTO test (name) VALUES (‘marco’);
INSERT INTO test (name) VALUES (‘giuly’);
INSERT INTO test (name) VALUES (‘gregory’);
INSERT INTO test (name) VALUES (‘house’);

Perform a SELECT rowid,* FROM test;
Here you go the result:
1 marco
2 giuly
3 gregory
4 house

Everything seems OK (until now…)
Now delete a couple of rows:
DELETE FROM test WHERE name=’marco’;
DELETE FROM test WHERE name=’gregory’;

Now perform again: SELECT rowid,* FROM test;
Here it is the result:
2 giuly
4 house

Everything is fine, no?
That’s cool…

Now, perform a VACUUM on the database and run again the query:
SELECT rowid,* FROM test;
Here it is the result:
1 giuly
2 house

Rowids are changed!!!! So please take extra care when you define a table and need to reference records using rowids.
From the official documentation: “Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change”. And I add also AUTOINCREMENT so you are sure that the same rowid(s) are not reused when rows are deleted.

Unless SQLite is running in “auto_vacuum=FULL” mode, when a large amount of data is deleted from the database file it leaves behind empty space, or “free” database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

（译文：除非SQLite运行在“auto_vacuum=FULL”模式，否则当从数据库文件中删除大量数据之后，就会留下很多空白空间，或者“空闲”的数据库页。这意味着数据库文件的大小会比（它所存储的数据）实际需要的（空间）更大。运行VACUUM命令将会重新构建数据库文件，回收空白空间，减小数据库文件的大小。）