sqlite自增：ROWID

最新推荐文章于 2024-07-24 15:59:19 发布

wwyyxx26

最新推荐文章于 2024-07-24 15:59:19 发布

阅读量4k

点赞数

CC 4.0 BY-SA版权

文章标签： sqlite integer table sorting structure exception

本文链接：https://blog.youkuaiyun.com/wwyyxx26/article/details/6623483

本文详细解释了SQLite数据库中ROWID的概念及其与INTEGER PRIMARY KEY的关系，阐述了ROWID作为唯一标识符的特性及如何高效地通过ROWID进行记录检索与排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ROWIDs and the INTEGER PRIMARY KEY

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usuallycalled the "rowid". The rowid value can be accessed using one of the specialcase-independent names "rowid", "oid", or "_rowid_" in place of a column name.If a table contains a user defined column named "rowid", "oid" or "_rowid_",then that name always refers the explicitly declared column and cannot be usedto retrieve the integer rowid value.

The data for each table in SQLite is stored as a B-Tree structure containingan entry for each table row, using the rowid value as the key. This means thatretrieving or sorting records by rowid is fast. Searching for a record with aspecific rowid, or for all records with rowids within a specified range isaround twice as fast as a similar search made by specifying any other PRIMARYKEY or indexed value.

With one exception, if a table has a primary key that consists of a single column, and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such acolumn is usually referred to as an "integer primary key". A PRIMARY KEY columnonly becomes an integer primary key if the declared type name is exactly"INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER"or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinarytable column with integer affinity and a unique index, not as an alias for the rowid.

红色部分：如果主键为INTEGER，则the column是rowid的alias ；